Search code examples
excelvbapowerqueryunpivot

How to get the counts/frequencies of values in a table?

I have the problem that I need to get the number of occurences / the counts or frequencies of values in a table. However, I cannot give the categories before, so in opposite to countif I cannot specify a specific value, as this needs to be done from the table. I have a whole table, not just a column. The picture shows a reduced example:

example

The result I need would look like the following (I counted manually, hope I did not do a mistake):

result

It is not needed to calculate empty cells.

For the starting point I am ok with not specifiying if there is a difference between lowercase and uppercase (so L and l for example). This can be calculated separately or as the same. Later on it would be good to be able to choose this as an option. (I could use lowercase function and refer to this column and fill another column with the values, however the original data is way too large.). My problem is now that I am not able to do this "simple" task. I found a solution for a single column, using a matrix function with countif:

matrix function

{=countif(A21:A27;A21:A27)}

This does not work for a whole table. My original data is too large to do any task manually and repeat this for each column or rearrange the data manually from table to one single column.

Unfortunately I am limited to Excel and/or Excel VBA. I cannot use any other tool. I am happy with an excel solution, if there is a way to do it in VBA I am also very ok with it, however for this I cannot give a proof that I tried it, because I don't have an idea how to program this and I am not advanced enough in coding VBA.

Solution

  • Through PowerQuery

    Sample data:

    enter image description here

    My Excel is in Dutch so excuse me for making some translation errors. It should still point you in the right direction:

    • 1: Select all data > Data Tab > Transform > From Table/Range > Choose No headers
    • 2: In PQ select all 4 columns > Transform Tab > Unpivot all columns
    • 3: Select value column > Transform Tab > Pivot Table > Ok
    • 4: Select all columns > Transform Tab > Unpivot all columns again
    • 5: Exit PowerQuery and confirm to save your edition.

    Result:

    enter image description here

    • Will exclude empty cells
    • Will distinguish between upper and lower case