Ask Your Question

Revision history [back]

Example:

  • A1: a
  • A2: 3
  • A3: 1
  • A4: 2
  • A5: 3
  • A6: 1
  • A7: 1
  1. Go to A1.
  2. Menu Data > Pivot Table > Create...
  3. OK.
  4. Drag "a" from Available Fields to Row Fields.
  5. Drag "a" from Available Fields to Data Fields. It becomes "Sum - a".
  6. Double-click "Sum - a".
  7. Click on Count. OK.
  8. OK. A Pivot table is created in a new sheet. It looks like this:
a Count - a
1 3
2 1
3 2
Total Result  6

Now you can see how often the values occur. To update the pivot table right click on it and choose Refresh.

Example:

  • A1: a
  • A2: 3
  • A3: 1
  • A4: 2
  • A5: 3
  • A6: 1
  • A7: 1
  1. Go to A1.
  2. Menu Data > Pivot Table > Create...
  3. OK.
  4. Drag "a" from Available Fields to Row Fields.
  5. Drag "a" from Available Fields to Data Fields. It becomes "Sum - a".
  6. Double-click "Sum - a".
  7. Click on Count. OK.
  8. OK. A Pivot table is created in a new sheet. It looks like this:
a Count - a
1 3
2 1
3 2
Total Result  6

Now you can see how often the values occur. To update the pivot table right click on it and choose Refresh.


Different solution:

This Python3 program counts values:

#! python3
import csv
d=dict()
with open('input.txt', newline='') as csvfile:
     reader = csv.reader(csvfile, delimiter='\t')
     for row in reader:
         for x in row:
            if x=='':
                continue
            if x not in d:
                d[x]=1
            else:
                d[x]=d[x]+1

a=list(d.keys())
a.sort()

with open('output.txt', 'w', newline='') as csvfile2:
    writer = csv.writer(csvfile2, delimiter='\t')
    for k in a:
        s=[k,d[k]]
        writer.writerow(s)