Compare and show only the same value cells as result?

**KMarks** · 01-26-2010, 03:34 PM

Hi,

I am new to Excel 2007 and this forum!

I have a large spreadsheet (65,000+ rows) that contains data pulled from a database. Each row in the spreadsheet corresponds to a record in the database.

I need to compare 2 "fields" from the database, which means each row is a separate comparison. To complicate things further, each field can be multi-value, so there are multiple cells (12) per field, per row.

The fields are mutually exclusive. What I need to do is check if the value in A1 is found in M1:X1 and indicate it if it is. Then B1 in same M1:X1, etc., for a1 thru L1.

Then I need to do the same thing for each of the 65,000+ rows.

I have no idea what to even search on to try to find ideas. Any guidance will be greatly appreciated!

**NBVC** · 01-26-2010, 03:48 PM

You can try:

=SUMPRODUCT(--ISNUMBER(MATCH(M1:X1,A1:N1,0)))

this will count how many times values in M1:X1are found in A1:N1

**JBeaucaire** · 01-26-2010, 03:49 PM

If A1 exists in M1:X1, indicate so where? And what is the indicator value? "Yes"?
If B1 exists in M1:X1, indicate so where? And what is the indicator value? "Yes"?

Etc.

**KMarks** · 01-26-2010, 04:18 PM

Originally Posted by JBeaucaire

If A1 exists in M1:X1, indicate so where? And what is the indicator value? "Yes"?
If B1 exists in M1:X1, indicate so where? And what is the indicator value? "Yes"?

Etc.

Indicate in the easiest way possible. Maybe highlight/shade the cells that are duplicates?

Probably would be best to not add a "results" cell(s), since there is enough data in the row already!

**NBVC** · 01-26-2010, 04:20 PM

To highlight select columns A:N

Go to Format|Conditional Formatting|New Rule

Select Use Formula to determine which cells to format

Enter formula

=Match(A1,$M1:$X1,0)

Click Format and choose colour from Pattern tab.

Click Ok.

Click Ok to apply.