Results 1 to 5 of 5

Thread: Consolidating 2 tables into one table

  1. #1

    Consolidating 2 tables into one table



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have 2 tables

    Table 1

    Column A, Column B

    Table 2

    Column B, Column C

    How can I get a table to display the following in one row.

    Column A, Column B, Column C (Where Column B is the same value for both tables - KEY). The order doesn't matter eg, it could be column B, A, C

    I've tried pivot tables, but the data isn't displayed across in a row. Tried merging and consolidating, but I must be missing a step.

    Can someone step me thru the process? All the values are TEXT values but sometimes Column B does not have any values (blanks) for some of the rows of data.

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    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 (4th 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.

  3. #3

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

    All that was needed was to use a vlookup formula.

  4. #4
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Ahhh...for some reason I thought you had duplicates in one or both tables, which VLOOKUP wouldn't handle.

  5. #5
    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).

    Quote Originally Posted by JeffreyWeir View Post
    Ahhh...for some reason I thought you had duplicates in one or both tables, which VLOOKUP wouldn't handle.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •