i refuse to do busy work

August 7, 2009 5:53 AM

I need to transpose a lot of data in Excel. How can I do it efficiently?

The data in the worksheet is information on international calling rates such as:

Andorra
0.158$
0.101$
0.092$
0.0840$

However, I need the data to look like this with each piece of data in its own column:

Andorra 0.158$ 0.101$ 0.092$ 0.0840$

Now, I have over 200 countries with 3 or 4 rates; so it would take me forever to manually transpose all the data in a copy and paste fashion. Is there anyway to do this quickly?

posted by bobber to Computers & Internet (19 answers total)

Paste Special -> Transpose
posted by SuperSquirrel at 5:59 AM on August 7, 2009

Response by poster: Yes I know I can do Paste Special -> Transpose, but there are 1000s of cells of data and that would take a century :(
posted by bobber at 6:00 AM on August 7, 2009

What happens if you select the entire table and transpose it?
posted by chrismear at 6:06 AM on August 7, 2009

1000s of cells is no big deal. Copy the whole table, paste special and transpose. Have a coffe if your computer is slow.
posted by molecicco at 6:10 AM on August 7, 2009

Do you have 600-800 cells, or 1000s? Either way, I can't see how it would take that long to copy/paste. Excel can handle much more, like an order of magnitude more.
posted by SuperSquirrel at 6:11 AM on August 7, 2009

Yeah, in my expereince Excel only gets really clunky when you are well into the 100K range.
posted by molecicco at 6:17 AM on August 7, 2009

Response by poster: Here it is better explained

All of this information is in column A:

Afghanistan
0.6070$
Albania
0.428$
0.247$
0.224$
0.2060$
Albania Mobile
0.315$
0.296$
0.259$
0.2405$
Algeria
0.491$
0.252$
0.229$
0.2100$
Algeria - Mobile/Special Services
0.506$
0.337$
0.306$
0.2810$

I want to look like this: Countries in column A, prices in columns B, C, D and E

Afghanistan 0.6070$
Albania 0.428$ 0.247$ 0.224$ 0.2060$
Albania Mobile 0.315$ 0.296$ 0.259$ 0.2405$
Algeria 0.491$ 0.252$ 0.229$ 0.2100$
Algeria - Mobile/Special Services 0.506$ 0.337$ 0.306$ 0.2810$


so if i hilight everything that is column A, copy it, paste special -> transpose; it doesn't quite understand what to do. I can manually select the prices under each country and past special into column B and it will transpose, but that will take forever and I want to impress my boss:). there are 2400 rows going all the way down.
posted by bobber at 6:22 AM on August 7, 2009

Text to columns and then concatenate based on... That's where I am getting stuck, the delimiter parameters for merging the cells. Where did you get the data from, is it by any chance in a csv somewhere? I swear I've figured out how to do this in the past.
posted by 8dot3 at 6:26 AM on August 7, 2009

Response by poster: No the data was in a PDF where it was already built into a table, but copying and pasting into Excel puts all the data into column A.
posted by bobber at 6:34 AM on August 7, 2009

Have you tried pasting it into Word first? I've found that, for whatever reason, sometimes Word will interpret a PDF table as a table when Excel won't, but then you can copy that table and paste it into Excel. Not always, but worth a shot.
posted by emumimic at 6:48 AM on August 7, 2009

Ah, this isn't really a "transpose" problem, then. :-) Sorry - I assumed based on your initial explanation that each country and data was in its own column.

OK - are the cells with numerical data formatted as Currency? Or is everything formatted as Text or General?
posted by SuperSquirrel at 6:53 AM on August 7, 2009

Well...this is kind of tricky. I might try a couple of things. First, since it was in a table in the PDF file, I would try going back to that, do a save/as to a .doc or rich text format and then try copying it from Word into Excel--then it may recognize the table format and save you a lot of trouble.

Otherwise, it sounds like you need something programmicably to go down the list to evaluate each section and transpose each chunk between text heading. But, since this is all likely recognized as text, I'm not sure of an easy way to do that either.

Barring all else, I would say screw it--go through a section at a time (i.e., highlight all of Afganistan's data first), transpose that chunk to a new worksheet, transpose the next section, etc. Yes, it will be a pain in the ASS, but seriously, with a little over 2,000 rows, you will spend less time doing this than trying to write VBA code to do it.
posted by Eicats at 6:53 AM on August 7, 2009

Response by poster: I got it!! I copied and pasted it correctly from the PDF as a table. Apparently my coworkers like playing sick jokes on me:)
posted by bobber at 6:54 AM on August 7, 2009

To tag onto emumimic's idea, if you paste it into word, come up with the right find and replace pattern matching for the table (like wildcard number, carriage return, wildcard letter means you know you are at a new country section) and add a comma or something there, so then you can import the csv into excel with the comma as the delimiter. THEN you could test to columns.
posted by 8dot3 at 6:55 AM on August 7, 2009

For future reference, if you can get a label on each line next to the number, then you can use a pivot table to turn that into columns.
posted by smackfu at 7:17 AM on August 7, 2009

copy to csv and write a simple perl/python/whatever script.
posted by ROU_Xenophobe at 7:26 AM on August 7, 2009 [1 favorite]

Save as text, then use the following regular expression, save, and reload. I used ; as the delimiter, since commas may appear in your names. Feel free to choose something else. Note that this is vi style, other editors may not want you to escape the parentheses.
%s/\n\(\d\)/;\1/g
posted by a robot made out of meat at 9:18 AM on August 7, 2009

What I usually do when I have this problem, is just to create a quick macro that selects, copies, pastes special, moves to start of next row. Make sure macro is set to relative references.
Then just ctrl-macrokey over and over and over again.
Then I sort the columns, move a few over here and there when the macro has messed up..
posted by dripped at 3:10 PM on August 7, 2009

The following VBA will do it, presuming that all your prices start with a zero, there are no gaps in your list, and your list starts at cell A1 on the active sheet.


Sub transpose_and_a_bit()

Dim results() As String
ReDim results(0)

Set wsSource = ActiveSheet
Set wsDestination = Worksheets.Add

wsSource.Activate
Range("A1").Activate

While ActiveCell.Value & Selection.Offset(1, 0).Value <> ""
If Left(ActiveCell.Value, 1) <> "0" Then ' or change this to something like "if instr(activecell.value,"$") > 1 Then..."
'to test for dollar signs rather than zeroes.
If UBound(results()) > 0 Then
wsDestination.Activate
For i = LBound(results) To UBound(results)
ActiveCell.Offset(i, 0).Value = results(i)
Next i
Selection.Offset(0, 1).Select
wsSource.Activate
End If
ReDim results(0)
results(0) = ActiveCell.Value
Selection.Offset(1, 0).Select
Else
ReDim Preserve results(UBound(results()) + 1)
results(UBound(results())) = ActiveCell.Value
Selection.Offset(1, 0).Select
End If

Wend

wsDestination.Activate
For i = LBound(results) To UBound(results)
ActiveCell.Offset(i, 0).Value = results(i)
Next i

End Sub


Sorry about the lack of indentation.
posted by pompomtom at 6:55 PM on August 11, 2009