Highlight formula assistance

rn119

New Member
Joined
Feb 27, 2013
Messages
49
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.
 
Last edited:

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,042
Office Version
365
Platform
Windows
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

New Member
Joined
Feb 27, 2013
Messages
49
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.

IDTypeCodeActive
WA01REF0075T
WA01REF0075TIP
WA01REF0076T
WA01REF0076TIP

<colgroup><col width="68" span="4" style="width:51pt"> </colgroup><tbody>
</tbody>
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,042
Office Version
365
Platform
Windows
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

New Member
Joined
Feb 27, 2013
Messages
49
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,042
Office Version
365
Platform
Windows
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

New Member
Joined
Feb 27, 2013
Messages
49
"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.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,042
Office Version
365
Platform
Windows

rn119

New Member
Joined
Feb 27, 2013
Messages
49
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,042
Office Version
365
Platform
Windows
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.
Code:
=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<>"")
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,746
Messages
5,446,253
Members
405,392
Latest member
Steveoaktree1977

This Week's Hot Topics

Top