Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.