Excel duplicate values in a single row but not where the same value is on the next row

Question

question

SaraBr-4493 asked Feb 26, '21 Lz-3068 commented Feb 26, '21

Excel duplicate values in a single row but not where the same value is on the next row

Hi I know how to find duplicates and how to conditionally format but I’m stuck on this one.

We have checklist where you can add X to columns B, C, D. I want to highlight if someone adds an X to more than 1 column within that row.

However, I need this to apply to all rows but I don’t want the Xs in the other rows to flag as duplicates unless of course there are multiple Xs in those rows.

I can do it by adding individual conditional formatting to each but some have 60+ rows so not practical

Any easier option?

Currently I have a count function at the bottom to make sure the number of Xs match the number of rows.

Apologies for example I am on my phone

B C D
1 X X Flag duplicate row 1
2 X Not a duplicate
3 X X Flag duplicate for row 3

Thanks all.

office-excel-itpro

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-02-26T07:16:23.003Z

Lz-3068 answered Feb 26, '21 Lz-3068 commented Feb 26, '21

Hi @SaraBr-4493

Not top clear to me. If the following is what you expect:

1/ Select the range the Cond. Format rule should Apply To (B1:D5 above)
2/ Cond. Format rule with formula: =COUNTIF($B1:$D1,"x")>1

demo.png (3.2 KiB)

· 3

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SaraBr-4493 · Feb 26, 2021 at 12:04 PM

I tried that but to $G1 as I do have additional columns.

It then highlighted 2 rows below it. I tried another row and again it highlighted 2 rows down

=COUNTIF($B1:$G1,”X”)>1 is how I have it written

0 ·

SaraBr-4493 SaraBr-4493 · Feb 26, 2021 at 12:06 PM

Ah resolved it. As I was only applying it to rows 3 on

It works. Thanks much!

0 ·

Lz-3068 SaraBr-4493 · Feb 26, 2021 at 12:09 PM

Glad I could help @SaraBr-4493. Please accept as answer to help others with a similar challenge - Thanks

0 ·

Answer 2 · 2021-02-26T08:56:27.597Z

erinding-msft answered Feb 26, '21

@SaraBr-4493

Do you would like to realize the requirement in Excel on a PC or on a mobile device?
If you use Excel on a PC, please check if Lz-3068's reply is helpful to you.
As we are responsible for general issues about desktop Excel, if you use Excel on a mobile device, it is suggested to post your issue on Microsoft Answers forum as it is not supported on Q&A forum currently.
Any updates, please let us know.

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question