Comparing values in multiple columns 1

[gmoorthy]

I have an excel spreadsheet ( Master) which has data in multiple columns begining with row #3 and then i have another worksheet which has data in one column. I want to comapre the data in the one column against the Master and mark which data in the multiple columns are not there either by yes or no.

 

[Andrzejek]

Are you saying you have data like this (if you would have it on one sheet):

[pre]
A B C D
1 AA BB CC AABBCC
2 XX YY ZZ XXYYZZ
3 ab cd ef klpnot
[/pre]

And you want to compare columns A & B & C to column D?
So row 1 and 2 would be Yes, and row 3 No?

Have fun.

---- Andy

 

[SkipVought]

hi,

So where do you put the yes/no?

Use the MATCH() function with the EXACT MATCH (0) value.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gmoorthy]

Here is how my data is

In WORKSHEET 1

Column A Column B COlumn C

123 ABC XYZ
FEG 789 MNR



I have another worksheet2 which has all in one column

123
PEG
MNR


I need to compare worksheet 2 with worsheet 2 and then either highight values in Worksheet 1 which are missing in worksheet 2 or some mechanism to put a Yes or no againt each values ( I am ok even if i can append the values with say * for yes and ? for No

 

[SkipVought]

Conditional Formatting.

1) Name the Range on sheet 2 MyValues
2) select ALL data in sheet 1
3) Hit the Conditional Formatting Icon and select New Rule > Use a formula to determine which cells to format
4) In the Edit Rule description enter this formula
[tt]
=MATCH(A1,MyValues,0)>0
[/tt]
This is assuming that the cell in the upper left of your selection (step 2) is A1.
5) select the FORMAT that you wish.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}