Ask Your Question
0

Count frequency of unique cell values in Calc

asked 2018-05-22 15:49:22 +0100

Carlyle gravatar image

updated 2018-05-22 16:11:55 +0100

Let's say I have a column with these entries:

image description

I want a method that gives me this result:

image description

What I want is to list unique cell entries in the column and sort them by frequency.

Notes:

  • Cells can have either a single word or multiple words as seen in the example (not sure if this is relevant to mention, but just in case).
  • I don't know how many unique cell values there are, so I can't use something like =COUNTIF(A1:A10;"Banana"). My column has over 1 million cells, so there are probably dozens of thousands of unique entries.
  • It's not necessary to list them all, I only need the 100 most frequent entries.
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
2

answered 2018-05-22 16:22:02 +0100

Regina gravatar image

Your data need a first row with a label.

  • Mark all data including the label and name it. That is in Data>Define Range. Make sure, that in part Options, the option Contains column labels is checked.
  • Keep the range marked, or in case you lost the selection, use Data>Select Range to mark it again.
  • Call the Pivot-Table wizard. That is an icon in the toolbar, or Data>Pivot Table.
  • Drag your label from Available Fields: into the part Row Fields: and into the part Data Fields.
  • Double-click the label in the part Data fields and select item Count.
  • Make sure that the Pivot table is generated in a new sheet, because we will filter it later on. Find the setting in part Source and Destination. You do not need totals, so you should disable them in the part Options.
  • Go to the Pivot table and select the entire table. A quick way for that is: Click into the Pivot table and press Strg+*(in the numpad).
  • Then set a filter by Data>More Filters> Standard Filter. In field Field name select Count - your label. In field Condition select Largest and in field Value write 100. OK.
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-05-22 15:49:22 +0100

Seen: 807 times

Last updated: May 22 '18