|
Compare two worksheets for missing information
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 |
Compare two worksheets for missing information
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 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 |
Compare two worksheets for missing information
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 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 |
Compare two worksheets for missing information
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 |
Compare two worksheets for missing information
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 |
Compare two worksheets for missing information
No problem...glad it helped
-- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk 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 |
| All times are GMT +1. The time now is 03:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com