unique values in a range

[tinkerbelle]

HI ALL

I have a range of values in range A2:AF30000
These items are not unique.

Can I ask how in VBA i can analyse the data and export a list of unique values into a range below below the data range and count the number of times each unique value is observed?

Thanks
L

[Colin Legg]

Hi,

If you want a count of each distinct item then a pivot table would be a good choice.

[tinkerbelle]

HI Colin, but the values are in separate columns and those items in the first column might not exist in the 2nd column and so on.

I thought about a pivot but couldnt think how to run it to produce the desired result.

L

[Colin Legg]

Hi,

You're right - I misread the range as A2:A30000.

The biggest issue with this is the sheer quantity of data - you have roughly 1 million items to check.
Are you using Excel 2007 or later?

[tinkerbelle]

Hi Colin, thanks for your reply. I am using Excel 2007.

I ended up using the countif() function.

thanks
Luce