single list of unique values from multiple columns

[gabbrielle]

Hey good people,

So here's my thing. I have multiple columns with keywords I want to analyse. Basically, what I need is a table with the number of time each word is use. My strategy was to use a advance filter and find the unique values, then do a =countif with the three columns as my range. The advance filter does not work with multiple columns (at least I can't get it to work).

So any suggestions either to get the advance filter to work or to get that table with
A) a list of all unique values
B) the count of those unique values for the whole range of the three columns.

Thanks tons

Gabrielle
Montréal


[TABLE="class: grid, width: 354"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]engagements[/TD]
[TD]engagements[/TD]
[TD]engagements[/TD]
[/TR]
[TR]
[TD]manifestations[/TD]
[TD]conversation[/TD]
[TD]bénévolat[/TD]
[/TR]
[TR]
[TD]manifestations[/TD]
[TD]Quotidien[/TD]
[TD]bénévolat[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]bénévolat[/TD]
[/TR]
[TR]
[TD]quotidien[/TD]
[TD]banane[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]bénévolat[/TD]
[/TR]
[TR]
[TD]ne m'implique plus[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]travail pour un groupe[/TD]
[TD]conversation[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]manifestations[/TD]
[TD]prise de parole[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]études[/TD]
[TD]éduquer[/TD]
[TD]événements[/TD]
[/TR]
[TR]
[TD]manifestations[/TD]
[TD]événements[/TD]
[TD]écrits[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]manifestations[/TD]
[TD] [/TD]
[TD]bénévolat[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]conversation[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Unicorn[/TD]
[TD] [/TD]
[TD]bénévolat[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Simone[/TD]
[/TR]
[TR]
[TD]quotidien[/TD]
[TD]conversation[/TD]
[TD]éduquer[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]écrits[/TD]
[/TR]
[TR]
[TD]membre d'un groupe[/TD]
[TD] [/TD]
[TD]écrits[/TD]
[/TR]
[TR]
[TD]manifestations[/TD]
[TD]recherche[/TD]
[TD]événements[/TD]
[/TR]
[TR]
[TD]bénévolat[/TD]
[TD]étuquer[/TD]
[TD]conversations[/TD]
[/TR]
[TR]
[TD]bénévolat[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]quotidien[/TD]
[TD]manifestation[/TD]
[TD]conversations[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]conversation[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]manifestation[/TD]
[TD]écrits[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]conversation[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]bénévolat[/TD]
[TD]membre d'un groupe[/TD]
[TD]écrits[/TD]
[/TR]
[TR]
[TD]écrits[/TD]
[TD]conversation[/TD]
[TD]bénévolat[/TD]
[/TR]
[TR]
[TD]membre d'un groupe[/TD]
[TD]événements[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]membre d'un groupe[/TD]
[TD]lecture[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]conversation[/TD]
[TD]membre d'un groupe[/TD]
[TD]événements[/TD]
[/TR]
[TR]
[TD]travail pour un groupe[/TD]
[TD]conversation[/TD]
[TD]bénévolat[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]conversation[/TD]
[TD]événement[/TD]
[TD]prise de parole[/TD]
[/TR]
[TR]
[TD]manifestations[/TD]
[TD]écrits[/TD]
[TD]conversations[/TD]
[/TR]
[TR]
[TD]manifestations[/TD]
[TD]bénévolat[/TD]
[TD]conversations[/TD]
[/TR]
[TR]
[TD]travail pour un groupe[/TD]
[TD]manifestation[/TD]
[TD]conversations[/TD]
[/TR]
[TR]
[TD]bénévolat[/TD]
[TD]lecture[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub CountUnique()
   Dim Ary As Variant
   Dim r As Long, c As Long
   
   Ary = ActiveSheet.UsedRange.Offset(1)
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         For c = 1 To UBound(Ary, 2)
            If Not .exists(Ary(r, c)) And Not IsEmpty(Ary(r, c)) Then
               .Add Ary(r, c), 1
            ElseIf Not IsEmpty(Ary(r, c)) Then
               .Item(Ary(r, c)) = .Item(Ary(r, c)) + 1
            End If
         Next c
      Next r
      Range("E1").Resize(.Count).Value = Application.Transpose(.keys)
      Range("F1").Resize(.Count).Value = Application.Transpose(.items)
   End With
End Sub

 

[Rick Rothstein]

The following macro is basically the same as the one Fluff posted, but I handle the assignment of elements to the Dictionary differently than Fluff does which has the effect of making my code more compact than his...

[table="width: 500"]
[tr]
	[td]Sub CountUnique()
  Dim V As Variant, Arr As Variant
  Arr = ActiveSheet.UsedRange.Offset(1)
  With CreateObject("Scripting.Dictionary")
    For Each V In Arr
      If Len(V) Then .Item(V) = .Item(V) + 1
    Next
    Range("E1").Resize(.Count).Value = Application.Transpose(.Keys)
    Range("F1").Resize(.Count).Value = Application.Transpose(.Items)
  End With
End Sub[/td]
[/tr]
[/table]

 

[gabbrielle]

....

Clearly, I'm way over my head. I don't even know what to do with the formula. :-/

Thanks you two for answering so quickly. I really appreciate it.

 

[Rick Rothstein]

....

Clearly, I'm way over my head. I don't even know what to do with the formula. :-/

Thanks you two for answering so quickly. I really appreciate it.

They are not formulas, they are macros. See if the following helps you...

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (CountUnique) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Eric W]

If you want a formula version, then try:

	A	B	C	D	E
manifestations
conversation
bénévolat
Quotidien
banane
ne m'implique plus
travail pour un groupe
prise de parole
études
éduquer
événements
écrits
Unicorn
Simone
membre d'un groupe
recherche
étuquer
conversations
manifestation
lecture
événement

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]engagements[/TD]
[TD="bgcolor: #FAFAFA"]engagements[/TD]
[TD="bgcolor: #FAFAFA"]engagements[/TD]
[TD="bgcolor: #FAFAFA"]Words[/TD]
[TD="bgcolor: #FAFAFA"]Count[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]manifestations[/TD]
[TD="bgcolor: #FAFAFA"]conversation[/TD]
[TD="bgcolor: #FAFAFA"]bénévolat[/TD]

[TD="align: right"]8[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]manifestations[/TD]
[TD="bgcolor: #FAFAFA"]Quotidien[/TD]
[TD="bgcolor: #FAFAFA"]bénévolat[/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]bénévolat[/TD]

[TD="align: right"]13[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]quotidien[/TD]
[TD="bgcolor: #FAFAFA"]banane[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]bénévolat[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]ne m'implique plus[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]travail pour un groupe[/TD]
[TD="bgcolor: #FAFAFA"]conversation[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]manifestations[/TD]
[TD="bgcolor: #FAFAFA"]prise de parole[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]études[/TD]
[TD="bgcolor: #FAFAFA"]éduquer[/TD]
[TD="bgcolor: #FAFAFA"]événements[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA"]manifestations[/TD]
[TD="bgcolor: #FAFAFA"]événements[/TD]
[TD="bgcolor: #FAFAFA"]écrits[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA"]manifestations[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]bénévolat[/TD]

[TD="align: right"]7[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]conversation[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA"]Unicorn[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]bénévolat[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Simone[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FAFAFA"]quotidien[/TD]
[TD="bgcolor: #FAFAFA"]conversation[/TD]
[TD="bgcolor: #FAFAFA"]éduquer[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]écrits[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FAFAFA"]membre d'un groupe[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]écrits[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FAFAFA"]manifestations[/TD]
[TD="bgcolor: #FAFAFA"]recherche[/TD]
[TD="bgcolor: #FAFAFA"]événements[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FAFAFA"]bénévolat[/TD]
[TD="bgcolor: #FAFAFA"]étuquer[/TD]
[TD="bgcolor: #FAFAFA"]conversations[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #FAFAFA"]bénévolat[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #FAFAFA"]quotidien[/TD]
[TD="bgcolor: #FAFAFA"]manifestation[/TD]
[TD="bgcolor: #FAFAFA"]conversations[/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]conversation[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]manifestation[/TD]
[TD="bgcolor: #FAFAFA"]écrits[/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]conversation[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="bgcolor: #FAFAFA"]bénévolat[/TD]
[TD="bgcolor: #FAFAFA"]membre d'un groupe[/TD]
[TD="bgcolor: #FAFAFA"]écrits[/TD]

[TD="align: center"]28[/TD]
[TD="bgcolor: #FAFAFA"]écrits[/TD]
[TD="bgcolor: #FAFAFA"]conversation[/TD]
[TD="bgcolor: #FAFAFA"]bénévolat[/TD]

[TD="align: center"]29[/TD]
[TD="bgcolor: #FAFAFA"]membre d'un groupe[/TD]
[TD="bgcolor: #FAFAFA"]événements[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="bgcolor: #FAFAFA"]membre d'un groupe[/TD]
[TD="bgcolor: #FAFAFA"]lecture[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="bgcolor: #FAFAFA"]conversation[/TD]
[TD="bgcolor: #FAFAFA"]membre d'un groupe[/TD]
[TD="bgcolor: #FAFAFA"]événements[/TD]

[TD="align: center"]32[/TD]
[TD="bgcolor: #FAFAFA"]travail pour un groupe[/TD]
[TD="bgcolor: #FAFAFA"]conversation[/TD]
[TD="bgcolor: #FAFAFA"]bénévolat[/TD]

[TD="align: center"]33[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="bgcolor: #FAFAFA"]conversation[/TD]
[TD="bgcolor: #FAFAFA"]événement[/TD]
[TD="bgcolor: #FAFAFA"]prise de parole[/TD]

[TD="align: center"]35[/TD]
[TD="bgcolor: #FAFAFA"]manifestations[/TD]
[TD="bgcolor: #FAFAFA"]écrits[/TD]
[TD="bgcolor: #FAFAFA"]conversations[/TD]

[TD="align: center"]36[/TD]
[TD="bgcolor: #FAFAFA"]manifestations[/TD]
[TD="bgcolor: #FAFAFA"]bénévolat[/TD]
[TD="bgcolor: #FAFAFA"]conversations[/TD]

[TD="align: center"]37[/TD]
[TD="bgcolor: #FAFAFA"]travail pour un groupe[/TD]
[TD="bgcolor: #FAFAFA"]manifestation[/TD]
[TD="bgcolor: #FAFAFA"]conversations[/TD]

[TD="align: center"]38[/TD]
[TD="bgcolor: #FAFAFA"]bénévolat[/TD]
[TD="bgcolor: #FAFAFA"]lecture[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=IF(D2<>"",COUNTIF($A$2:$C$50,D2),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=IFERROR(INDIRECT(TEXT(MIN(IF($A$2:$C$50<>"",IF(COUNTIF($D$1:$D1,$A$2:$C$50)=0,ROW($A$2:$C$50)*100+COLUMN($A$2:$C$50)))),"R00C00"),0),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

However, you still might want to try the macros. It's much easier to update a macro with additional requirements, for example, if you want the list sorted.