Highlight formula assistance

[rn119]

I need a formula to highlight the cell right above if a cell in a specific column (let's say Column C) has a unique text value (e.g., IP). Any assistance would be appreciated.

 

[Joe4]

Unique compared to what? Other values in column C, or compared to something else?

It may be helpful if you could show us a small sampling of your data, and your expected result.

 

[rn119]

This column only has one specific text value (i.e., IP). So I need the blank cells directly above that to be higlighted. See example below.

[TABLE="width: 272"]
<colgroup><col width="68" span="4" style="width:51pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 68"]ID[/TD]
[TD="class: xl65, width: 68"]Type[/TD]
[TD="class: xl65, width: 68"]Code[/TD]
[TD="class: xl65, width: 68"]Active[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 68"]WA01[/TD]
[TD="class: xl66, width: 68"]REF[/TD]
[TD="class: xl66, width: 68"]0075T[/TD]
[TD="class: xl67, width: 68"] [/TD]
[/TR]
[TR]
[TD="class: xl66, width: 68"]WA01[/TD]
[TD="class: xl66, width: 68"]REF[/TD]
[TD="class: xl66, width: 68"]0075T[/TD]
[TD="class: xl66, width: 68"]IP[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 68"]WA01[/TD]
[TD="class: xl66, width: 68"]REF[/TD]
[TD="class: xl66, width: 68"]0076T[/TD]
[TD="class: xl67, width: 68"] [/TD]
[/TR]
[TR]
[TD="class: xl66, width: 68"]WA01[/TD]
[TD="class: xl66, width: 68"]REF[/TD]
[TD="class: xl66, width: 68"]0076T[/TD]
[TD="class: xl66, width: 68"]IP[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

What columns are you considering when trying to determine uniqueness?
Can you post some examples of what ones that you don't consider unique look like?

 

[rn119]

It's only one column ....so in the example above, that would be Column D. If that column had the "IP" text value (again in the example above...that would be D2 and D4) the cell(s) directly above it is highlighted (so this would be D1 and D3). That's all. No other columns are being referenced for the highlight action.

 

[Joe4]

OK, so when you say "unique", you mean that there is only one "different" value showing in column D, though it that value may show up many times?
So, then would an example of not unique be something like where "IP" appears on one line, and "DQ" appears on another line (even if they both only appear once)?

 

[rn119]

"OK, so when you say "unique", you mean that there is only one "different" value showing in column D, though it that value may show up many times?"

Correct.

"So, then would an example of not unique be something like where "IP" appears on one line, and "DQ" appears on another line (even if they both only appear once)?"

There would be no other text value in this column but yes to this as well in the event there COULD be other text values in the future.

 

[Joe4]

Working with your example, select cells D2:D5 and apply this Conditional Formatting formula:

=AND(COUNTIF(D2:D5,"*")-COUNTIF(D2:D5,LOOKUP(2,1/(D2:D5<>""),D2:D5))=0,D2="",D3<>"")

and choose your highlighting color.

Here are a few links that show some of the techniques I used:
https://exceljet.net/formula/count-cells-that-contain-text
https://exceljet.net/formula/get-value-of-last-non-empty-cell

 

[rn119]

Joe sorry I forgot to mention earlier...but is there any way to adjust this to accommodate ALL of column D? Rows are variable based on the data pull so I need this to be dynamic rather than static.

 

[Joe4]

It would be best to just pick some large row number that you will never hit, as there are a few problems using the whole column, i.e.
- you would be doing unnecessary calculations on tons of rows which could slow things down
- the formula looks at one cell below the current row, so on the last row, it would be looking off the sheet, which would cause an error

So, if you choose some row number you will never hit, it should work, selecting D2 down to that row number, i.e.

=AND(COUNTIF(D2:D[COLOR=#ff0000][B]9999[/B][/COLOR][B],[/B][COLOR=#ff0000][/COLOR]"*")-COUNTIF(D2:D[B][COLOR=#ff0000]9999[/COLOR],[/B]LOOKUP(2,1/(D2:D[B][COLOR=#ff0000]9999[/COLOR][/B]<>""),D2:D[B][COLOR=#ff0000]9999[/COLOR][/B]))=0,D2="",D3<>"")