I have a excel file with entries that appear multiple times. Example:
- A
- A
- A
- B
- B
- C
- C
How can I change this to a form that looks like this:
- A 3
- B 2
- C 2
You can do this easily with a pivot table.
First, select your data. Then, from the Insert
ribbon, choose Pivot Table
. In the dialog that pops up, you will need to tell Excel where to place your pivot table. By default, it is on a new sheet.
After clicking OK, you will see something like this:
In the PivotTable Field List, click and drag the field name you want to summarize ('Category' in my example) to the Row Labels box. Then, click and drag the same field name into the Values box. When you've done this you should see this:
As you can see, this gives you the table you requested.
So long as you know the name of each item in the list, you can automate it.
A B C
1 A A =COUNTIF(A:A, B1)
2 A B =COUNTIF(A:A, B2)
3 A C =COUNTIF(A:A, B3)
4 B D =COUNTIF(A:A, B4)
5 B
6 C
7 C
Will show:
A B C
1 A A 3
2 A B 2
3 A C 2
4 B D 0
5 B
6 C
7 C