Table/function that counts number of occurrences within multiple columns

[Superion]

First off: hello everyone!

I have an interesting problem, and I have been looking for the solution just about everywhere!

I have a table in excel, with a header. Within that table, I have three columns of data, which contain various topics, like so:

	M	N	O	P
1	education	justice	law
2	privacy	law
3	entertainment	education
4	education	courts	police	records

I need a count of each occurrence of a topic within those four columns. Using the previous example, I would get a result like this:

AA	BB
education	3
privacy	1
law	2

...and so forth and so on.

The important thing here: I have no idea what the topics will be. So that kills COUNTIF and similar functions. After all my looking for solutions, I'm beginning to have the sneaking suspicion that this can only be solved via VBA code. If that's truly the case, please note in advance that I am a VBA newbie...I found out how to enable it in Excel 2010, and that's about it! So please be clear in providing any steps I need to take.

I'd GREATLY appreciate anyone's help...I'm really stumped! Thanks in advance!

[JapanDave]

Try This,

Sub d()

    Dim a, v, b, sOut, r
    
       a = ActiveSheet.UsedRange
          With CreateObject("Scripting.dictionary")
            For Each r In a
                If Not .exists(r) Then
                  .add r, r
                  .Item(r) = 1
                Else
                     .Item(r) = .Item(r) + 1
                End If
            Next r
                    v = .items
                    b = .Keys
                    For i = LBound(b) To UBound(b)
                        sOut = sOut & vbNewLine & b(i) & " " & v(i)
                    Next i
    MsgBox Mid$(sOut, 2)
          End With
End Sub

[rylo]

Hi

How about

Sub aaa()
  Set nodupes = CreateObject("Scripting.dictionary")
  
  For Each ce In Range("M1:P4")
    If Len(ce.Value) > 0 Then
      If Not nodupes.exists(ce.Value) Then
        nodupes.Add Item:=1, Key:=CStr(ce.Value)
      Else
        nodupes(ce.Value) = nodupes(ce.Value) + 1
      End If
    End If
  Next ce
  
  For Each ce In nodupes.keys
    outrow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Cells(outrow, 1).Value = ce
    Cells(outrow, 2).Value = nodupes(ce)
  Next ce
End Sub

rylo