How to highlight/match Column A values to Column B values, if Column A has duplicate values

Question

Hello,

I'm trying to filter a large data set of information and I'd like to highlight the specific values that I'm looking for. The conditional formatting (highlight duplicate values) rules don't work for my task since the column I'm searching through has duplicate values with in, which are not included in my search.

Customer Name	Account # (Column A)	Status	Email	Account # Search (Column B) <br><br>I only want to highlight these account #'s from Column A
Mikey Mouse	1111111111	Paid	******@Gmail.com	1111111111
Donald Duck	2222222222	Unpaid	******@Gmail.com	3333333333
Goofy	1111111111	Paid	******@Gmail.com
Roger Rabbit	3333333333	Unpaid	******@Gmail.com
Winnie Pooh	2222222222	Paid	******@gmail.com

Answer 1

I don't quite understand your references to Column A and Column B. They don't align with your sample data, but here is a solution as I understand it using your sample data. Hopefully it is sufficient for you to make the adjustments to match your actual data. If not, then reply with excerpts that match more precisely the actual layout of your data and sheets.

Here is the procedure to highlight with Conditional Formatting the data in Column B of your mock-up that matches with the search accounts that are listed in Column E.

1. Select all of the cells that need to be considered for the Conditional Formatting. In the screenshot that will be B3:B7. Make sure that the B3 reference in the formula matches the first row that you begin your selection in.
2. On the Home ribbon open Conditional Formatting>New Rule>Use a Formula. In the Format Values line enter this formula adjusted for the actual location of your data. The E3:E4 reference is for the account #'s you want to identify and the B3 reference is the first account # in the selection.

=COUNTIF($E$3:$E$4,$B3)>0

3. Click on the Format button at the lower right and choose the formatting that you want in the Format Cells dialogue. Then click OK and OK.

Any Account # in Column B in the sample that occurs in the list in Column E will be highlighted by the Conditional Formatting.

Answer 2

Hi,

Your question is not clear. Show the expected result very clearly.

Answer 3

Thanks, it kind of works.

It highlights the matching duplicates but it leaves some of the duplicate numbers in the search criteria out.

Answer 4

1. Make sure that all of the account numbers (in Column B) are included in the range you select before entering the Conditional Formatting.
2. Make sure that all of the numbers in the search list are included in the range entered where the Column E range is located.
3. If there are still numbers in Column B that don't highlight when the same number is in the search list, check whether they are stored differently. If the number is stored as text in one location and the other is stored as a number Excel will not identify them as a match. You can use an ISNUMBER function to test them to see how they are stored. (By the way, the cell's format doesn't determine this and can't tell you this.) Use the formula in an empty cell to test the matches so if you are testing a number in B3 you would use this to test it.

=ISNUMBER(B3)

A TRUE result means that it is stored as a number and a FALSE result means it is stored a text. Both entries must be the same for Excel to identify a match.

Answer 5

This is great, thank you for the support!