I am trying to locate rows which contain duplicates based on two columns. It is a little hard to explain but let me try. Below is a picture on what I am trying to achieve.

Example Image

If an ID value (Column B) has a duplicate and the values in the IDF's (Column C) are different it needs to highlight the column or in Column D provide some text which I can use for conditional formatting.


  1. Rows 5-7 have the same ID but there is a different IDF value in one of the fields, therefore it needs to be highlighted
  2. Rows 9-10 have the same ID and all of the IDF values are the same, no further action is needed
  3. Rows 11-15 have the same ID but there is different IDF values so all of the rows relating to that ID need to be highlighted
  4. Row 3 has only one ID value row therefore no further action is required

This is a little hard to explain, but please. Let me know if it makes sense or if you need any more information.

Also Note: This data will be sorted by ID (unlike the example shown above)


You can do this quickly with a PivotTable. Click anywhere in your table and then click Insert > PivotTable from the menu bar.

Add ID and IDF to the Rows area of the PivotTable. It will look something like this:

enter image description here

Now, click anywhere on the PivotTable and select Design > Report Layout > Show In Tabular Form. Right-click any of the cells that say something like 1 Total and untick Subtotal ID.

It will look like this, with gaps where you have multiple IDFs per ID.

enter image description here

Right-click any value in the ID column, and select Field Settings. On the Layout & Print tab, tick Repeat item labels.

In column D of your original table, in the first data row - add this formula, making sure to change I2:I20 to wherever you have put the first column of your PivotTable.

For each ID, it counts how many distinct IDFs there are, and will output flag if there are more than 1. You can use this to drive conditional formatting.


enter image description here

| improve this answer | |
  • 1
    Perfect answer with a great explanation! Thanks you so much – AaronHatton Nov 15 '16 at 10:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.