Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE
SOLVED

I am looking for a function that can highlight a row based on entries in two separate columns

Copper Contributor

Good day I am struggling to figure out how to check in one column if the client ID has been entered multiple times in the ID column if it has been entered multiple times has there been duplicate submission numbers in the submission number column and if there have been duplicate submission I want the row to be highlighted. I hope someone can help me and thanks.

 

                  Column 1        Column 2
ROW           Sub NO         Customer ID
   1                  1                      1
   2                  1                      2
   3                  1                      3
   4                  1                      1
   5                  2                      2
   6                  1                      4

 

So in the example, I would want row 4 to highlight

2 Replies
best response confirmed by Steph1685 (Copper Contributor)
Solution

@Steph1685 

Let's say the columns are A and B, with data starting in row 2.

Select rows 3 and down. The active cell in the selection should be in row 3.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=COUNTIFS($A$2:$A2, $A3, $B$2:$B2, $B3)

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.

1 best response

Accepted Solutions
best response confirmed by Steph1685 (Copper Contributor)
Solution

@Steph1685 

Let's say the columns are A and B, with data starting in row 2.

Select rows 3 and down. The active cell in the selection should be in row 3.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=COUNTIFS($A$2:$A2, $A3, $B$2:$B2, $B3)

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.

View solution in original post