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.

 

[sandy666]

another option with

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Employees"),
    TSC = Table.SelectColumns(Unpivot,{"Employees"}),
    Distinct = Table.Distinct(TSC, {"Employees"}),
    Sort = Table.Sort(Distinct,{{"Employees", Order.Ascending}})
in
    Sort

 

[Sufiyan97]

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.

I am wondering how you built this formula I think it's tough like coding.

 

[Dossfm0q]

this belong to hrayani
Extract unique items from list > Amend the formula to give sorted results

 

[jeff_cp]

I went with Power Query for this...pretty simple after playing around with it for a bit. Thanks Sandy.

 

[sandy666]

I went with Power Query for this...pretty simple after playing around with it for a bit. Thanks Sandy.

You are welcome
Have a nice day