Combine multiple columns of table data into a single column with duplicates removed

[jeff_cp]

I have multiple columns of data listing out employees from different divisions. Each column is a query and will have different numbers of employees and in some cases, the query can return a NULL employee. I need to combine these employees into a single column with the duplicates removed. Here is an example:

Division1	Division2	Division3	Division4	Division5	Division6	Division7	Division8	Division9	Division10	Division11	Division12
BRIAN	DANNY RAY	BOB	BRIAN	JASON		BRIAN	BRIAN	GINA
DAVE	JASON	DANNY RAY	EDWARD			DANNY RAY	DANNY RAY
DANNY RAY		JASON	GINA			GINA	GINA
GINA						KRISTEN	JASON
JASON							KRISTEN
KRISTEN

I need to combine the employees in columns A thru L into a single list in column M and remove any duplicates. I also need to leave the NULL value if it exists in any of the queries. The resulting list would look like this:

Employees
BOB
BRIAN
DANNY RAY
DAVE
EDWARD
GINA
JASON
KRISTEN
NULL

I need VBA to do this as I want this to occur when I open the spreadsheet. I have each of the 12 queries set to run when I open the spreadsheet so they will be populated. I need the VBA to produce a list of unique employees that I can use in another worksheet with getpivotdata formulas to summarize some data on other worksheets.

Thanks in advance for any help with this.

 

[xlyfe]

I currently have a thread open trying to solve a similar problem.
Stacking multiple nonadjacent columns into one (best practice)

Anyways, I went ahead and took a stab at yours since it's a similar (sort of) issue to mine.
Let me know if you have any problems dissecting what I did. Just remember to enter this formula as an array formula.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Division1	Division2	Division3	Division4	Division5	Division6	Division7	Division8	Division9	Division10	Division11	Division12
2	BRIAN	DANNY RAY	BOB	BRIAN	JASON		BRIAN	BRIAN	GINA						BRIAN
3	DAVE	JASON	DANNY RAY	EDWARD			DANNY RAY	DANNY RAY							DANNY RAY
4	DANNY RAY		JASON	GINA			GINA	GINA							BOB
5	GINA						KRISTEN	JASON							JASON
6	JASON							KRISTEN							GINA
7	KRISTEN														DAVE
8															EDWARD
9															KRISTEN
10
11
12
13
Sheet3

Cell Formulas
Range	Formula
O2:O13	O2	=INDIRECT(TEXT(MIN(IF(($A$2:$L$7<>"")*(COUNTIF($O$1:O1,$A$2:$L$7)=0),ROW($2:$7)*100+COLUMN($A:$L),7^8)),"R0C00"),)&""

 

[xlyfe]

You could probably also use a pivot table for this unique list as well.
That might be easier to make quickly, assuming this is something that you are doing often.

 

[xlyfe]

Here's what the pivot table of this unique list could be made to look like.

Book1
	Q
1	Row Labels
2	BOB
3	BRIAN
4	DANNY RAY
5	DAVE
6	EDWARD
7	GINA
8	JASON
9	KRISTEN
Sheet3

 

[sandy666]

Division1	Division2	Division3	Division4	Division5	Division6	Division7	Division8	Division9	Division10	Division11	Division12
BRIAN	DANNY RAY	BOB	BRIAN	JASON		BRIAN	BRIAN	GINA
DAVE	JASON	DANNY RAY	EDWARD			DANNY RAY	DANNY RAY
DANNY RAY		JASON	GINA			GINA	GINA
GINA						KRISTEN	JASON
JASON							KRISTEN
KRISTEN
Employees
BOB
BRIAN
DANNY RAY
DAVE
EDWARD
GINA
JASON
KRISTEN

let
    Source = Table.ToColumns(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    C2T = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Distinct = Table.Distinct(Table.ExpandListColumn(C2T, "Column1")),
    Filter = Table.SelectRows(Distinct, each ([Column1] <> null)),
    Sort = Table.Sort(Filter,{{"Column1", Order.Ascending}}),
    Ren = Table.RenameColumns(Sort,{{"Column1", "Employees"}})
in
    Ren

 

[jeff_cp]

Here's what the pivot table of this unique list could be made to look like.

Book1
	Q
1	Row Labels
2	BOB
3	BRIAN
4	DANNY RAY
5	DAVE
6	EDWARD
7	GINA
8	JASON
9	KRISTEN
Sheet3

Can you provide instructions on how to do this with a pivot table? I tried to figure it out but not having any luck.

 

[xlyfe]

Can you provide instructions on how to do this with a pivot table? I tried to figure it out but not having any luck.

In order to save me from having to take lots of screen shots, I found this online which shows exactly how to achieve this pivot table.

But even better, you can now achieve this with a formula if you have the LET function available to you.
Check it out here.

 

[Dossfm0q]

Formula option
Press CTRL+SHIFT+ENTER to enter array formulas.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Division1	Division2	Division3	Division4	Division5	Division6	Division7	Division8	Division9	Division10	Division11	Division12
2	BRIAN	DANNY RAY	BOB	BRIAN	JASON		BRIAN	BRIAN	GINA
3	DAVE	JASON	DANNY RAY	EDWARD			DANNY RAY	DANNY RAY
4	DANNY RAY		JASON	GINA			GINA	GINA
5	GINA						KRISTEN	JASON
6	JASON							KRISTEN
7	KRISTEN
8
9
10
11
12	Employees
13	BOB
14	BRIAN
15	DANNY RAY
16	DAVE
17	EDWARD
18	GINA
19	JASON
20	KRISTEN
21
22
23
24
25
26
27
Sheet2

Cell Formulas
Range	Formula
A13:A27	A13	=IFERROR(INDEX($A$2:$L$7,MOD(MATCH(1,(NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($A$2:$L$7,"<"&TRANSPOSE($A$2:$L$7)))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($A$2:$L$7,"<"&TRANSPOSE($A$2:$L$7))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($A$2:$L$7,"<"&TRANSPOSE($A$2:$L$7)))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($A$2:$L$7,"<"&TRANSPOSE($A$2:$L$7))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($A$2:$L$7,"<"&TRANSPOSE($A$2:$L$7)))," ",""))+1)))=1),99)))+NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($A$2:$L$7<>"")))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($A$2:$L$7<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($A$2:$L$7<>"")))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($A$2:$L$7<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($A$2:$L$7<>"")))," ",""))+1)))=1),99))))-SUM(COUNTIF($A$2:$L$7,A$12:A12)),0)-1,ROWS($A$2:$L$7))+1,CEILING(MATCH(1,(NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($A$2:$L$7,"<"&TRANSPOSE($A$2:$L$7)))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($A$2:$L$7,"<"&TRANSPOSE($A$2:$L$7))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($A$2:$L$7,"<"&TRANSPOSE($A$2:$L$7)))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($A$2:$L$7,"<"&TRANSPOSE($A$2:$L$7))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($A$2:$L$7,"<"&TRANSPOSE($A$2:$L$7)))," ",""))+1)))=1),99)))+NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($A$2:$L$7<>"")))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($A$2:$L$7<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($A$2:$L$7<>"")))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($A$2:$L$7<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($A$2:$L$7<>"")))," ",""))+1)))=1),99))))-SUM(COUNTIF($A$2:$L$7,A$12:A12)),0),ROWS($A$2:$L$7))/ROWS($A$2:$L$7)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[veyselemre]

Hi,

Sub test()
    Dim lastRow&, lastColumn&, rng As Range, kys As Variant
    lastRow = [a1].CurrentRegion.Rows.Count
    lastColumn = [a1].CurrentRegion.Columns.Count
    With CreateObject("Scripting.Dictionary")
        For Each rng In Range("a2", Cells(lastRow, lastColumn)).SpecialCells(xlCellTypeConstants)
            .Item(rng.Value) = Null
        Next
        kys = Application.Transpose(.keys)

    End With
    With Cells(1, lastColumn + 2).Resize(UBound(kys), 1)
        .Value = kys
        .Sort .Cells(1)
    End With
End Sub

 

[Fluff]

Another VBA option

Sub jeffcp()
   Dim Ary As Variant
   Dim r As Long, c As Long
   
   Ary = Range("A1").CurrentRegion.Value2
   
   With CreateObject("system.collections.arraylist")
      For r = 2 To UBound(Ary)
         For c = 1 To UBound(Ary, 2)
            If Not .contains(Ary(r, c)) And Ary(r, c) <> "" Then .Add Ary(r, c)
         Next c
      Next r
      .Sort
      Sheets("Sheet2").Range("A2").Resize(.Count).Value = Application.Transpose(.toarray)
   End With
End Sub