Excel add on to join two tables

  • Thread starter Thread starter ~Rachel mary~
  • Start date Start date
R

~Rachel mary~

I am looking for an inexpensive Excel add on to join two tables (or
worksheets) using a unique identifier column (record ID).

Any suggestions?
 
What do you mean by "join" - just butt them together (sideways or at
the bottom?) or to add/subtract values for each corresponding ID ??
You will need to give examples of how your data is laid out if you
want more specific help here, but VLOOKUP or INDEX/MATCH functions can
help you to do this.

Hope this helps.

Pete
 
Rachel,

You probably know this, but I'll give it a go anyway. You can use VLOOKUP formulas to get
the fields you want in the join. Put them in the "many" table, and have them look up the
stuff in the "one" table. the VLOOKUP has the restriction that the key (ID) column must be
left of the column being retrieved. YOu can use MATCH/INDEX combinations to work around
that.

Or use a bona fide data base program! :)

Couldn't resist.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
I wish I wish they'd let us use a real database. But I am stuck with
Excel :(

This needs to be done fairly automatically (like with a wizard) - no
coding, minimal cutting/pasting, etc. We may do this a lot, so we want
a nice tool, not a one-time hack.

The two tables to be merged may have over a thousand records (rows).
Each table may 15 - 30 fields (columns).

Example:

I have the following two tables each in their own worksheet in the
same workbook.

Table 1
record ID, task name, person assigned, date completed
1, change light bulb, mike, 6/6/07
2, move furniture, jay 6/7/07
3, move furniture, jay, 6/7/07
4, plumbing problem, janet, 6/9/07


Table 2
task name, record ID, date completed, customer, customer satisfaction
score
change light bulb, 1, 6/6/07, Sally, 4
move furniture, 3, 6/7/07, Dave, 3

I want to get one table in its own worksheet. The order of the fields
in the new table doesn't matter.

Table 3
record ID, task name, person assigned, date completed, customer,
customer satisfaction score
1, change light bulb, mike, 6/6/07, Sally, 4
2, move furniture, jay 6/7/07, (no data), (no data)
3, move furniture, jay, 6/7/07, Dave, 3
4, plumbing problem, janet, 6/9/07, (no data), (no data)

I do not need to do any calculations during the merge.

Let me know if any other information will be useful.

Thanks in advance for the advice!
 
It strikes me that you want to merge two columns from Table 2 into
Table 1, and your example implies that you may not have as many
entries in Table 2 as in Table 1. So, first of all add the headings
"Customer" and "Satisfaction Score" to cells E1 and F1 of Table 1.
Then put this formula in E2:

=IF(ISNA(VLOOKUP(A2,'Table 2'!B$2:D$2000,2,0)),"",VLOOKUP(A2,'Table 2'!
B$2:D$2000,2,0))

and this one in F2:

=IF(ISNA(VLOOKUP(A2,'Table 2'!B$2:D$2000,3,0)),"",VLOOKUP(A2,'Table 2'!
B$2:D$2000,3,0))

Notice that I've made the lookup table down to row 2,000 - you can
adjust this if you wish, but this should ensure that you cover every
row if you would normally have about 1,000. Note also that the only
difference in the formulae is that the first has a 2 as the third
parameter in the VLOOKUP and the second has a 3. This relates to the
column in the lookup table that the data is returned from (as measured
from column B), and if you are going to have a number of VLOOKUP
formulae then you could make use of:

=IF(ISNA(VLOOKUP(A2,'Table 2'!B$2:Z$2000,COLUMN(B2),
0)),"",VLOOKUP(A2,'Table 2'!B$2:Z$2000,COLUMN(B2),0))

and then just copy this one formula across - the B2 will change to C2,
D2, E2 etc, which will effectively mean 2, 3, 4, 5 etc. By changing
the lookup table to cover up to column Z in this formula, this will
accommodate up to 25 columns in all - adjust to suit.

Then copy these two formulae down to the bottom of Table 1 - an easy
way is to double-click the fill handle (the small black square in the
bottom right corner of the cursor). You can then fix the values to get
rid of the formulae - highlight all the cells with the formulae in (E2
to F1000), click <copy> then Edit | Paste Special | Values (check) |
OK then <Enter>.

You can now delete the Table 2 sheet, rename Table 1 to Table 3 and
use File | Save As to save your file with a different name.

If you expect to repeat this several times in the future, then you can
record a macro while you do it once, and then you can just playback
that macro when you need to. Obviously, if you have 15 to 30 fields in
reality then your example is simplified, but you should be able to
adapt the above to suit your circumstances.

Hope this helps.

Pete
 
Back
Top