Count the repeated values in a column and then numbering the results

[zinah]

Hi,

I have below table and what I need to do is to count how many repeated value in the column A, then number the results (the expected results column is what I need), anyone can help?

Book2
	A	B	C
1	EE ID	Countif	Expected Results
2	AA	13	1
3	AA	13	2
4	AA	13	3
5	AA	13	4
6	AA	13	5
7	AA	13	6
8	AA	13	7
9	AA	13	8
10	AA	13	9
11	AA	13	10
12	AA	13	11
13	AA	13	12
14	AA	13	13
15	BB	5	1
16	BB	5	2
17	BB	5	3
18	BB	5	4
19	BB	5	5
20	CC	2	1
21	CC	2	2
22	DD	8	1
23	DD	8	2
24	DD	8	3
25	DD	8	4
26	DD	8	5
27	DD	8	6
28	DD	8	7
29	DD	8	8
30	EE	2	1
31	EE	2	2
32	F	1	1
Sheet1

Cell Formulas
Range	Formula
B2:B32	B2	=COUNTIF($A$2:$A$32,A2)

Thank you!

 

[jtakw]

Hi,

Use COUNTIF this way:

Book3.xlsx
	A	B	C
1	EE ID	Countif	Expected Results
2	AA	13	1
3	AA	13	2
4	AA	13	3
5	AA	13	4
6	AA	13	5
7	AA	13	6
8	AA	13	7
9	AA	13	8
10	AA	13	9
11	AA	13	10
12	AA	13	11
13	AA	13	12
14	AA	13	13
15	BB	5	1
16	BB	5	2
17	BB	5	3
18	BB	5	4
19	BB	5	5
20	CC	2	1
21	CC	2	2
22	DD	8	1
23	DD	8	2
24	DD	8	3
25	DD	8	4
26	DD	8	5
27	DD	8	6
28	DD	8	7
29	DD	8	8
30	EE	2	1
31	EE	2	2
32	F	1	1
Sheet1084

Cell Formulas
Range	Formula
C2:C32	C2	=COUNTIF(A$2:A2,A2)

 

[zinah]

Hi,

Use COUNTIF this way:

Book3.xlsx
	A	B	C
1	EE ID	Countif	Expected Results
2	AA	13	1
3	AA	13	2
4	AA	13	3
5	AA	13	4
6	AA	13	5
7	AA	13	6
8	AA	13	7
9	AA	13	8
10	AA	13	9
11	AA	13	10
12	AA	13	11
13	AA	13	12
14	AA	13	13
15	BB	5	1
16	BB	5	2
17	BB	5	3
18	BB	5	4
19	BB	5	5
20	CC	2	1
21	CC	2	2
22	DD	8	1
23	DD	8	2
24	DD	8	3
25	DD	8	4
26	DD	8	5
27	DD	8	6
28	DD	8	7
29	DD	8	8
30	EE	2	1
31	EE	2	2
32	F	1	1
Sheet1084

Cell Formulas
Range	Formula
C2:C32	C2	=COUNTIF(A$2:A2,A2)

Perfect, thank you for your quick response!

 

[alansidman]

I offer you two power query solutions

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"EE ID"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"EE ID"}, {{"Data", each _, type table [EE ID=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Index",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Index"}, {"Custom.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Data"})
in
    #"Removed Columns"

 

[jtakw]

You're welcome, thanks for the feedback.