Match multiple columns in excel/access

September 8, 2011 12:41 PM

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?

posted by thankyoumuchly to technology (3 answers total)

The phrase you're looking for is 'flat dataset'. What I'd recommend is getting the data to look something like this and then throwing it all into a pivot table (if you're familiar with that).

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

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]

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