How can I reformat some rows into columns in Excel?

December 6, 2010 8:16 AM

How can I reformat some rows into columns in Excel?

Here's a sample of the kind of data I have:

ClientID  Date    Amount
123       Jan-1   $5.00
123       Jan-15  $6.00
123       Feb-1   $4.00
456       Jan-1   $7.00
456       Feb-1   $3.00

And I want to reformat it to:

ClientID  Jan-1  Jan-15  Feb-1
123       $5.00  $6.00   $4.00
456       $7.00  [NULL]  $3.00

I'm running Excel 2007. If it matters: there are only 7 different possible dates, but the number of clients is large. There won't be more than one record for any client-date combo. Also, this data is coming out of an Access database, so if there's an easier way to do this in a report in Access, I can do that instead.

posted by teg to Computers & Internet (14 answers total)

Select your data. Go to Edit, then Paste Special. Check the "Transpose" box.
posted by madcaptenor at 8:17 AM on December 6, 2010

Alright, I left out a step there. Select your data. Cut or copy. Go to Edit, then Paste Special. Check the "Transpose" box.

(I might be leaving out other steps, too; I don't actually have a copy of Excel here to test. The point is, Paste Special/Transpose is the magic words.)
posted by madcaptenor at 8:18 AM on December 6, 2010

You can also use TRANSPOSE() as an array function.
posted by dfriedman at 8:20 AM on December 6, 2010

Another possibility is to take your data as is and create a pivot table.

There is also, I believe, in Access, some kind of query that has pivot table-like functionality that you may want to play around with.
posted by dfriedman at 8:22 AM on December 6, 2010

Yeah I'm seconding the pivot table -- you don't actually want to transpose your data, you want to make it into a table that doesn't exist right now.
posted by brainmouse at 8:34 AM on December 6, 2010

Transpose won't work as it will go from this:

ClientID  Date    Amount
123       Jan-1   $5.00
123       Jan-15  $6.00
123       Feb-1   $4.00
456       Jan-1   $7.00
456       Feb-1   $3.00

to this:

ClientID     123     123     123     456     456 
Date         Jan-1   Jan-15  Feb-1   Jan-1   Feb-1
Amount       $5.00   $6.00   $4.00   $7.00   $3.00

Which is not what the Asker asked for.
posted by EndsOfInvention at 8:37 AM on December 6, 2010

Transpose will just filp your data around, while you are looking to summarize it.

To use a pivot table, go to the Insert tab, hit the arrow below PivotTable and select PivotTable. In the Pivot Table Field List that comes up on the right, drag the client ID in the row area, the date into the column area and the amount in the data area.
posted by soelo at 8:38 AM on December 6, 2010

Alright, transpose won't work. That's what I get for answering too quickly.
posted by madcaptenor at 8:39 AM on December 6, 2010

Sorry, "data area" should read "Values". Back in 2003 it was called data but now it is the one under the sigma symbol.
posted by soelo at 8:40 AM on December 6, 2010

Also, you will have to change the Field options on the Values to Sum since the default is Count so you will just get a bunch of ones. If you get zeroes when you change it to count, you will need to make sure your money column is formatted as currency. Then refresh the Pivot Table by right-clicking inside of it and selecting Refresh.
posted by soelo at 8:44 AM on December 6, 2010

If pivot tables don't work out, R is a great hammer. Export to testreshape.csv and the following code does what you want.

caveats: 1) it puts 'NA' as the answer where there was no data 2) I've done nothing to get the formatting how you might want it.

mydata <-data.frame(read.csv("testreshape.csv"))
mydata2 <-reshape(mydata, idvar="ClientID", timevar="Date", v.names="Amount", direction="wide")
names(mydata2)<-sub("Amount.", "", names(mydata2))
write.csv(mydata2, file="reshapedtestdata.csv")
posted by a robot made out of meat at 9:07 AM on December 6, 2010

oops, meant to add the argument row.names=FALSE to that last function.
posted by a robot made out of meat at 10:36 AM on December 6, 2010

Thanks, I finally get the point of pivot tables! Soelo, your instructions worked perfectly. And I'll keep R in mind if I ever need to do something more complicated.
posted by teg at 10:48 AM on December 6, 2010

(And thanks for the clarifications that I wasn't just looking to transpose. I should have stated that in the question.)
posted by teg at 10:51 AM on December 6, 2010