How can I reformat some rows into columns in Excel?
December 6, 2010 8:16 AM Subscribe
How can I reformat some rows into columns in Excel?
Here's a sample of the kind of data I have:
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.00And 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.00I'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.
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
(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
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
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
posted by brainmouse at 8:34 AM on December 6, 2010
Transpose won't work as it will go from this:
posted by EndsOfInvention at 8:37 AM on December 6, 2010
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.00to 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.00Which is not what the Asker asked for.
posted by EndsOfInvention at 8:37 AM on December 6, 2010
Best answer: 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
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
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
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
posted by soelo at 8:44 AM on December 6, 2010
Best answer: 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
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
posted by a robot made out of meat at 10:36 AM on December 6, 2010
Response by poster: 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
posted by teg at 10:48 AM on December 6, 2010
Response by poster: (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
posted by teg at 10:51 AM on December 6, 2010
« Older Which world atlas has the most beautiful... | A technological solution for unsavvy editors Newer »
This thread is closed to new comments.
posted by madcaptenor at 8:17 AM on December 6, 2010