Excel question about consolidating data onto one sheet

[pete6032]

I have an Excel workbook that has company sales numbers for each state by year. Each state is a different tab, in column A is year, in column b is sales. I would like to consolidate all the sales number for each state onto one sheet, for each year.

I could do this by writing a vlookup formula on the consolidated sheet for each state but I would have to change the vlookup formula to reference each state (=VLOOKUP(A1,Alabama!$A$B,2,FALSE). This would be a pain in the ass to change the state name for all 51 tabs. Is there a faster way to consolidate this data onto one sheet?

 

[Paperdoc]

Try this. In the consolidated sheet, create the entire table. First Column is Year. Fill in all the years down that column. Next 50 columns are all Sales numbers, one column for each state. Each of these has a state name its the header. Over all of those is a master header saying "Yearly Sales in $" or some such.

Now, one column at a time, go to the first sales year cell. In that type =, then use your mouse to click on the separate sheet for that state and click on the cell for that year's Sales. This will enter into the cell of the consolidated sheet the reference to the year and sales cell for the correct State. Do this across the top line for each of the 50 states. When you have them all done, highlight the entire 50 cells across the top line with all those values already entered, and press CTRL-C to copy. Go back to the top left first state - first column cell, hold down the mouse key and scan done that column to the bottom year line. Release the mouse and it will highlight all of that big block. Hit CTRL-V to Paste, and it will copy all those references from the first line into all the other lines, adjusting each for the appropriate line numbers.

NOTE that this does not do any lookup to ensure that the correct year and state are being used in the consolidated sheet. It depends on having each of the individual 50 state sheets organized exactly correctly with the same years in the same places on each sheet.

 

[Scarpozzi]

Consolidate in Excel: Merge multiple sheets into one

The tutorial demonstrates different ways to combine sheets in Excel depending on what result you are after – consolidate data from multiple worksheets, combine several sheets by copying their data, or merge two Excel spreadsheets into one by the key column.

www.ablebits.com

 

[LurchFrinky]

I did not know about that Consolidate function. That's pretty neat.
And you can still use the VLOOKUP function, just use "INDIRECT($RC)" in place of "Alabama", and have all of your states as column headers (or row headers.)

 

[Scarpozzi]

I did not know about that Consolidate function. That's pretty neat.
And you can still use the VLOOKUP function, just use "INDIRECT($RC)" in place of "Alabama", and have all of your states as column headers (or row headers.)

I've done something similar with Powershell, but used it to open the xlsx file in an Excel object and save worksheets as CSV files, then manipulated those as separate datasets in nested for each loops.

 

[GrumpyMan]

Use ASAP Utilities. I've merged so many spread sheets....anyway, it has over 300 macros for this type of thing and other cool Excel things.

 

[ringtail]

I used to do that a lot, but haven't in a while.
Maybe you'll be helped from these links taught me how to do it (it works well once you figure it out).

RDBMerge, Excel Merge Add-in for Excel for Windows <=maybe best of this list

Merging Data from Multiple Workbooks into a Summary Workbook in Excel

Merging Data from Multiple Workbooks into a Summary Workbook in Excel 2010

Extract data from multiple cells of closed Excel files