How to transpose and copy 16384+ column data in excel to notepad?
Hi! i am stuck in this problem:
1) I have a column with about 200,000+ rows in Excel 2007, and need to
somehow get this transposed into a row (so with 200,000+ columns). I have
tried to do this with the TRANSPOSE function using Ctrl-C and using the Paste
drop-down menu and selecting the Transpose option. But I get an error message
that the copy area and paste area are not the same size. I presume this is
because I cannot paste across 25,000+ columns due to column capacity of
office i.e 16384 :( The thing is that i have to transpose this data in excel
and save this file as tab delimited in notepad... well it was easy for me
when i had small data to transpose and convert it into notepad file but now
copy pasting and transposing one by one will be headache i really dont know
what to do??
2) I have done copy pasting chunks of data and transposing but when the
continued 16385 (i.e Row 2, column 1) onwards is pasted and file is changed
into notepad the data is fine until the continued 16385(Row2) data in notepad
is given with a lot of space and is a mess when i do backspace. Kindly guide
me to get the data in notepad from excel in one proper tab delimited format
(all data is seperated by same space i.e. no uneven spaces in the places i
have mentioned.
Actually i work in MS DOS command line envorinment which require equally
spaced notepad file i.e. when its in a proper notepad tab delimited
format....
Kindly help me!! I shall be thankful to you
Noor - this is an odd thing
Noor - this is an odd thing to want to do.
What are you actually trying to do here ?
.. the only way I can think of to do this would be to write some VBA to write the range to txt file, but instead of writing rows, it writes cols as rows.
Actually i have this genetic
Actually i have this genetic data in MS excel that is too large and have to export it in a proper format in .txt files because its the requirement of the whole genome analysis tool, PLINK which i am using for my project. Also dont know anything about VBA that you are saying!
Sub TransposeToText() Dim
Sub TransposeToText()
Dim LineOut As String, Y As Long, FName As String, FNum As Integer
FName = "C:\Users\username\Desktop\Test.txt"
FNum = FreeFile
Open FName For Output Access Write As #FNum
For Y = 11 To 2500000
If Range("A" & Y).Value = "" Then Exit For
LineOut = LineOut & Range("A" & Y).Value & ","
Next Y
LineOut = Left(LineOut, Len(LineOut) - 1)
Print #FNum, LineOut
Close #FNum
End Sub
the above sub assumes the following :-
1) data is in column a - row 11 down
2) you require comma seperation
i've tested this up to 50k