Excel formula for duplicated data

Question

Hey

I have a execl sheet with 10,000 lines of data.

Ive sorted by column a then column b. I now need to highlight rows where the data in eg b2 matches b3 AND c2 matches c3. Ive used one formula but it only highlights one line and I want it to highlight both or more if there are more than two matches

Answer 1

....I have a execl sheet with 10,000 lines of data.

Thanks. :>) This was a very challenging worksheet "function" to reduce down.

Answer 2

Hello Samwise76,

Thank you for reaching out in Microsoft Q&A forum.

I understand you want Excel to highlight both rows when column B matches the next row (B2 = B3) and column C also matches (C2 = C3), but your current rule only highlights one row of the matching pair.

If your data is sorted so duplicates sit next to each other, use a rule that checks the row above or the row below (so both rows in the pair get picked up):

1. Select your full data range (for example, A2:Z10000 or whatever columns you want to highlight).
2. Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. Use this formula: =OR(AND($B2=$B1,$C2=$C1),AND($B2=$B3,$C2=$C3))
4. Choose your fill color, then OK.

With this rule, the “top” row in a matching pair is highlighted because it matches the row below, and the “bottom” row is highlighted because it matches the row above. For groups of 3+ identical adjacent rows, every row in the group will be highlighted as well.

If instead you want to highlight every row where the B+C combination appears more than once anywhere in the list (not just adjacent), use a COUNTIFS-based rule:

• Use this formula: =COUNTIFS($B:$B,$B2,$C:$C,$C2)>1

This approach highlights entire duplicate rows by counting how many times the same combination occurs across the columns you care about.

Hope this helps

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

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

Answer 3

To highlight rows in Excel where the data in column B matches the data in the next row (e.g., B2 matches B3) and the data in column C also matches (e.g., C2 matches C3), you can use Conditional Formatting with a formula. Here’s how you can set it up:

1. Select the range of your data (e.g., A2:C10000).
2. Go to the Home tab, click on Conditional Formatting, and select New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter the following formula:

      =AND($B2=$B3, $C2=$C3)
   

5. Click on the Format button to choose the formatting options you want (like fill color).
6. Click OK to apply the rule.

This formula checks if the value in column B of the current row matches the value in column B of the next row, and if the value in column C of the current row matches the value in column C of the next row. If both conditions are true, it will highlight the row.

To ensure that all matching rows are highlighted, make sure to apply the formatting rule to the entire range of your data.

---

References:

• How to use a logical AND or OR in a SUM+IF statement in Excel