Consolidating 2 tables into one table

Technurse...Excel has something called MS Query built in that will let you do this (and much much more) provided your tables are in the same workbook but in different sheets.

Google MS Query for more detailed instructions, but here's a rough outline.

1. If using Excel 2007+, then from the Data tab, select 'From Other Sources' and then select 'Excel Files' from the dialog box that comes up and click OK.
2. THen in the dialog box that comes up, navigate to the file where your data is and press Okay.
3. Expand the plus sign next to the sheet where your first table is, select the columns you want from the pane on the left, and click the >. These columns will appear in the pane on the right
4. Do the same for the sheet where your second table is.
5. A warning will come up about having to define joins in Microsoft Query. Click OK.This opens Microsoft Query. You’ll see two boxes near the top that say “Sheet1$” and “Sheet2$” that have your columns listed in them.
6. Click on the Two in the Sheet1 box, and while holding down click, draw a line over to Two in the Sheet2 box. If you look in the bottom pane you’ll now see that MS Query has joined the data how you want.
7. Now click the Return Data icon (4[SUP]th[/SUP] icon from the left that looks like a open folder with an arrow pointing to it).

This will take you back to Excel, where you can choose if you want to import this data as a table or as a PivotTable. Make your choice, choose where you want the table/pivottable to appear, and click OK.

note that if you have Excel 2010, you can use Microsoft's PowerPivot addin to do this. Best do some googling for instructions.

Thank you, I figured it out, actually quite simple.

All that was needed was to use a vlookup formula.

Ahhh...for some reason I thought you had duplicates in one or both tables, which VLOOKUP wouldn't handle.

Thanks for the headsup. I wasn't aware about the duplicate limitation.

Here's the vlookup used: =VLOOKUP(A451,Worksheet B!$1:$1048576,3,FALSE) Where A451 is the cell value to compare in WORKSHEETA(Table 1), WORKSHEETB!$1:$1048576 is the range on Worksheet B(TABLE 2) to compare, 3 is the column location on Worksheet B (Table 2) who's data value I pulled to Worksheet A (Table 1).

JeffreyWeir said:

Ahhh...for some reason I thought you had duplicates in one or both tables, which VLOOKUP wouldn't handle.

Click to expand...