Matching lists to give a new list

[jsclare]

I have had a look through the forum and have not seen anything simila
enough to help me so i wondered if any of you could help me.

Basically I have two sets of data one about 12,000 long and anothe
about 9,000 long.

They both have identical columns such as name , id number and a date
and both have different independant columns.

I want to some how , compare id number and date to make to the tw
sheets into one.

So if the id number equals the date from the two sheets the entry i
combined and moved to a new sheet.

Another problem i have is the dates might not be exactly the same, so
want to make another list showing the ones that are say plus or minus
week from the other that have the same id number.

and finally the ones that dont match at all I want to go to a thir
list.

I hope i have made some sort of sense, I am not to sure where to star
with this one

 

[Frank Kabel]

Hi
as a starting point have a look at
http://www.cpearson.com/excel/duplicat.htm#ExtractingCommon
http://www.cpearson.com/excel/duplicat.htm#InOneNotOther

 

[Pdelpriore]

Basically I have two sets of data one about 12,000 long and another

about 9,000 long.

They both have identical columns such as name , id number and a date.
and both have different independant columns.

I want to some how , compare id number and date to make to the two
sheets into one.

So if the id number equals the date from the two sheets the entry is
combined and moved to a new sheet.

Another problem i have is the dates might not be exactly the same, so i
want to make another list showing the ones that are say plus or minus a
week from the other that have the same id number.

and finally the ones that dont match at all I want to go to a third
list.

I have a similar problem. I have two columns of unequal lengths. A1 to A37 and
L1 to L72. Some of the numbers in the A column are in the L column. I want to
compare each cell in A to each cell in L and if they match to put that number
in a third column in the same row as the cell in the A column. The numbers in A
and L are unique in ascending order.

Paul

 

[Frank Kabel]

Hi
also as a starting point have a look at
http://www.cpearson.com/excel/duplicat.htm#ExtractingCommon
http://www.cpearson.com/excel/duplicat.htm#InOneNotOther

 

[Pdelpriore]

Frank,
I took a look at them but they use Array formulas which require that the two
lists are equal in length. In my case they are very different.

Paul

 

[Frank Kabel]

Hi
the list don't have to be of equal length. It works also with different
lengths

 

[Pdelpriore]

Frank,

I'll try it again.

Paul

 

[Pdelpriore]

Hi

the list don't have to be of equal length. It works also with different
lengths

Frank,

Your right. It works great!

Thanks.

Paul

 

[jsclare]

Thanks for your help, I had a look at links provided and while i foun
them useful they did not help me solve my problem 100%.


I came up with the formula:

=IF (COUNTIF ( SHEET1 ID1 : SHEET1 DATE1, SHEET2 ID1: SHEET2 DATE 10)

0 , "MATCH", "NO MATCH")

Matching the first entry of sheet 1 and against all the entries o
Sheet 2. The only problem I have is that it returns a Match is only on
of the conditions are met not both. I need it to give a Match when onl
the ID and Date on sheet 1 match the ID and Date on sheet 2.
Is there a way i can put an AND between the first part.
SHEET 1 ID1 AND SHEET1 DATE1.
Or set it up so that if SHEET 1 ID1 Matchs any of the ID's in the whol
range on SHEET2 ID then it checks the SHEET 1 DATE against the SHEET
DATE

 

[jsclare]

No to worry i think i have cracked it using
=IF (( SHEET 1 ID1 <> SHEET2 ID RANGE), "No Match", (IF ( SHEET 1 DAT
1 = SHEET 2 DATE RANGE), "Date Match", "ID Match")