How do you list unique values in Excel and count occurrences of each?

Put this in B2 and copy down...
=IFERROR(INDEX($A$2:$A$10,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$10),0,0),0)),"")

Then in C2, copied down...
=IF(B2="","",COUNTIF($A$2:$A$10,B2))

Here is another non-array solution for the unique listing. Data starts in A1. This formula will ignore blank cells in the list if there are any. Enter in B2 and copy down.

Formula:

Please Login or Register  to view this content.

Enter this in C2 and copy down: (same formula as FDibbins for counting only the cell references differ because of different start cell of data.)

Formula:

Please Login or Register  to view this content.

Here's another option.

Use advanced filter.

Let's assume your data is in the range A2:A10 and cell A1 is the column header.

Select the range including the column header A1:A10.

Goto the Data tab>Sort & Filter group>Advanced
Select: Copy to another location
Copy to: B1
Select: Unique records only
OK

Then, to get the counts, enter this formula in B2 and copy down as needed:

=COUNTIF(A:A,B2)

The filter method described in reply #4 would have to be redone whenever you add new data to the range.

However, you can automate this completely by using an event macro.

See this thread:

http://www.excelforum.com/excel-prog...pty-cells.html

Post 2 and 3 will update as you add new data

Originally Posted by rpc06

Thanks Tony. I have managed to get it work with PivotTable. I am still going to try your method.

If I'm not mistaking, you have to refresh a pivot table to get it to update.

Depending on how much data there is to evaluate and how many unique items there are, formulas to do this might be a bit slow to calculate.

You should probably try the formula suggestions first and see if the performance is acceptable.

Originally Posted by rpc06

Hi Tony,

Yes I have noticed the formulas is take long time to calculate as my spreadsheet has 2000 rows of data.

2000 rows of data should not slow your file down (depending on what else you have going on, of course)


=IFERROR(INDEX('PROBLEM MGT'!$A$2:$A$10,MATCH(0,INDEX(COUNTIF($B$1:B1,'PROBLEM MGT'!$A$2:$A$10),0,0),0)),"")

Then in C2, copied down...
=IF(B2="","",COUNTIF($A$2:$A$10,B2))

My calculation sheet is called PROBLEM MGT where i want to resolve the unique in excel and place a count occurrences of each.

If your base data that the Pivot Table is created from is in a TABLE, as the table grows, all you have to do is to refresh the Pivot Table without changing the Data Source to match your expanded data. When you refresh the Pivot Table, make sure that the active cell in the data table worksheet is a cell in the data table otherwise any grouping could get messed up in the Pivot Table......learned from experience on that one.

What else is going on in the worksheet? 2000 rows is really nothing at all.

Originally Posted by FDibbins

2000 rows of data should not slow your file down

Originally Posted by newdoverman

What else is going on in the worksheet? 2000 rows is really nothing at all.

I notice that neither of you guys are using Excel 2013?

Excel 2013 is slower than all previous versions.

Using Excel 2013 (as is the OP?) on my machine (quad core Intel I3 w/8gb ram)...

Worst case scenario:

A2:A2000 = every cell contains a unique entry

So, that means I needed 1999 of these formulas:

=IFERROR(INDEX($A$2:$A$2000,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$2000),0,0),0)),"")

It took my machine almost 5 minutes to return all 1999 unique entries.

Your mileage may vary!

Thanks for the input, Tony, always appreciated Nope, dont have 2013 yet, and may not get it anyway

Thank you Tony. My machine is considerably different. i7 3770 (3.4 ghz) 16 GB ram, 4 actual +4 virtual cores. My laptop would be closer though AMD Quad core 6 GB ram.

I set up the same situation with 2000 unique values and it took just over 6 minutes on the laptop.