R
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
__________________________
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
__________________________