list and count unique Excel 2013

[apgmin]

In column A, I have a list of values from A2-A503, I fill these cells

In column B from Column B2 onwards I want to list the unique values from column A ( A2-A503 )

In column C from Column C2 onwards I want the nos. of repetitions
of the unique values listed from B2 onwards

PLEASE SEE THE FOLLOWING TABLE IN WHICH I MADE A REPRESENTATIVE TABLE

VALUE TO BE FILLED BY USER	UNIQUE VALUES	No. OF OCCURRENCE
500	500	3
350	350	2
500	400	1
400	200	1
500
350
200

 

[jasonb75]

Does it need to be formulas? You can get what you want much easier with a pivot table.

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C
1
2	104	104	3
3	100	100	3
4	107	107	3
5	102	102	1
6	110	110	4
7	104	106	3
8	106	105	1
9	105	103	1
10	100	109	1
11	100
12	110
13	110
14	110
15	106
16	106
17	103
18	109
19	104
20	107
21	107
Main

Cell Formulas
Range	Formula
B2:B17	B2	=IFERROR(INDEX($A$2:$A$503,MATCH(0,COUNTIFS(B$1:B1,$A$2:$A$503)+($A$2:$A$503=""),0)),"")
C2:C17	C2	=IF(B2="","",COUNTIFS($A$2:$A$503,B2))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[braindiesel]

If you do not have the UNIQUE FUNCTION available to you in 2013, then add a helper column.
IN cell B2
=COUNTIF($A$2:A2,A2)
In cell B3, copied down
=IF(COUNTIF($A$2:A3,A3)=1,COUNTIF($A$2:A3,A3)+MAX($B$2:B2),0)
In cell C2 copied down
=IFERROR(INDEX($A$2:$A$15,MATCH(ROWS($C$2:C2),$B$2:$B$15,0),1),"")
In cell D2 Copied down
=IF(C2<>"",COUNTIF($A$2:$A$15,C2),"")

 

[apgmin]

Does it need to be formulas? You can get what you want much easier with a pivot table.

I am using Office 2013, Also need it to be a formula as the table is filled by staff at other location and emailed to me.
TRIED YOUR FORMULA AND WORKS PERFECTLY.

I wonder how you can make such formulas looks nothing less than Magic to me

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Eric W]

If your values in column A are in fact numeric, and you want them in sorted order, you can use this shorter non-array formula:

Book5
	A	B	C
1	List	Unique List	Count
2	104	100	3
3	100	102	1
4	107	103	1
5	102	104	3
6	110	105	1
7	104	106	3
8	106	107	3
9	105	109	1
10	100	110	4
11	100
12	110
13	110
14	110
15	106
16	106
17	103
18	109
19	104
20	107
21	107
22
Sheet14

Cell Formulas
Range	Formula
C2:C17	C2	=IF(B2="","",COUNTIFS($A$2:$A$503,B2))
B2:B21	B2	=IFERROR(SMALL($A$2:$A$503,SUM(C$1:C1)+1),"")

 

[braindiesel]

If your values in column A are in fact numeric, and you want them in sorted order, you can use this shorter non-array formula:

Book5
	A	B	C
1	List	Unique List	Count
2	104	100	3
3	100	102	1
4	107	103	1
5	102	104	3
6	110	105	1
7	104	106	3
8	106	107	3
9	105	109	1
10	100	110	4
11	100
12	110
13	110
14	110
15	106
16	106
17	103
18	109
19	104
20	107
21	107
22
Sheet14

Cell Formulas
Range	Formula
C2:C17	C2	=IF(B2="","",COUNTIFS($A$2:$A$503,B2))
B2:B21	B2	=IFERROR(SMALL($A$2:$A$503,SUM(C$1:C1)+1),"")

Just wanted to applaud this method... dancing sooooo close to a circular error without getting singed.