Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with cell compare in two worksheets
Hello all, I have a slight problem that I just can't seem to figure out how to accomplish. I have two excel sheets, one is a new master and the other is old master The new and old master sheets contain mostly the same data but not in the same order. I am trying to simply compare cell A1 of newmaster to all 13 cells in old master looking for a match and making a note in newmaster column B of the same row that says either "exists" or "notexist" So like, if my sheets looked like the following: NEWMASTER......................................... ...................OLDMASTER A1 pokemon.jpg....................................... ..................A1 beyblades.jpg A2 halo.jpg.......................................... .......................A2 pokemon.jpg A3 bleach.jpg........................................ .....................A3 onepiece.jpg A4 beyblades.jpg..................................... ..................A4 sailormoon.jpg I would be left with my newmaster looking like the following: NEWMASTER A1 pokemon.jpg..........B1 exists A2 halo.jpg..................B2 notexist A3 bleach.jpg...............B3 notexist A4 beyblades.jpg.........B4 exists I hope I have explained this well. I have included two files that represent a teeny snippet of the actual data. I have tried to do this myself for several days now by taking bits from a bunch of macros but I am not getting anywhere. I can compare as if they both had to be in same order, but not with them out of order. Thanks in advance for any help I can get on this! Bill Beecham +-------------------------------------------------------------------+ |Filename: oldmaster.txt | |Download: http://www.excelforum.com/attachment.php?postid=3736 | +-------------------------------------------------------------------+ -- billbeecham ------------------------------------------------------------------------ billbeecham's Profile: http://www.excelforum.com/member.php...o&userid=16286 View this thread: http://www.excelforum.com/showthread...hreadid=398686 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with cell compare in two worksheets
In your new master workbook, in column B, put:
=IF(ISNA(VLOOKUP(A1,[OldMasterWkBk.xls]Sheet1!$A$1:$A$13,1)),"notexist","exists") (i.e., if VLOOKUP fails to find a value, #NA is true) "billbeecham" wrote: Hello all, I have a slight problem that I just can't seem to figure out how to accomplish. I have two excel sheets, one is a new master and the other is old master The new and old master sheets contain mostly the same data but not in the same order. I am trying to simply compare cell A1 of newmaster to all 13 cells in old master looking for a match and making a note in newmaster column B of the same row that says either "exists" or "notexist" So like, if my sheets looked like the following: NEWMASTER......................................... ...................OLDMASTER A1 pokemon.jpg....................................... ..................A1 beyblades.jpg A2 halo.jpg.......................................... .......................A2 pokemon.jpg A3 bleach.jpg........................................ .....................A3 onepiece.jpg A4 beyblades.jpg..................................... ..................A4 sailormoon.jpg I would be left with my newmaster looking like the following: NEWMASTER A1 pokemon.jpg..........B1 exists A2 halo.jpg..................B2 notexist A3 bleach.jpg...............B3 notexist A4 beyblades.jpg.........B4 exists I hope I have explained this well. I have included two files that represent a teeny snippet of the actual data. I have tried to do this myself for several days now by taking bits from a bunch of macros but I am not getting anywhere. I can compare as if they both had to be in same order, but not with them out of order. Thanks in advance for any help I can get on this! Bill Beecham +-------------------------------------------------------------------+ |Filename: oldmaster.txt | |Download: http://www.excelforum.com/attachment.php?postid=3736 | +-------------------------------------------------------------------+ -- billbeecham ------------------------------------------------------------------------ billbeecham's Profile: http://www.excelforum.com/member.php...o&userid=16286 View this thread: http://www.excelforum.com/showthread...hreadid=398686 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with cell compare in two worksheets
THank you for trying to help me, but that still does not work. It jus says "exists" for every entry in the worksheet. I need something that basically says "check a1 in this sheet agains every cell in column a of the other sheet, then check a2 in this shee against every cell of column a in the other" ad nauseum. Appreciate the help though. Unless of course you think I may be missing something. Bill Beecha -- billbeecha ----------------------------------------------------------------------- billbeecham's Profile: http://www.excelforum.com/member.php...fo&userid=1628 View this thread: http://www.excelforum.com/showthread.php?threadid=39868 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with cell compare in two worksheets
try this.... Sub CheckExistence() Dim NewRange As Range Set NewRange = Range("'New Master'!A:A") Dim OldRange As Range Set OldRange = Range("'Old Master'!A:A") Dim NrIndex As Long Dim OrIndex As Long Dim SearchedFor As Range For NrIndex = 1 To NewRange.Rows.Count If NewRange.Item(NrIndex).Value < "" Then Set SearchedFor = OldRange.Find(NewRange.Item(NrIndex), LookIn:=xlValues) If Not SearchedFor Is Nothing Then Range("'New Master'!B" & NrIndex).Value = "Exists" Else Range("'New Master'!B" & NrIndex).Value = "DoesNotExist" End If End If Next NrIndex End Sub billbeecham Wrote: THank you for trying to help me, but that still does not work. It just says "exists" for every entry in the worksheet. I need something that basically says "check a1 in this sheet against every cell in column a of the other sheet, then check a2 in this sheet against every cell of column a in the other" ad nauseum. Appreciate the help though. Unless of course you think I may be missing something. Bill Beecham -- T-®ex ------------------------------------------------------------------------ T-®ex's Profile: http://www.excelforum.com/member.php...o&userid=26572 View this thread: http://www.excelforum.com/showthread...hreadid=398686 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with cell compare in two worksheets
Hi, that worked great on the sample sheets I attached, but when I ru the macro on the actual data I get the following error: Runtime error '1004' method 'Range' of object '_Global' failed Do you have any idea why this would occur? Too long of a list? I hav over 3000 rows. Thanks for helping, Bill Beecha -- billbeecha ----------------------------------------------------------------------- billbeecham's Profile: http://www.excelforum.com/member.php...fo&userid=1628 View this thread: http://www.excelforum.com/showthread.php?threadid=39868 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with cell compare in two worksheets
Hi! Notice the italized, colored, bold words in the following lines in th code i gave: Dim NewRange As Range Set NewRange = Range(*-"'New Master'!A:A"-*) Dim OldRange As Range Set OldRange = Range(*-"'Old Master'!A:A"-*) Your actual data are probably located in sheets named differently fro "New Master" and "Old Master". If this is the case, you should modif the lines above to reflect the actual sheets' names. ;) Dim NrIndex As *Long* Dim OrIndex As *Long* If you used the sample code for single columns, you should have no pro even if your list has more than 3000 items. (Long in excel can handle u to 2,147,483,647.) :) billbeecham Wrote: Hi, that worked great on the sample sheets I attached, but when I ru the macro on the actual data I get the following error: Runtime error '1004' method 'Range' of object '_Global' failed Do you have any idea why this would occur? Too long of a list? I hav over 3000 rows. Thanks for helping, Bill Beecha -- T-®e ----------------------------------------------------------------------- T-®ex's Profile: http://www.excelforum.com/member.php...fo&userid=2657 View this thread: http://www.excelforum.com/showthread.php?threadid=39868 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare Worksheets | Excel Discussion (Misc queries) | |||
How do I compare cells and if FALSE compare to next cell in EXCEL | Excel Worksheet Functions | |||
How do I compare two Excel worksheets for cell differences? | Excel Discussion (Misc queries) | |||
Compare cell content from two worksheets | Excel Programming | |||
Code to compare cell values from different worksheets | Excel Programming |