Compare rows in a worksheet to rows in another WS

[sterlingmissile]

I would really appreciate any help on this if you guys are able.

I have 2 worksheets, one with data and one with master information. the data WS has rows (All 5 columns across) that need to be compared against the master WS for matches. I need to compare these rows and I need to write a formula that will compare this to the master WS and tell me if there is a match anywhere on the master. Can this be done? Please help. I really do not want to eye over 10000 rows! Thanks guys.

[MattShoreson]

concatenate the columns into a single cell in the master sheet....
=A1 & A2 & A3 etc... This should return a single value

Do the same for the data sheet...
=Data!A1 & Data!A2 & Data!A3 etc...

Then do a match to return a true/false value.

[sterlingmissile]

Thanks Matt - got it. Once that is done, how do I do the compare so it tells me if there is a match or not? Please help. Thanks.

[MattShoreson]

Presume that in colA you have the ws concatenated value
and in colB you have the data sheet value.

in colC:

=IF(ISNA(MATCH(A1,B:B)),"",MATCH(A1,B:B))

This will return nothing if there is no match and the row number if there is a valid match

[sterlingmissile]

Matt - will try this out. Just to clarify, this will compare each cell in column A to the full column B, right? I do not want ti to compare A1 to B1, A2 to B2 etc. Let me know. Thanks.

Regards,
Joe

[MattShoreson]

yes it will.

[sterlingmissile]

Matt - you are the man. Worked like a charm. Thanks for your assistance, I appreciate it.

[sterlingmissile]

Matt - this did not work. It only matches if they are in the same row. Here is the formula:

=IF((G2=H:H),"EXACT","NO MATCH")

Trying to compare cell contents in G2 to each and every cell within column H. Please advise if you are able. Thanks Matt.

Joe