row to column lookup ..need help

[ritesh.sharma25]

HI Guys,

I need help to figure out something , i have read few forums but did not quite get what I am looking for.

Here is my issue,

excellook.png

I have a column 1 , which has some list of reports , and column 2 , which has list of reports, in excel 2010.



What i want to do is , I want do search each value in column 1 against values of column 2 , and if there is a match , I want to change the colour of that cell.

Could you please help me.

Regards,
RVS

[leelnich]

Hi ritesh.sharma25, welcome to Excel Forum. You can do this with conditional formatting.
Assuming you want to highlight column B, select B2:B?? and apply this Conditional Formatting formula:
=COUNTIF(A:A,B2)>0

Then click on the Format button and choose your Fill color.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[ritesh.sharma25]

thanks lee for the reply.

i am not able to do that,

I select the column, and then select condtitonal formating, then new rule, then "use formulas to determine which cells to format" ->

Enter the formula, given by you.

Select the color and click ok..BUt nothing happens

[JohnTopley]

Attach a sample workbook not image).


Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[leelnich]

ritesh- The formula as written only works in column B starting at cell B2. If you selected the entire column, or any cell other than B2 as the top cell, it won't work correctly. Did you look at my attached workbook?

NOTE: if you want to select the entire column, you could use this (including the "=" sign):
=COUNTIF(A:A,B1)>0

[ritesh.sharma25]

Thank you Lee and John, for the reply.

I have attached an sample excel, which has 3 sheets as

RAW DATA - my data on which I want to do analysis
Expected Outcome 1 - This is what I am expecting OR
Expected Outcome 2 - This is my second expectation if 1st is not possible.

So, what I basically want is .
1. Take First cell Value from Column 1 (reports name in source) .
2. Search this vale in entire second column.
3. If there is a match Color that cell in Column, or Create a 3rd column with YES/NO.

Similarly ,

1. Take Second cell Value from Column 1 (reports name in source) .
2. Search this value in entire second column.
3. If there is a match Color that cell in Column 1, or Create a 3rd column with YES/NO.

[leelnich]

Ok, Here's your workbook w exactly the same CF formulas as I used above applied, but for some reason they didn't work initially. I had to enter the formula as a regular formula in a cell on the worksheet. Then I copied it from the formula bar and pasted it in Conditional Formatting. THEN it worked. Weird!
Select Entire column A and paste this in Conditional Formatting Dialog:
=COUNTIF(B:B,A1)>0

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[ritesh.sharma25]

Thanks Lee, you were really helpfull.