VBA or Formula that gives me all unique values in a column AND counts how many times that value appears.

[booleanboolean]

Hello all,

I want to know if there is a VBA or Formula that will let me know how many unique values are in a certain column AND how many times that value appears in each column:

[TABLE="width: 497"]
<colgroup><col span="7"></colgroup><tbody>[TR]
[TD]MON[/TD]
[TD]TUE[/TD]
[TD]WED[/TD]
[TD]THU[/TD]
[TD]FRI[/TD]
[TD]SAT[/TD]
[TD]SUN[/TD]
[/TR]
[TR]
[TD]5a-1p[/TD]
[TD]5a-1p[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]5a-1p[/TD]
[TD]5a-1p[/TD]
[TD]5a-1p[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[/TR]
[TR]
[TD]8p-4a[/TD]
[TD]8p-4a[/TD]
[TD]8p-4a[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]8p-4a[/TD]
[TD]8p-4a[/TD]
[/TR]
[TR]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]6a-2p[/TD]
[/TR]
[TR]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]6a-2p[/TD]
[/TR]
[TR]
[TD]12p-8p[/TD]
[TD]12p-8p[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]12p-8p[/TD]
[TD]12p-8p[/TD]
[TD]12p-8p[/TD]
[/TR]
[TR]
[TD]6a-2p[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[/TR]
[TR]
[TD]2p-10p[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]2p-10p[/TD]
[TD]2p-10p[/TD]
[TD]2p-10p[/TD]
[TD]2p-10p[/TD]
[/TR]
[TR]
[TD]2p-10p[/TD]
[TD]2p-10p[/TD]
[TD]2p-10p[/TD]
[TD]2p-10p[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]2p-10p[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]2p-10p[/TD]
[TD]2p-10p[/TD]
[TD]2p-10p[/TD]
[TD]2p-10p[/TD]
[TD]2p-10p[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[TD]2p-10p[/TD]
[TD]1215p-815p[/TD]
[TD]12p-8p[/TD]
[TD]2p-10p[/TD]
[TD]2p-10p[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[TD]10p-6a[/TD]
[TD]10p-6a[/TD]
[TD]10p-6a[/TD]
[TD]10p-6a[/TD]
[TD]10p-6a[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]10p-6a[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]10p-6a[/TD]
[TD]10p-6a[/TD]
[TD]10p-6a[/TD]
[TD]10p-6a[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[TD]2p-10p[/TD]
[TD]2p-10p[/TD]
[TD]2p-10p[/TD]
[TD]8p-4a[/TD]
[TD]8p-4a[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[TD]9p-5a[/TD]
[TD]9p-5a[/TD]
[TD]9p-5a[/TD]
[TD]9p-5a[/TD]
[TD]9p-5a[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]8p-4a[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]8p-4a[/TD]
[TD]8p-4a[/TD]
[TD]8p-4a[/TD]
[TD]8p-4a[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[TD]8p-4a[/TD]
[TD]8p-4a[/TD]
[TD]8p-4a[/TD]
[TD]8p-4a[/TD]
[TD]8p-4a[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]8p-4a[/TD]
[TD]8p-4a[/TD]
[TD]8p-4a[/TD]
[TD]8p-4a[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]8p-4a[/TD]
[/TR]
[TR]
[TD]9p-5a[/TD]
[TD]9p-5a[/TD]
[TD]9p-5a[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]9p-5a[/TD]
[TD]9p-5a[/TD]
[/TR]
[TR]
[TD]10p-6a[/TD]
[TD]10p-6a[/TD]
[TD]10p-6a[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]10p-6a[/TD]
[TD]10p-6a[/TD]
[/TR]
[TR]
[TD]9p-5a[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]9p-5a[/TD]
[TD]9p-5a[/TD]
[TD]9p-5a[/TD]
[TD]9p-5a[/TD]
[/TR]
</tbody>[/TABLE]


My project is to count how many times different shifts (5A-1P, 10P-6A, etc) occur EACH DAY. For example, 5A - 1P occurs occurs ONCE on Monday and ONCE on Tuesday, but 0 on Wednesday.

I have been doing this manually and it's such a pain on my eyes. I want to know if there is an easier way to do this via Macro or Formula.

THANKS!

 

[eduzs]

Count unique values:

=SUM(IF(FREQUENCY(MATCH(A28:A32,A28:A32,0),MATCH(A28:A32,A28:A32,0))>0,1))

To count how many times that value appears in each column, first you need to tell if the values is restricted list or can have diferent and imprevisible values.