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

Jinson

New Member
Joined
Mar 14, 2017
Messages
17
Office Version
  1. 2019
Platform
  1. Windows
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.
 
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
ABCDEFG
1ACBBCUUnique
2CCACAVA
3BABBCWC
4DDBADXB
5ABBDAYD
6BBBDDZ 
7 
8 
9 
10 
11 
12 
13 
Unique List (2)
Cell Formulas
RangeFormula
G2:G13G2=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)),"")
From my point of view with your formula there would be 2 changes which I would have to make if I wish to replicate the formula on any other grid :
1) Change the "6" in the formula to the requisite size of my grid
2) Change the second argument in the Text function to incorporate the change in grid size

whereas with respect to the formula which I have preferred (and which I have memorized) needs only a change of cell range. So lets say if I'm replicating i.e. typing the formula in any other sheet, I would just highlight the required cell range (use CTRL +H) and replace it.


Also I must admire your keen vision!. The preferred formula does give "0" when the data is blank. I'm right now thinking of how to correct this issue in the formula.
 
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
From my point of view with your formula there would be 2 changes which I would have to make if I wish to replicate the formula on any other grid :
1) Change the "6" in the formula to the requisite size of my grid
2) Change the second argument in the Text function to incorporate the change in grid size

whereas with respect to the formula which I have preferred (and which I have memorized) needs only a change of cell range. So lets say if I'm replicating i.e. typing the formula in any other sheet, I would just highlight the required cell range (use CTRL +H) and replace it.


Also I must admire your keen vision!. The preferred formula does give "0" when the data is blank. I'm right now thinking of how to correct this issue in the formula.
I'm working on improving the formula but if it is possible in the meantime can you suggest ways to "Robustify" your Formula or "the selected formula" to not show "0" when data is blank !!
 
Upvote 0
So lets say if I'm replicating i.e. typing the formula in any other sheet, I would just highlight the required cell range (use CTRL +H) and replace it.
Unless I have miss-understood Peter's formula, that all you need to do with his.
+Fluff 1.xlsm
ABCDEFGHIJKLMN
1Name 10Name 18Name 12Name 14Name 14Name 14Name 20Name 2Name 8Name 14Name 13Name 8Unique
2Name 2Name 5Name 5Name 10Name 14Name 9Name 5Name 11Name 2Name 2Name 1Name 1Name 10
3Name 3Name 10Name 2Name 14Name 15Name 14Name 18Name 8Name 18Name 17Name 13Name 11Name 18
4Name 9Name 12Name 13Name 1Name 16Name 17Name 4Name 18Name 9Name 4Name 6Name 18Name 12
5Name 6Name 15Name 4Name 5Name 19Name 10Name 16Name 5Name 11Name 20Name 8Name 3Name 14
6Name 20Name 18Name 13Name 6Name 15Name 19Name 2Name 16Name 12Name 4Name 8Name 5Name 20
7Name 13Name 14Name 10Name 6Name 15Name 20Name 9Name 10Name 11Name 17Name 11Name 4Name 2
8Name 3Name 10Name 7Name 10Name 14Name 7Name 19Name 17Name 18Name 12Name 20Name 8
9Name 5Name 20Name 6Name 9Name 10Name 11Name 7Name 1Name 13Name 17Name 12Name 6Name 13
10Name 17Name 20Name 7Name 4Name 1Name 4Name 15Name 18Name 8Name 9Name 20Name 10Name 5
11Name 14Name 18Name 10Name 1Name 3Name 14Name 8Name 18Name 18Name 16Name 1Name 11Name 9
12Name 10Name 19Name 15Name 20Name 4Name 18Name 8Name 14Name 3Name 17Name 12Name 11
13Name 10Name 1Name 20Name 1Name 6Name 7Name 18Name 8Name 11Name 20Name 19Name 1
14Name 15Name 9Name 11Name 19Name 16Name 14Name 5Name 2Name 6Name 1Name 18Name 1Name 3
15Name 3Name 13Name 13Name 6Name 8Name 2Name 14Name 13Name 11Name 13Name 19Name 20Name 15
16Name 10Name 7Name 8Name 18Name 5Name 8Name 13Name 17Name 6Name 20Name 17
17Name 7Name 7Name 16Name 9Name 12Name 10Name 8Name 2Name 7Name 20Name 19Name 11Name 16
18Name 20Name 6Name 16Name 7Name 6Name 15Name 17Name 11Name 12Name 2Name 12Name 4
19Name 20Name 2Name 10Name 9Name 15Name 18Name 11Name 3Name 15Name 13Name 7Name 8Name 6
20Name 3Name 13Name 8Name 16Name 14Name 8Name 12Name 18Name 4Name 14Name 5Name 6Name 19
21Name 7
22 
23 
24 
25 
26 
27 
Main
Cell Formulas
RangeFormula
N2:N27N2=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW($A$1:$L$20)*10^6+COLUMN($A$1:$L$20))/(($A$1:$L$20<>"")*(ISNA(MATCH($A$1:$L$20,N$1:N1,0)))),1),"R000000C000000"),0),"")
 
Upvote 0
Unless I have miss-understood Peter's formula, that all you need to do with his.
+Fluff 1.xlsm
ABCDEFGHIJKLMN
1Name 10Name 18Name 12Name 14Name 14Name 14Name 20Name 2Name 8Name 14Name 13Name 8Unique
2Name 2Name 5Name 5Name 10Name 14Name 9Name 5Name 11Name 2Name 2Name 1Name 1Name 10
3Name 3Name 10Name 2Name 14Name 15Name 14Name 18Name 8Name 18Name 17Name 13Name 11Name 18
4Name 9Name 12Name 13Name 1Name 16Name 17Name 4Name 18Name 9Name 4Name 6Name 18Name 12
5Name 6Name 15Name 4Name 5Name 19Name 10Name 16Name 5Name 11Name 20Name 8Name 3Name 14
6Name 20Name 18Name 13Name 6Name 15Name 19Name 2Name 16Name 12Name 4Name 8Name 5Name 20
7Name 13Name 14Name 10Name 6Name 15Name 20Name 9Name 10Name 11Name 17Name 11Name 4Name 2
8Name 3Name 10Name 7Name 10Name 14Name 7Name 19Name 17Name 18Name 12Name 20Name 8
9Name 5Name 20Name 6Name 9Name 10Name 11Name 7Name 1Name 13Name 17Name 12Name 6Name 13
10Name 17Name 20Name 7Name 4Name 1Name 4Name 15Name 18Name 8Name 9Name 20Name 10Name 5
11Name 14Name 18Name 10Name 1Name 3Name 14Name 8Name 18Name 18Name 16Name 1Name 11Name 9
12Name 10Name 19Name 15Name 20Name 4Name 18Name 8Name 14Name 3Name 17Name 12Name 11
13Name 10Name 1Name 20Name 1Name 6Name 7Name 18Name 8Name 11Name 20Name 19Name 1
14Name 15Name 9Name 11Name 19Name 16Name 14Name 5Name 2Name 6Name 1Name 18Name 1Name 3
15Name 3Name 13Name 13Name 6Name 8Name 2Name 14Name 13Name 11Name 13Name 19Name 20Name 15
16Name 10Name 7Name 8Name 18Name 5Name 8Name 13Name 17Name 6Name 20Name 17
17Name 7Name 7Name 16Name 9Name 12Name 10Name 8Name 2Name 7Name 20Name 19Name 11Name 16
18Name 20Name 6Name 16Name 7Name 6Name 15Name 17Name 11Name 12Name 2Name 12Name 4
19Name 20Name 2Name 10Name 9Name 15Name 18Name 11Name 3Name 15Name 13Name 7Name 8Name 6
20Name 3Name 13Name 8Name 16Name 14Name 8Name 12Name 18Name 4Name 14Name 5Name 6Name 19
21Name 7
22 
23 
24 
25 
26 
27 
Main
Cell Formulas
RangeFormula
N2:N27N2=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW($A$1:$L$20)*10^6+COLUMN($A$1:$L$20))/(($A$1:$L$20<>"")*(ISNA(MATCH($A$1:$L$20,N$1:N1,0)))),1),"R000000C000000"),0),"")
Thank you for your response !!
Correct me if I'm wrong but you made no changes to the Peter's version of the formula right ? You just created a bigger example ?.

Also I now realize that the solution which I preferred had an issue that it could not handle(blanks) but Peter's version of the formula works just fine !!

Maybe I overjumped with the solution !!

Thank you Peter and Fluff !!!
 
Upvote 0
Correct me if I'm wrong but you made no changes to the Peter's version of the formula right ?
I only changed the ranges from A1:F6 to A1:L20 and the I$1:I1 to N$1:N1 as that is were the formula is
 
Upvote 0
fixed aspect of only a 6*6 matrix ((ROW(A$1:F$6)*10^6).

2 changes which I would have to make if I wish to replicate the formula on any other grid :
1) Change the "6" in the formula to the requisite size of my grid
I originally missed that you had bolded that 6 in the first quote above but the second one alerted me to it. As has been seen by Fluff's amendment, that number is not related to your grid size at all so does not need changing. Further, if you move my formula far enough out of the way of any grid you might have and make the range reference big enough to cover any range you might have, then the formula should not need changing at all.

For example, here I have data in a 10 x 10 grid but the formula has been set up to work with any data up to a 26 column x 1,000 row grid. So provided you have copied the formula down as far as might ever be needed, then unless your data is going to exceed that grid size, there is no need to change anything.

Jinson.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1Name 2Name 3Name 3Name 6Name 8Name 9Unique
2Name 2Name 2Name 7Name 6Name 2
3Name 7Name 1Name 1Name 4Name 3
4Name 5Name 5Name 1Name 4Name 5Name 6
5Name 1Name 3Name 1Name 9Name 1Name 8
6Name 1Name 8Name 7Name 4Name 5Name 1Name 9
7Name 8Name 7
8Name 8Name 3Name 1
9Name 9Name 9Name 4
10Name 6Name 5
11 
12 
13 
14 
Unique List
Cell Formulas
RangeFormula
AA2:AA14AA2=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW(A$1:Z$1000)*10^6+COLUMN(A$1:Z$1000))/((A$1:Z$1000<>"")*(ISNA(MATCH(A$1:Z$1000,AA$1:AA1,0)))),1),"R000000C000000"),0),"")
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top