Matching Records in two tables

[Richard Buttrey]

Hi,

I have two tables which I'm attempting to link with a common key
field. Table 1 contains all the records from table 2, plus additional
records not on table 2. Table 2 contains records that are not in table
1.

The field I'm matching on is common to both, and many records contain
the same key field value, but differences in other fields.

The task is to get the data from a non common field in table 2, and
add these field values to the table 1 records where the key field
matches.

I've tried to use an =MATCH() function, in table 1 to find the
equivalent row in table 2, but of course where there are more than one
record with the same key value, the MATCH() returns the same row
number. For instance with values A,B,C,C,C,D in table 2 rows 1:6, and
the same record values somewhere in table1, using a match in table
1for say value C in table 2, returns row 3 for the three 'C' values,
and not rows 3,4 & 5

I was hoping to use this Match value to either sort table 1 into
exctly the same order as table 2, and then copy the non common field
from table 2 into table1. Or alternatively loop down table 2 with a
macro and identify which rows need blank rows introducing to line up
with table 1.

I guess I need some variety of a countif() or sumproduct() function,
but I've not yet found the right flavour.

Can anyone offer any advice please?

Usual TIA


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Pete_UK]

Hi Richard,

One approach would be to convert those duplicate values into uniques -
one way is like this in a helper column:

=A2&"_"&COUNTIF(A$2:A2,A2)

and copy down, assuming your values A,B,C,C,C,D are in column A
starting in row 2. These will show as A_1,B_1,C_1,C_2,C_3,D_1 in the
helper column, so they have been converted into unique references by
adding a sequential number to the end.

Hope this helps.

Pete

 

[michael.beckinsale]

Hi Richard,

Try this utility which l wrote some time ago.

It matches records in 2 lists by the value of the leftmost column in
each record. Where a matching record cannot be found a blank cell is
inserted.

Please let me know how you get on with it as l am planning to publish
it as an add-in.

Link:

http://www.excelexperts.co.uk/testweb.htm

It is on the downloads page and is called 'Match 2 Lists'

Regards

Michael Beckinsale