Match multiple columns in excel/access
September 8, 2011 12:41 PM Subscribe
I'm trying to merge multiple month's worth of data into one master spreadsheet with a master column on the left, and individual month's counts on the right. My data is laid out as such:
Current State: http://i.imgur.com/mR9PO.png
Desired State: http://i.imgur.com/k16ln.png
As you can see I'm going from the "fruit" columns being spread out across multiple columns, to having one master "fruit" column on the left, and individual monthly count columns on the right.
Can I do this easily in excel and/or access?
Current State: http://i.imgur.com/mR9PO.png
Desired State: http://i.imgur.com/k16ln.png
As you can see I'm going from the "fruit" columns being spread out across multiple columns, to having one master "fruit" column on the left, and individual monthly count columns on the right.
Can I do this easily in excel and/or access?
What you want is a pivot table.
Probably the easiest way to do this is
1) Make a new work sheet with month, fruit, total as columns
2) Paste all your monthly data into this new sheet in one big column, but ADD the month field (this should take two seconds in the source sheet. type in the month, copy it, paste it all the way down)
3) Select the new filled in worksheet and use the Wizard to create a Pivot Table. This will depend on what version of Excel you are using. Pre-2007 you choose "Create Pivot Table and Pivot Chart" from the "Data" menu.
That's it!
posted by jeb at 12:51 PM on September 8, 2011 [1 favorite]
Probably the easiest way to do this is
1) Make a new work sheet with month, fruit, total as columns
2) Paste all your monthly data into this new sheet in one big column, but ADD the month field (this should take two seconds in the source sheet. type in the month, copy it, paste it all the way down)
3) Select the new filled in worksheet and use the Wizard to create a Pivot Table. This will depend on what version of Excel you are using. Pre-2007 you choose "Create Pivot Table and Pivot Chart" from the "Data" menu.
That's it!
posted by jeb at 12:51 PM on September 8, 2011 [1 favorite]
Add a column between A and B, one between C and D, one between E and F (or before, or after, whichever).
This column will have the month, so you can just copy the month name all the way down that column.
Now you should have, for each month, three columns: fruit, month, count. Copy and paste to make it one set of three columns, then do a pivot table.
posted by jeather at 1:00 PM on September 8, 2011
This column will have the month, so you can just copy the month name all the way down that column.
Now you should have, for each month, three columns: fruit, month, count. Copy and paste to make it one set of three columns, then do a pivot table.
posted by jeather at 1:00 PM on September 8, 2011
This thread is closed to new comments.
It should be as simple as creating some column headers, inserting a new column in between quantity and type and some basic copy/pasting.
posted by jourman2 at 12:49 PM on September 8, 2011