Conditional formatting duplicate values in multiple columns

[PowerZ]

Hi all,

I am trying to conditional format duplicate values in two columns so at the moment I'm doing =COUNTIF($D:$D,$D1)>1 in the conditional formatting formula box.

This basically formats anythign that occurs more than once in that column.

Data:

Column C. Column D:

11112 F111
11113 F112
11113 F141
11114 F141
11114
11115 F999
11116 F111

So basically column C is the reference number to a case and column D is another reference to a group.

A lot of the records only have column D filled for the top record instead of repeating it etc so how can I group them together?

So conditional formatting that would highlight 11114 - F141 but also 11114 with empty D column because it is of the same case.

Hope thats clear haha

Thank you!

[José Augusto]

Try these

Please Login or Register  to view this content.

Apply to C and D

[PowerZ]

Hi,

That seems to highlight the duplicate column C cells. The way I need it if possible is,

If Column D is a DUPLICATE highlight row and also highlight the rows beneath it if the COLUMN C value is the same as the Duplicated one.

Hope that makes sense, if not I can upload a sample spreadsheet if it makes it clearer.

Thanks for the reply

[José Augusto]

Try

Please Login or Register  to view this content.

and apply to C and D

[PowerZ]

Hi thanks again for the reply,

I might not be the best at explaining this.

I have attached a sample spreadsheet which has the duplicates highlighted with different colour and separated the different cases with a bottom border so you are able to see the data more clearly and how they are grouped together.

That previous formula did work but the opposite way.

So as you can see from the spreadsheet there are sometimes multiple case references because they have more than one person in the family on that specific case. However the family reference is only recorded on the first row of the case reference.

Therefore instead of just the first row being highlighted that has the family reference value, the ones below it need to be highlighted because it is of the same case reference.

Thank you so much for helping, I think you could have cracked it first go if I wasn't so bad at explaining!

Cheers,

[José Augusto]

Hi
try

Please Login or Register  to view this content.

see the file excelforum.xlsx

[PowerZ]

Hi,

Thats is nearly there however it is still highlighting the rows if the Case ref column is the same even if the family ref is not a duplicate.

It should only highlight the row if family ref is a duplicate and then highlight the rows below it if the case ref is the same

I've attached the spreadsheet outlining which section it is.

Thanks again for your help!

[José Augusto]

One more try

Please Login or Register  to view this content.

[PowerZ]

You're so close! haha

It is now highlighting the blank rows correctly but not highlighting the row with the family reference. so it only now needs to highlight that row

Thank you for helping

EDIT: Wait I may have been doing it wrong, i think its working!

[PowerZ]

Hmmm still not working.

Attached the spreadsheet with the formula.

Left side is how it should highlight and the conditional formatting is not matching up.

Not sure what to do.

Thanks for your help.

[JeteMc]

It would seem that you would like to highlight all of the records that belong to the same family. That would be possible using your original conditional formatting rule, with the exception of changing $D1 to $D2, IF all of the values for the family reference column were filled in. You can populate column D with a few steps.
1. Select C2, the Case Reference column, and press Ctrl + down arrow.
2. Press the right arrow.
3. Press and hold Ctrl + Shift, then press the up arrow key repeatedly until you reach the top of column D.
4. Select Find & Select from the Home tab.
5. Select Go To Special.
6. Select Blanks.
7. Press = then up arrow then enter.
All of your Family Reference values should now be filled in. The one possible issue that I found was with rows 23 and 24.
Here is a copy of your file with the values filled in using the steps described above and applying your original conditional formatting with the one change:
Copy of excelforum (2).xlsx
Let me know if you have any questions.

[José Augusto]

See the file excelforum (3).xlsx with my suggestion
and answer my questions

[PowerZ]

JeteMC: Yeah you understand what I am trying to achieve but unfortunately the spreadsheet cant like you said the rest of the family reference cell filled for complicated reasons.

José I have attached the file with the correct true/false with explanations on why.

Unfortunately the reasons why the family reference are not populated all the way down is for other reasons and unfortunately that cannot change. Only the first cell of the case reference has the family reference and the rest below only have the same case reference.

Hopefully that spreadsheet clears it up for you,

Thanks for all your help guys.

[PowerZ]

Sorry please see this one attached

I have separated the different cases with dark bottom border so you can see the separate cases easier.

[José Augusto]

Solution:

Please Login or Register  to view this content.

See the file Book1 (1).xlsx

[PowerZ]

Solution:

Please Login or Register  to view this content.

See the file Attachment 439168

That work's perfectly!!!!! Thank you so much!!