help with cell compare in two worksheets

[billbeecham]

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

[Eric White]

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
>
>

[billbeecham]

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-容x]

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

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

[billbeecham]

Hi, that worked great on the sample sheets I attached, but when I run 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 have over 3000 rows.


Thanks for helping,

Bill Beecham

[T-容x]

Hi!

Notice the italized, colored, bold words in the following lines in the 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 from "New Master" and "Old Master". If this is the case, you should modify 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 prob even if your list has more than 3000 items. (Long in excel can handle up to 2,147,483,647.)

Hi, that worked great on the sample sheets I attached, but when I run 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 have over 3000 rows.


Thanks for helping,

Bill Beecham

[billbeecham]

T-容x,

Will try again. Ya, I knew to change the bolded info. I did that. It's 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 figured I changed something by accident. I re-copied your code and tried again and still got that error. I even got the idea to try it again on the samples and see if maybe I was overlooking something, but it still errored.

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

I will try again tonight after work.

Thanks muchly!

Bill

[billbeecham]

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