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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200607/1

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 Removed)HIS


"Naba via OfficeKB.com" <u23570@uwe> wrote in message
news:62d40bec186bf@uwe...
> 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.
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...tions/200607/1

Nick Hodge wrote:
>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 Removed)
>
>> Hi Nick,
>>
>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
--------------------------------------------------
1 1004 Beth 11/06/05 F
2 1003 Bob 02/06/02 M

>> can you help or any one help please...
>> Thanks, i'd he gratefull.....

--
Message posted via http://www.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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(E-Mail Removed)HIS


"Naba via OfficeKB.com" <u23570@uwe> wrote in message
news:62dcb54058041@uwe...
> Nick Hodge wrote:
>>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 Removed)
>>
>>> Hi Nick,
>>>
>>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
> --------------------------------------------------
> 1 1004 Beth 11/06/05 F
> 2 1003 Bob 02/06/02 M
>
>>> can you help or any one help please...
>>> Thanks, i'd he gratefull.....
>
> --
> Message posted via http://www.officekb.com

Nick Hodge wrote:
>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

>Thanks Very much nick, you genus.

>>>> Thanks, Nick mate.....

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200607/1

Nick Hodge wrote:
>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

>Thanks Very much nick, you genus.

>>>> Thanks, Nick mate.....

--
Message posted via http://www.officekb.com

No problem...glad it helped

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(E-Mail Removed)HIS


"Naba via OfficeKB.com" <u23570@uwe> wrote in message
news:62e1076649659@uwe...
> Nick Hodge wrote:
>>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
>
>>Thanks Very much nick, you genus.
>
>>>>> Thanks, Nick mate.....
>
> --
> Message posted via http://www.officekb.com