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

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

<colgroup><col span="3"></colgroup><tbody>
</tbody>

 

[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
1	engagements	engagements	engagements	Words	Count
2	manifestations	conversation	bénévolat	manifestations	8
3	manifestations	Quotidien	bénévolat	conversation	10
4			bénévolat	bénévolat	13
5	quotidien	banane		Quotidien	4
6			bénévolat	banane	1
7	ne m'implique plus			ne m'implique plus	1
8	travail pour un groupe	conversation		travail pour un groupe	3
9	manifestations	prise de parole		prise de parole	2
10	études	éduquer	événements	études	1
11	manifestations	événements	écrits	éduquer	2
12				événements	5
13	manifestations		bénévolat	écrits	7
14		conversation		Unicorn	1
15	Unicorn		bénévolat	Simone	1
16			Simone	membre d'un groupe	5
17	quotidien	conversation	éduquer	recherche	1
18			écrits	étuquer	1
19	membre d'un groupe		écrits	conversations	5
20	manifestations	recherche	événements	manifestation	3
21	bénévolat	étuquer	conversations	lecture	2
22	bénévolat			événement	1
23	quotidien	manifestation	conversations
24		conversation
25		manifestation	écrits
26		conversation
27	bénévolat	membre d'un groupe	écrits
28	écrits	conversation	bénévolat
29	membre d'un groupe	événements
30	membre d'un groupe	lecture
31	conversation	membre d'un groupe	événements
32	travail pour un groupe	conversation	bénévolat
33
34	conversation	événement	prise de parole
35	manifestations	écrits	conversations
36	manifestations	bénévolat	conversations
37	travail pour un groupe	manifestation	conversations
38	bénévolat	lecture

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E2 =IF(D2<>"",COUNTIF($A$2:$C$50,D2),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D2 {=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),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.