Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have two tables with the same columns. Each table has a "name" column (unique key basically) and two amount columns.

Each name may appear in either or both table.

What I want to do is align them up so that each instance of the same name appears side by side.

Example:

If the "input" is

Table 1:
Name      Amount1   Amount2
Field A   1         2
Field B   3         4
Field C   5         6

Table 2
Name      Amount1   Amount2
Field B   7         8
Field D   9         10

Then the "output" would be:

Name      Amount1   Amount2       Name      Amount1   Amount2  
Field A   1         2
Field B   3         4             Field B   7         8
Field C   5         6
                                  Field D   9         10

Is there any way of doing this in Excel (short of writing a macro)?

share|improve this question

Basically, you make your third (joined) table with a list of all the names.

For the value cells, you need to do a lookup on the value columns of the other tables from that compares the name fields of the two tables.

You need to do a lookup for each value field (so 4 altogether in your example) but it should work.

OK check this spreadsheet for an example.

share|improve this answer
    
This works but is a little unwieldy with a large dataset. – Kris Jul 29 '10 at 16:52
    
It's easier if you use the $ tags on fields. Basically you set the top row for each column and can drag the definition down to the end. It's not perfect but it will certainly get the job done. – JNK Jul 29 '10 at 16:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .