MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Suppressing Repeating Values


Posted by JB on January 26, 2001 12:52 PM

Is there a way to suppress repeating values, but still have them valid for sorting purposes?

for exeample:

XPORT 2
XPORT 3
XPORT 4
IMPORT 1
IMPORT 2
IMPORT 5

Would display as:

IMPORT 1
2
5
EXPORT 2
3
4

when the repeating value is suppressed.

Thanks, JB......


Posted by Mark W. on January 26, 2001 2:48 PM

JB, use conditional formatting. Let's say that
A2:A7 contains {"XPORT";"XPORT";"XPORT";"IMPORT";
"IMPORT";"IMPORT"}. Using the Format Conditional
Formatting... menu command assign the following
condition:

Cell Value Is equal to =$A1

to cell A2. Press the Format... button and set the
font color to match the cell pattern color (which
is probably white). Now copy cell A2 and do a
Paste Special... Formats onto cells A3:A7. Voila!
Your repeating values are suppressed, but the value
is still present in the cells for sorting purposes.

Pretty cool, huh?