1

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
0

2 Answers 2

5

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.

enter image description here

After clicking OK, you will see something like this:

enter image description here

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:

enter image description here

As you can see, this gives you the table you requested.

More information about pivot tables in Excel

0
4

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
1
  • 2
    +1 Excel's Advanced Filter can help the OP quickly extract unique values from a range like so: i.imgur.com/Q8plB.png
    – Ellesa
    Aug 29, 2011 at 6:14

Not the answer you're looking for? Browse other questions tagged .