Extract unique list of names vertically from a 6*6 matrix( 6 rows and 6 columns)

[Jinson]

Hello !!

I have a matrix of 6*6 with names of all my colleagues. I want to extract the unique names vertically. I'm using Microsoft excel 2019. Also the matrix range is dynamic i.e. it can be 3by3, 7by7, 3by2 ( 3 rows and 2 columns), 2by3 (2 rows and 3 columns) and so on. Looking for a either a power query or a formula solution.

Thanks!!

PS : I already know the trick of using Ctrl + C twice to open clipboard, copy all the names, paste vertically and use remove duplicate feature.

 

[bebo021999]

Try:

Book1 (2).xlsx
	A	B	C	D	E	F	G
1	A	C	B	B	C		Unique
2	C	C	A	C	A		A
3	B	A	B	B	C		C
4	D	D	B	A	D		B
5	A	B	B	D	A		D
6	B	B	B	D	D
7
8
9
10
11
12
13
Sheet2

Cell Formulas
Range	Formula
G2:G13	G2	=IFERROR(INDIRECT(ADDRESS(INT(AGGREGATE(15,6,IF(COUNTIF($G$1:G1,$A$1:$E$6)=0,ROW($A$1:$E$6),"")+IF(COUNTIF($G$1:G1,$A$1:$E$6)=0,COLUMN($A$1:$E$6)/100,""),1)),MOD(AGGREGATE(15,6,IF(COUNTIF($G$1:G1,$A$1:$E$6)=0,ROW($A$1:$E$6),"")+IF(COUNTIF($G$1:G1,$A$1:$E$6)=0,COLUMN($A$1:$E$6)/100,""),1),1)*100)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[alansidman]

Power Query

Book13
	A	B	C	D	E	F	G	H
1	Column1	Column2	Column3	Column4	Column5	Column6		Value
2	Sam	Mary	Jack	Louis	Al	Joe		Sam
3	Mary	Luis	Al	Joe	Sim	Joanne		Mary
4	Jack	Sam	Sim	Hal	Don	Mary		Jack
5	Louis	Joe	Jim	Jack	Hal	Bill		Louis
6								Al
7								Joe
8								Luis
9								Sim
10								Joanne
11								Hal
12								Don
13								Jim
14								Bill
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Value", Text.Trim, type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Trimmed Text")
in
    #"Removed Duplicates"

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have MS365 you can try the column H formula below (formula only needs to go in cell H2), otherwise try column I (formula needs to be copied down as far as you might need)

21 10 29.xlsm
	A	B	C	D	E	F	G	H	I
1	Name 2	Name 3	Name 3	Name 6	Name 8	Name 9		Unique	Unique
2	Name 2	Name 6	Name 2	Name 7	Name 4	Name 6		Name 2	Name 2
3	Name 8	Name 7	Name 1	Name 8	Name 5	Name 1		Name 8	Name 3
4	Name 9	Name 5	Name 5	Name 3	Name 1	Name 4		Name 9	Name 6
5	Name 1	Name 3	Name 1	Name 9	Name 9	Name 1		Name 1	Name 8
6	Name 1	Name 8	Name 7	Name 4	Name 5	Name 1		Name 3	Name 9
7								Name 6	Name 7
8								Name 7	Name 4
9								Name 5	Name 1
10								Name 4	Name 5
11
12
Unique List

Cell Formulas
Range	Formula
H2:H10	H2	=LET(a,A1:F6,r,ROWS(a),seq,SEQUENCE(r*COLUMNS(a),,0),arr,INDEX(a,MOD(seq,r)+1,seq/r+1),UNIQUE(FILTER(arr,arr<>0)))
I2:I12	I2	=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW(A$1:F$6)*10^6+COLUMN(A$1:F$6))/((A$1:F$6<>"")*(ISNA(MATCH(A$1:F$6,I$1:I1,0)))),1),"R000000C000000"),0),"")
Dynamic array formulas.

 

[Jinson]

Try:

Book1 (2).xlsx
	A	B	C	D	E	F	G
1	A	C	B	B	C		Unique
2	C	C	A	C	A		A
3	B	A	B	B	C		C
4	D	D	B	A	D		B
5	A	B	B	D	A		D
6	B	B	B	D	D
7
8
9
10
11
12
13
Sheet2

Cell Formulas
Range	Formula
G2:G13	G2	=IFERROR(INDIRECT(ADDRESS(INT(AGGREGATE(15,6,IF(COUNTIF($G$1:G1,$A$1:$E$6)=0,ROW($A$1:$E$6),"")+IF(COUNTIF($G$1:G1,$A$1:$E$6)=0,COLUMN($A$1:$E$6)/100,""),1)),MOD(AGGREGATE(15,6,IF(COUNTIF($G$1:G1,$A$1:$E$6)=0,ROW($A$1:$E$6),"")+IF(COUNTIF($G$1:G1,$A$1:$E$6)=0,COLUMN($A$1:$E$6)/100,""),1),1)*100)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you for your response !! it was very helpful!!

 

[Jinson]

Power Query

Book13
	A	B	C	D	E	F	G	H
1	Column1	Column2	Column3	Column4	Column5	Column6		Value
2	Sam	Mary	Jack	Louis	Al	Joe		Sam
3	Mary	Luis	Al	Joe	Sim	Joanne		Mary
4	Jack	Sam	Sim	Hal	Don	Mary		Jack
5	Louis	Joe	Jim	Jack	Hal	Bill		Louis
6								Al
7								Joe
8								Luis
9								Sim
10								Joanne
11								Hal
12								Don
13								Jim
14								Bill
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Value", Text.Trim, type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Trimmed Text")
in
    #"Removed Duplicates"

Awesome Power Query Solution !! Thank you !! Both Formula and Power Query solution worked for me !!

 

[Jinson]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have MS365 you can try the column H formula below (formula only needs to go in cell H2), otherwise try column I (formula needs to be copied down as far as you might need)

21 10 29.xlsm
	A	B	C	D	E	F	G	H	I
1	Name 2	Name 3	Name 3	Name 6	Name 8	Name 9		Unique	Unique
2	Name 2	Name 6	Name 2	Name 7	Name 4	Name 6		Name 2	Name 2
3	Name 8	Name 7	Name 1	Name 8	Name 5	Name 1		Name 8	Name 3
4	Name 9	Name 5	Name 5	Name 3	Name 1	Name 4		Name 9	Name 6
5	Name 1	Name 3	Name 1	Name 9	Name 9	Name 1		Name 1	Name 8
6	Name 1	Name 8	Name 7	Name 4	Name 5	Name 1		Name 3	Name 9
7								Name 6	Name 7
8								Name 7	Name 4
9								Name 5	Name 1
10								Name 4	Name 5
11
12
Unique List

Cell Formulas
Range	Formula
H2:H10	H2	=LET(a,A1:F6,r,ROWS(a),seq,SEQUENCE(r*COLUMNS(a),,0),arr,INDEX(a,MOD(seq,r)+1,seq/r+1),UNIQUE(FILTER(arr,arr<>0)))
I2:I12	I2	=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW(A$1:F$6)*10^6+COLUMN(A$1:F$6))/((A$1:F$6<>"")*(ISNA(MATCH(A$1:F$6,I$1:I1,0)))),1),"R000000C000000"),0),"")
Dynamic array formulas.

Hello Peter, Thank you for your response and suggestion !! Have done the needful for future reference !!

PS : Actually I had highlighted in the query that I use Microsoft Excel 2019

 

[Peter_SSs]

PS : Actually I had highlighted in the query that I use Microsoft Excel 2019

Indeed you had and I missed it even though bold.
I guess I am just so used to looking here

Anyway, thanks for updating your details.

Given that version, if you did want to go for a formula version, the one I posted in column I of my mini-sheet is considerably simpler than the one you appear to have chosen.

 

[Jinson]

Indeed you had and I missed it even though bold.
I guess I am just so used to looking here
View attachment 50107
Anyway, thanks for updating your details.

Given that version, if you did want to go for a formula version, the one I posted in column I of my mini-sheet is considerably simpler than the one you appear to have chosen.

I'm sorry Peter, I believe I should have clarified that the solution posted by fellow member considered the dynamic aspect of the question I had. The dynamic aspect was that the matrix is dynamic and can changed orientation from being 6*6 to say 3by2 ( 3 rows and 2 columns) or 4by3( 4 rows and 2 columns ) and so on. I would like to humbly point out that the solution suggested by you considered the fixed aspect of only a 6*6 matrix ((ROW(A$1:F$6)*10^6).

If my interpretation is incorrect please correct me !!.

 

[Peter_SSs]

If my interpretation is incorrect please correct me !!.

Your interpretation that my formula was written for a 6x6 grid is correct.
I think that your interpretation that the longer formula would work for any size grid is incorrect as in the formula it explicitly uses $A$1:$E$6 (a 5x6 grid) several times in the formula.

Below I have copied the exact mini-sheet to a blank worksheet and added a 6th column to the data and you will see that none of those values appear in the list.

Have I mis-interpreted what you were getting at?

BTW, there is one other small difference between the formulas. If there happens to be any blank cells in the range, my formula ignores them whereas the other formula will report that as a "0" unique value. Not sure if blanks are possible with your data and if so how you would want them treated.

21 10 29.xlsm
	A	B	C	D	E	F	G
1	A	C	B	B	C	U	Unique
2	C	C	A	C	A	V	A
3	B	A	B	B	C	W	C
4	D	D	B	A	D	X	B
5	A	B	B	D	A	Y	D
6	B	B	B	D	D	Z
7
8
9
10
11
12
13
Unique List (2)

Cell Formulas
Range	Formula
G2:G13	G2	=IFERROR(INDIRECT(ADDRESS(INT(AGGREGATE(15,6,IF(COUNTIF($G$1:G1,$A$1:$E$6)=0,ROW($A$1:$E$6),"")+IF(COUNTIF($G$1:G1,$A$1:$E$6)=0,COLUMN($A$1:$E$6)/100,""),1)),MOD(AGGREGATE(15,6,IF(COUNTIF($G$1:G1,$A$1:$E$6)=0,ROW($A$1:$E$6),"")+IF(COUNTIF($G$1:G1,$A$1:$E$6)=0,COLUMN($A$1:$E$6)/100,""),1),1)*100)),"")