Combining Two Similar Lists?

J

Jeff

Hello,
I have two related lists of data. One has three columns
of information [Name (L1A), Birth Date (L1B),
Relationship (L1C)], with 520 rows and the other has 4
column's of information (Name (L2A), Birth Date (L2B),
Death Date (L2C), Spouse (L2D)] with 675 rows. Names
and Birth Dates are the same in both lists and
Relationship, Death Date, and Spouse are unique to both
lists.
L1A = L2A and L1B = L2B
L1C, L2C, and L2D have unique data.
How do I combine the lists into one list with 4 columns,
dropping those records that are not found in both lists?
[Name, Birth Date, Death Date, Spouse, Relationship]
Thanks in advance.
Jeff
 
M

Max

One possible approach ..

In Sheet1
-----------
Headers in A1:C1 are: Name, Birth Date, Relationship
Data runs from row2 down to row521

In Sheet2
-----------
Headers in A1:D1 are: Name, Birth Date, Death Date, Spouse
Data runs from row2 down to row676

Put in E1: Relationship (label)

Put in E2:

=IF(ISNA(MATCH(1,(Sheet1!$A$2:$A$521=A2)*(Sheet1!$B$2:$B$521=B2),0)),"",INDE
X(Sheet1!$C$2:$C$521,MATCH(1,(Sheet1!$A$2:$A$521=A2)*(Sheet1!$B$2:$B$521=B2)
,0)))

Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
(instead of just pressing ENTER)

Put in F2: =IF(OR(E2=0,E2=""),"",ROW())

Select E2:F2, fill down 675 rows

Col E will match and return the Relationships (if any) from Sheet1
(unmatched cases will return "")
while col F sets up an arbitrary col for us
to extract the desired records into Sheet3

In Sheet3
-----------
With headers in A1:E1
: Name, Birth Date, Death Date, Spouse, Relationship

Put in A2:

=IF(ISERROR(MATCH(SMALL(Sheet2!$F:$F,ROW(A1)),Sheet2!$F:$F,0)),"",OFFSET(She
et2!$A$1,MATCH(SMALL(Sheet2!$F:$F,ROW(A1)),Sheet2!$F:$F,0)-1,COLUMN(A1)-1))

Copy A2 across to E2, then down 675 rows
Format cols B and C as dates

This'll return only the desired records from Sheet2
dropping off those unmatched cases in Sheet2
 
F

Frank Kabel

Hi Jeff
try the following:
1. In your first list in D1 enter the following formula (entered as
array formula with CTRL+SHIFT+ENTER):
==IF(ISNA(MATCH(1,('list2'!$A$1:$A$700=A1)*('list2'!$B$1:$B$700=B1),0))
,"XXX",INDEX('list2'!$C$1:$C$700,MATCH(1,('list2'!$A$1:$A$700=A1)*('lis
t2'!$B$1:$B$700=B1),0)))

Copy this down and afterwards filter all rows with 'XXX' and delete
them. For your other columns just change the first parameter of the
INDEX function
 
J

jEFF

Thanks for your help. I beleive I followed your
directions. I got an XXX for every entry. Not sure what
I did wrong.
Jeff
-----Original Message-----
Hi Jeff
try the following:
1. In your first list in D1 enter the following formula (entered as
array formula with CTRL+SHIFT+ENTER):
==IF(ISNA(MATCH(1,('list2'!$A$1:$A$700=A1)*('list2'! $B$1:$B$700=B1),0))
,"XXX",INDEX('list2'!$C$1:$C$700,MATCH(1,('list2'!
Click to expand...
$A$1:$A$700=A1)*('lis
t2'!$B$1:$B$700=B1),0)))

Copy this down and afterwards filter all rows with 'XXX' and delete
them. For your other columns just change the first parameter of the
INDEX function


--
Regards
Frank Kabel
Frankfurt, Germany

Hello,
I have two related lists of data. One has three columns
of information [Name (L1A), Birth Date (L1B),
Relationship (L1C)], with 520 rows and the other has 4
column's of information (Name (L2A), Birth Date (L2B),
Death Date (L2C), Spouse (L2D)] with 675 rows. Names
and Birth Dates are the same in both lists and
Relationship, Death Date, and Spouse are unique to both
lists.
L1A = L2A and L1B = L2B
L1C, L2C, and L2D have unique data.
How do I combine the lists into one list with 4 columns,
dropping those records that are not found in both lists?
[Name, Birth Date, Death Date, Spouse, Relationship]
Thanks in advance.
Jeff
Click to expand...

.
Click to expand...
 
J

Jeff

Hello,
Thanks for your help. I must have done something wrong
because although I see the folurma you gave me in the
dialoge box the value in the cell is blank.
Thanks
Jeff
-----Original Message-----
One possible approach ..

In Sheet1
-----------
Headers in A1:C1 are: Name, Birth Date, Relationship
Data runs from row2 down to row521

In Sheet2
-----------
Headers in A1:D1 are: Name, Birth Date, Death Date, Spouse
Data runs from row2 down to row676

Put in E1: Relationship (label)

Put in E2:

=IF(ISNA(MATCH(1,(Sheet1!$A$2:$A$521=A2)*(Sheet1! $B$2:$B$521=B2),0)),"",INDE
X(Sheet1!$C$2:$C$521,MATCH(1,(Sheet1!$A$2:$A$521=A2)*
Click to expand...
(Sheet1!$B$2:$B$521=B2)
,0)))

Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
(instead of just pressing ENTER)

Put in F2: =IF(OR(E2=0,E2=""),"",ROW())

Select E2:F2, fill down 675 rows

Col E will match and return the Relationships (if any) from Sheet1
(unmatched cases will return "")
while col F sets up an arbitrary col for us
to extract the desired records into Sheet3

In Sheet3
-----------
With headers in A1:E1
: Name, Birth Date, Death Date, Spouse, Relationship

Put in A2:

=IF(ISERROR(MATCH(SMALL(Sheet2!$F:$F,ROW(A1)),Sheet2! $F:$F,0)),"",OFFSET(She
et2!$A$1,MATCH(SMALL(Sheet2!$F:$F,ROW(A1)),Sheet2!
Click to expand...
$F:$F,0)-1,COLUMN(A1)-1))

Copy A2 across to E2, then down 675 rows
Format cols B and C as dates

This'll return only the desired records from Sheet2
dropping off those unmatched cases in Sheet2
--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
Hello,
I have two related lists of data. One has three columns
of information [Name (L1A), Birth Date (L1B),
Relationship (L1C)], with 520 rows and the other has 4
column's of information (Name (L2A), Birth Date (L2B),
Death Date (L2C), Spouse (L2D)] with 675 rows. Names
and Birth Dates are the same in both lists and
Relationship, Death Date, and Spouse are unique to both
lists.
L1A = L2A and L1B = L2B
L1C, L2C, and L2D have unique data.
How do I combine the lists into one list with 4 columns,
dropping those records that are not found in both lists?
[Name, Birth Date, Death Date, Spouse, Relationship]
Thanks in advance.
Jeff
Click to expand...


.
Click to expand...
 
M

Max

Jeff said:
Hello,
Thanks for your help. I must have done something wrong
because although I see the folurma you gave me in the
dialoge box the value in the cell is blank.
Thanks
Jeff
Click to expand...

The problem could be due to "invisible"
leading/trailing or extra in-between
white spaces in the names under the Name col A in Sheet1
which is throwing the matching off with the names in Sheet2

Try this formula instead
[with TRIM() added]

Put in E2 (of Sheet2):

=IF(ISNA(MATCH(1,(TRIM(Sheet1!$A$2:$A$521)=A2)*(Sheet1!$B$2:$B$521=B2),0)),"
",INDEX(Sheet1!$C$2:$C$521,MATCH(1,(TRIM(Sheet1!$A$2:$A$521)=A2)*(Sheet1!$B$
2:$B$521=B2),0)))

Remember to *array-enter* the formula in E2, i.e.
Press CTRL+SHIFT+ENTER
(don't just press ENTER)

Pl feedback further ..
 
M

Max

On 2nd thought, might as well TRIM
the lookup names in col A of Sheet2 as well
just in case the problem is not just with
entries in Sheet1..

So try this amended formula:
[with TRIM() added to clean cols A in both Sheets 1 and 2]

Put in E2 (of Sheet2) and array-enter:

=IF(ISNA(MATCH(1,(TRIM(Sheet1!$A$2:$A$521)=TRIM(A2))*(Sheet1!$B$2:$B$521=B2)
,0)),"",INDEX(Sheet1!$C$2:$C$521,MATCH(1,(TRIM(Sheet1!$A$2:$A$521)=TRIM(A2))
*(Sheet1!$B$2:$B$521=B2),0)))

Post back how this turned out for you
 
Top