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

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

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

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

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

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

T-®ex,

Will try again. Ya, I knew to change the bolded info. I did that. It'
weird, I tried it on the two sheets I posted and it worked beautifully
Then I tried it on my actual sheets and it gave that error. So I figure
I changed something by accident. I re-copied your code and tried agai
and still got that error. I even got the idea to try it again on th
samples and see if maybe I was overlooking something, but it stil
errored.

I have to be overlooking something, because I know I am not crazy.
know it worked on the samples once.

I will try again tonight after work.

Thanks muchly!

Bil

--
billbeecha
-----------------------------------------------------------------------
billbeecham's Profile: http://www.excelforum.com/member.php...fo&userid=1628
View this thread: http://www.excelforum.com/showthread.php?threadid=39868

T-Rex,

Thanks a million. It worked fine. I did have to figure out that in
order for it to work I had to save the originals as txt files, then
open them, and save them as xls files, then put the macro in place and
run it. I must have had some kind of format on the columns... that's
all I can think of... that screwed it up and caused the error.

Thanks again, worked like a charm! That will enable me to finish my
work up very very quickly!!!

Bill Beecham


--
billbeecham
------------------------------------------------------------------------
billbeecham's Profile: http://www.excelforum.com/member.php...o&userid=16286
View this thread: http://www.excelforum.com/showthread...hreadid=398686