Showing one of every value in array

[rs56369]

I have a column C2:C2000 listing multiple kinds of lock systems useing abreviations. Their are only 8 or 9 different abbreviations that are repeated, but I need to show all the different kinds in cell C1. Is there a function I can use to scan the entire column and pull out one of every kind of abreviation and display it in a cell not in the actual array?

[Richard Buttrey]

Hi,

You could use Data Filter Advanced Filter, and filter with the 'unique' option ticked, to another range. This will list the unique items in a column, not a single cell, but if you need them in a single cell you could concatenate them together with a function like

=D5&D6&D7.........&Dn

HTH

[Richard Schollar]

Howdy

Richard's Advanced Filter suggestion is the one I would go for, however, there are formula options too. In an adjacent column (eg column D) in D2 write the formula:

=C2

Then in D3 (aqnd copying downwards) use the formula:

=IF(D2="","",IF(ISNA(INDEX($C$2:$C$2000,MATCH(0,COUNTIF(D$2:D2,$C$2:$C$2000),0))),"",INDEX($C$2:$C$2000,MATCH(0,COUNTIF(D$2:D2,$C$2:$C$2000),0))))

which is an array formula and so must be confirmed with Ctrl+Shift+Enter.

This is an 'expensive' formula in terms of processing cycles.

Again, it returns unique values to individual cells (rather than all of them in a single cell).

Best regards

Richard