-5

I have a ragged range of cells with recurring contents. I need to know how often each unique content appears. How do I get where I need go?

enter image description here

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
nicomp
  • 4,344
  • 4
  • 27
  • 60
  • `=Countif(C1:H3, "A")` would give you `2`. Do this for each distinct letter and you'll have your table. – JNevill Aug 06 '18 at 20:08
  • I don't know the letters that will be in the dataset. – nicomp Aug 06 '18 at 20:14
  • Sounds like you'll need to write some VBA for that then. Then you can use the Countif() solution. – JNevill Aug 06 '18 at 20:17
  • @JNevill Hence the tag on the question. :) – nicomp Aug 06 '18 at 20:18
  • Basically you have the two problems to solve 1) how to get distinct values from a range 2) how to get counts of those distinct values in that range. For the distinct values: you could loop through each cell in the range `For Each rngCell in Range("C1:H3")` and use a [scripting dictionary to identify unique values](https://stackoverflow.com/questions/36044556/quicker-way-to-get-all-unique-values-of-a-column-in-vba) then spit those back out to the sheet and `countif()` that. There is also the option of using AdvacedFilter mentioned in another answer on that question. – JNevill Aug 06 '18 at 20:21

2 Answers2

3

While the accepted answer will certainly produce the expected result as long as the values to check are known and contained in row 6, I thought a macro was needed as these values wouldn't be known.

I would loop through the range and, as was suggested, use a scripting dictionary to keep track of the unique values. You can also use the dictionary to keep a count of each instance of each value. I don't see a need for Countif() if a dictionary is used.

Dim wc As Object
Set wc = CreateObject("Scripting.Dictionary")
For Each cell In Selection
 If cell.Value <> "" Then
   wc(cell.Value) = wc(cell.Value) + 1
 End If
Next

This works because VBA will add a key and initialize its value to 0 if it doesn't already exist. Then you can loop through your dictionary an spit out the keys on one row and the values on another.

The only way this differs from the expected result is that the keys wouldn't be sorted, but sorting dictionaries by keys is Covered elsewhere.

Chris Walker
  • 144
  • 6
2

Why not a simple formula?

Presuming your range is C1:H3and the first value-to-check is in C6

=COUNTIF($C$1:$H$3, C$6)

Will produce the expected result

Samuel Hulla
  • 6,617
  • 7
  • 36
  • 70