Counting # of Unique Items In List & Listing Unique Items In A List

**unpluggedmusic** · 12-05-2012, 09:44 AM

Dear all,

I am stuck with a project that requires us to count the number of unique items in a list. The list contains blank cells which are not really empty. They contain "" which is shown as a blank.

I need to count them, and furthermore, I need an array formula to list them out into another table and each unique item will only appear once in this table.

I have attached a sample. Please take a look.

This is difficult for me. I need the experts over here to assist. Thank you all for the time.

regards,
Lim

**BB1972** · 12-05-2012, 10:04 AM

Have you tried using the search function on the forum? There are numerous similar queries, which should help to get you started, if you read through them.

**benishiryo** · 12-05-2012, 10:12 AM

hi unpluggedmusic, you can try 2 of these array formulas.

in C7:
=SUM(IF(FREQUENCY(IF($A$2:$A$24<>"",MATCH($A$2:$A$24,$A$2:$A$24,0)),ROW($A$2:$A$24)-1)>0,1))

in E14:
=INDEX($A$2:$A$24,MATCH(0,IF($A$2:$A$24<>"",COUNTIF($E$13:E13,$A$2:$A$24)),0))

for both of them, you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER