How to compare Two worksheets and gerenerate differences in 3rd

**Krishna Sastry** · 07-05-2009, 08:46 AM

Hi,
I am looking for someone to help with this excel task.
I have two worksheets.
Sheet1 is a Master List and Sheet2 is subset of Master List.
Now I have to separate those records which are there in Master List, but not there is Sheet2 and list them in Sheet3.

Example:
Sheet1 contains records: a, b, c, d, e, f in rows
Sheet2 contains records: a, d, e in rows
Sheet3 must show: b, c, f in rows

I need this done immediately, so any help would be appreciated.
Thanks,
Krishna Sastry

**Domenic** · 07-05-2009, 09:42 AM

Maybe something like the following...

Assumptions

Sheet1!A2:A7 contains A, B, C, D, E, and F

Sheet1!B2:E7 contains the corresponding data

Sheet2!A2:A4 contains A, D, and E

Sheet2!B2:E4 contains the corresponding data

Formulas

Sheet1!F1: 0

(Enter a zero in F1.)

Sheet1!F2, and copied down:

=IF(A2<>"",IF(ISNA(MATCH(A2,Sheet2!$A$2:$A$4,0)),LOOKUP(9.99999999999999E+307,Sheet1!$F$1:F1)+1,""), "")

Sheet3!A2:

=LOOKUP(9.99999999999999E+307,Sheet1!F1:F7)

Sheet3!B2, copied across and down:

=IF(ROWS(B$2:B2)<=$A$2,LOOKUP(ROWS(B$2:B2),Sheet1!$F$2:$F$7,Sheet1!A$2:A$7),"")

See sample file attached...

**Krishna Sastry** · 07-05-2009, 01:28 PM

Hi Domenic,
That's brilliant. It worked. Thanks a lot for your timely help. I was trying with LOOKUP functions for quite some time, but no luck.
I understood your logic except that 9E+307 thing, can you shed some light on this please. I think this is what made the difference.
Regards,
Krishna Sastry

**Domenic** · 07-05-2009, 01:37 PM

You're very welcome! The following number...

9.99999999999999E+307

...is the largest number recognized by Excel. When used as a lookup value, LOOKUP returns the last numerical value in the lookup range.