Compare two worksheets for missing information

[Naba via OfficeKB.com]

Hi all,

Recently i was given an excel workbook with two worksheet, both contain the
same data up a 10000 records each, i want to compare the two work sheets and
than copy the missing data to another worksheet using macro, if anyone can
help me with this please i would be grateful.

 

[Nick Hodge]

Naba

Use a VLOOKUP function on each set of data, like

=VLOOKUP(A2,Sheet2!$A$1:$A$10000,1,FALSE)

and then any #N/A errors will be where data does not exist in the lookup
table (In this case A1:A10000)

You can then autofilter and copy the data elsewhere, repeat for the other
data

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Naba via OfficeKB.com]

Naba

Use a VLOOKUP function on each set of data, like

=VLOOKUP(A2,Sheet2!$A$1:$A$10000,1,FALSE)

and then any #N/A errors will be where data does not exist in the lookup
table (In this case A1:A10000)

You can then autofilter and copy the data elsewhere, repeat for the other
data

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

Thanks but i still can't work out what's happening,i'm a newbbie, tried what you said but all i have is NA s on all my data,which means all my dat is missing.

My books is has two sheets with data, something like this;

Sheet1
A B C D
--------------------------------------------------
1 1001 Joe 02/02/06 M
2 1002 Sammy 04/08/05 F
3 1005 Kila 05/05/03 F
4 1004 Beth 11/06/05 F

Sheet2
A B C D
--------------------------------------------------
1 1001 Joe 02/02/06 M
2 1002 Sammy 04/08/05 F
3 1005 Kila 05/05/03 F
4 1003 Bob 02/06/02 M

i kind of want to extact the data thats not on sheet1 or sheet2 to sheet3,
something like this:
Sheet3
A B C D[/QUOTE]

 

[Nick Hodge]

Naba

The theory still holds. (Using your example), in Sheet1 E1 enter

=VLOOKUP(A1,Sheet2!$A$1:$A$10000,1,FALSE)

This presumes your sheet2 is actually called Sheet2

The repeat on Sheet2 replacing the Sheet2 reference with Sheet1 in the
formula

If this returns all #N/As then almost certainly other ways would do the same
as the data does not 'match'. This can be caused by invisible characters in
the data or one set of data being 'text', while the others are 'numbers',
although they may look the same.

If you still have problems get back

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Naba via OfficeKB.com]

Naba

The theory still holds. (Using your example), in Sheet1 E1 enter

=VLOOKUP(A1,Sheet2!$A$1:$A$10000,1,FALSE)

This presumes your sheet2 is actually called Sheet2

The repeat on Sheet2 replacing the Sheet2 reference with Sheet1 in the
formula

If this returns all #N/As then almost certainly other ways would do the same
as the data does not 'match'. This can be caused by invisible characters in
the data or one set of data being 'text', while the others are 'numbers',
although they may look the same.

If you still have problems get back

 

[Naba via OfficeKB.com]

Naba

The theory still holds. (Using your example), in Sheet1 E1 enter

=VLOOKUP(A1,Sheet2!$A$1:$A$10000,1,FALSE)

This presumes your sheet2 is actually called Sheet2

The repeat on Sheet2 replacing the Sheet2 reference with Sheet1 in the
formula

If this returns all #N/As then almost certainly other ways would do the same
as the data does not 'match'. This can be caused by invisible characters in
the data or one set of data being 'text', while the others are 'numbers',
although they may look the same.

If you still have problems get back

 

[Nick Hodge]

No problem...glad it helped

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)