Doubt on how to highlight rows that have same account number but different names

[kumshan450]

Hi I wanted to know excel formula and function that highlight rows that have same account number but different names. I don't want to highlight rows that have same account number and same customer name. I want to highlight rows that have got same account number but different names.

For eg those highlighted in red in attached file.

Doubt.JPG

[mehmetcik]

use conditional formatting.

use countif to find duplicate account numbers and highlight the entire row.

Attach a sample workbook. 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 use the paperclip icon to open the upload window.

View Pic

[kumshan450]

Hi thanks appreciate your quick reply. but using conditional formatting/countif would not give me the desired result as I wanted to highlight rows that have same account number but different names. Using conditional formatting/countif would also highlight that have same account number and same names. Also you were right that I have not provided enough data to demonstrate my need. please find the attached file

[mehmetcik]

This macro does what you need.

Select the developer tab

Then click on run Macro and select Highlight

Please Login or Register  to view this content.

[newdoverman]

I get that you want to highlight the row if there is more than one count per account regardless of the name associated with it. This is one way of doing this using a helper column with the following formula in column C.

This formula will result in a TRUE or FALSE for each row. All rows with TRUE will be highlighted with red.

Please Login or Register  to view this content.

[kumshan450]

Hi thanks but is there any simple formula?

[kumshan450]

Hi thanks for the quick reply Actually my concern is highlight the row if there is more than one count per account and two different customer names. I got a clue from the formula mentioned that you have but not getting exact answer

I did =COUNTIF($A$2:$A$15,A2)>1 for the name column
I did =COUNTIF($B$2:$B$15,B2)>1 for the account column
I applied auto filter on the data and selected countifcount for the name column as false and then selected countifcount for the name column as True.
I got somewhat the result I want but missed account number which has got both different customer name and same customer name in the data. Refer Account no 124

Once again thanks

[samba_ravi]

select A2:B15 and use the below formula in conditional format
=SUM(INDEX(1/COUNTIFS($B$2:$B$15,$B$2:$B$15,$A$2:$A$15,$A$2:$A$15)*($B$2:$B$15=$B2),0))>1
see attached file