Unique values for an array of n rows and m columns

XcelLearner

Board Regular
Joined
Feb 6, 2016
Messages
52
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi,

I have a table of n rows and m columns. For simplicity, I use a table of 5 rows and 2 columns from B1:C5. I try to find unique values, filtering for blank value (as in C3). I look for a formula that extracts values from both columns and rows - I googled for the usage of UNIQUE function but it seems that solutions are based on either rows or columns, not both. I would also like to count the times of appearance for each unique value.

The expected results would be in B9:B16 for unique values, and C9:C16 for counts.

cKXVUTm.png


Can anyone please help with the formula? I am using Office 365. Thanks a lot.
 
Greetings
this works for Office 2019
What about this, Feedback Please
I have this unique but sorted from Lowest to highest
I think your formula is quite ingenious. I've seen some very clever uses of the FILTERXML function, and this is one. The problem with this is maintainability. It's quite complicated, and few people could modify it or even change the range in it easily. pgc01's formula in post 14 is shorter and simpler for Excel 2019 (although it doesn't sort), and the LET formula in post 15 is also shorter and simpler for versions with the new functions.

If we all agree, I will choose the solution submitted by Peter, revised from Eric's as the best solution. It is like a team work.
This was definitely a team effort! The new functions are still relatively new, and people are experimenting with them in different circumstances. I think we can agree that if you want a SPILL formula, the one in post 15 is the best. But we don't all need to agree! :unsure: Ultimately, this was your question, and you should pick the solution that works best for you. I see Sandy has now added a PQ option.

Thanks for an interesting question that sparked a lively discussion.
 
Upvote 0

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
alternative option, but I need Help of len of result now ok with 1 to 9 if len 10 to be 2 100 to be 3
(highlighted with Red Color)
  1. =NUMBERVALUE(MID(TEXTJOIN("",FALSE,COUNTIF($B$2:$C$6,"<"&TRANSPOSE($B$2:$C$6))),ROW(INDIRECT("1:"&(COUNTA($B$2:$C$6)+COUNTBLANK($B$2:$C$6)))),1))
  2. +NUMBERVALUE(MID(TEXTJOIN("",FALSE,TRANSPOSE(--($B$2:$C$6<>""))),ROW(INDIRECT("1:"&(COUNTA($B$2:$C$6)+COUNTBLANK($B$2:$C$6)))),1))




Unique values for an array of n rows and m columns.xlsx
ABC
1DATEITEMS
29/22/2020VGICTR
39/24/2020EVFC32
49/25/2020DGC
59/29/2020VTPBMI
69/30/2020VGIDIG
7
8
9
10ITEMSQnt
11BMI1
12C321
13CTR1
14DGC1
15DIG1
16EVF1
17VGI2
18VTP1
Sheet2
Cell Formulas
RangeFormula
B11:B18B11=IFERROR(INDEX($B$2:$C$6,MOD(MATCH(1,(NUMBERVALUE(MID(TEXTJOIN("",FALSE,COUNTIF($B$2:$C$6,"<"&TRANSPOSE($B$2:$C$6))),ROW(INDIRECT("1:"&(COUNTA($B$2:$C$6)+COUNTBLANK($B$2:$C$6)))),1))+NUMBERVALUE(MID(TEXTJOIN("",FALSE,TRANSPOSE(--($B$2:$C$6<>""))),ROW(INDIRECT("1:"&(COUNTA($B$2:$C$6)+COUNTBLANK($B$2:$C$6)))),1)))-SUM(COUNTIF($B$2:$C$6,B$10:B10)),0)-1,ROWS($B$2:$C$6))+1,CEILING(MATCH(1,(NUMBERVALUE(MID(TEXTJOIN("",FALSE,COUNTIF($B$2:$C$6,"<"&TRANSPOSE($B$2:$C$6))),ROW(INDIRECT("1:"&(COUNTA($B$2:$C$6)+COUNTBLANK($B$2:$C$6)))),1))+NUMBERVALUE(MID(TEXTJOIN("",FALSE,TRANSPOSE(--($B$2:$C$6<>""))),ROW(INDIRECT("1:"&(COUNTA($B$2:$C$6)+COUNTBLANK($B$2:$C$6)))),1)))-SUM(COUNTIF($B$2:$C$6,B$10:B10)),0),ROWS($B$2:$C$6))/ROWS($B$2:$C$6)),"")
C11:C18C11=IF(B11<>"",COUNTIF($B$2:$C$6,B11),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
alternative option,
The question was for a table of m rows and n columns. I have used your latest formula and inserted another column into the original range and the results are shown below so it seems something is not working. :(

20 11 03.xlsm
ABCD
1DATEITEMS
29/22/2020VGIABCCTR
39/24/2020EVFC32
49/25/2020DGCGGG
59/29/2020VTPBMI
69/30/2020VGIDIG
7
8
9
10ITEMSQnt
11VGI2
12  
13EVF1
14  
15  
16  
17  
18  
Test1
Cell Formulas
RangeFormula
B11:B18B11=IFERROR(INDEX($B$2:$D$6,MOD(MATCH(1,(NUMBERVALUE(MID(TEXTJOIN("",FALSE,COUNTIF($B$2:$D$6,"<"&TRANSPOSE($B$2:$D$6))),ROW(INDIRECT("1:"&(COUNTA($B$2:$D$6)+COUNTBLANK($B$2:$D$6)))),1))+NUMBERVALUE(MID(TEXTJOIN("",FALSE,TRANSPOSE(--($B$2:$D$6<>""))),ROW(INDIRECT("1:"&(COUNTA($B$2:$D$6)+COUNTBLANK($B$2:$D$6)))),1)))-SUM(COUNTIF($B$2:$D$6,B$10:B10)),0)-1,ROWS($B$2:$D$6))+1,CEILING(MATCH(1,(NUMBERVALUE(MID(TEXTJOIN("",FALSE,COUNTIF($B$2:$D$6,"<"&TRANSPOSE($B$2:$D$6))),ROW(INDIRECT("1:"&(COUNTA($B$2:$D$6)+COUNTBLANK($B$2:$D$6)))),1))+NUMBERVALUE(MID(TEXTJOIN("",FALSE,TRANSPOSE(--($B$2:$D$6<>""))),ROW(INDIRECT("1:"&(COUNTA($B$2:$D$6)+COUNTBLANK($B$2:$D$6)))),1)))-SUM(COUNTIF($B$2:$D$6,B$10:B10)),0),ROWS($B$2:$D$6))/ROWS($B$2:$D$6)),"")
C11:C18C11=IF(B11<>"",COUNTIF($B$2:$D$6,B11),"")
 
Upvote 0
المصنف1.xlsx
ABCDE
1DATEITEMS
29/22/2020VGICTRhh
3KKKLHLkk
4GGGnnnnHOL
59/24/2020EVFC32RDR
69/25/2020DGCVGIFMD
79/29/2020VTPBMISSS
89/30/2020hhhAAAAAA
9
10
11
12ITEMSQnt
13AAA2
14BMI1
15C321
16CTR1
17DGC1
18EVF1
19FMD1
20GGG1
21hh1
22hhh1
23HOL1
24kk1
25KKK1
26LHL1
27nnnn1
28RDR1
29SSS1
30VGI2
31VTP1
32 
33
Sheet1
Cell Formulas
RangeFormula
C13:C31C13=IF(B13<>"",COUNTIF($B$2:$D$8,B13),"")
B13:B32B13=IFERROR(INDEX($B$2:$D$8,MOD(MATCH(1,(NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$D$8,"<"&TRANSPOSE($B$2:$D$8)))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$D$8,"<"&TRANSPOSE($B$2:$D$8))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$D$8,"<"&TRANSPOSE($B$2:$D$8)))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$D$8,"<"&TRANSPOSE($B$2:$D$8))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$D$8,"<"&TRANSPOSE($B$2:$D$8)))," ",""))+1)))=1),99)))+NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$D$8<>"")))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$D$8<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$D$8<>"")))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$D$8<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$D$8<>"")))," ",""))+1)))=1),99))))-SUM(COUNTIF($B$2:$D$8,B$12:B12)),0)-1,ROWS($B$2:$D$8))+1,CEILING(MATCH(1,(NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$D$8,"<"&TRANSPOSE($B$2:$D$8)))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$D$8,"<"&TRANSPOSE($B$2:$D$8))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$D$8,"<"&TRANSPOSE($B$2:$D$8)))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$D$8,"<"&TRANSPOSE($B$2:$D$8))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$D$8,"<"&TRANSPOSE($B$2:$D$8)))," ",""))+1)))=1),99)))+NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$D$8<>"")))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$D$8<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$D$8<>"")))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$D$8<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$D$8<>"")))," ",""))+1)))=1),99))))-SUM(COUNTIF($B$2:$D$8,B$12:B12)),0),ROWS($B$2:$D$8))/ROWS($B$2:$D$8)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Re post #25
I'm afraid that the column B formula is incomprehensible to me but to test it I decided to add a few columns and rows and some extra values. This is the result (with formulas from some other posts to compare)

20 11 03.xlsm
ABCDEFGH
1DATEITEMS
29/22/2020VGICTRhh
3KKKLHLkk
4
5qqq
6qqq
7GGGqqqnnnnHOL
89/24/2020EVFqqqY3445C32RDR
9zzqqq
10zzqqq
11zz
129/25/2020DGCzzVGIFMD
13zz
149/29/2020VTPBMISSS
159/30/2020hhhAAAAAA
16
17
18
19Post #:251415
20AAAVGIVGI
21BMICTRKKK
22C32hhGGG
23CTRKKKEVF
24DGCLHLDGC
25EVFkkVTP
26 qqqhhh
27 GGGzz
28 nnnnqqq
29 HOLY3445
30 EVFCTR
31 Y3445LHL
32 C32nnnn
33 RDRC32
34 zzBMI
35 DGCAAA
36 FMDhh
37 VTPkk
38 BMIHOL
39 SSSRDR
40hhhFMD
41AAASSS
42 
Test2
Cell Formulas
RangeFormula
D20:D41D20=LET(a,B2:H15,r,ROWS(a),seq,SEQUENCE(r*COLUMNS(a),,0),arr,INDEX(a,MOD(seq,r)+1,seq/r+1),UNIQUE(FILTER(arr,arr<>0)))
B20:B39B20=IFERROR(INDEX($B$2:$H$15,MOD(MATCH(1,(NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",""))+1)))=1),99)))+NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",""))+1)))=1),99))))-SUM(COUNTIF($B$2:$H$15,B$19:B19)),0)-1,ROWS($B$2:$H$15))+1,CEILING(MATCH(1,(NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",""))+1)))=1),99)))+NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",""))+1)))=1),99))))-SUM(COUNTIF($B$2:$H$15,B$19:B19)),0),ROWS($B$2:$H$15))/ROWS($B$2:$H$15)),"")
C20:C42C20=IFERROR(INDIRECT(TEXT(MIN(IF($B$2:$H$15<>"",IF(ISERROR(MATCH($B$2:$H$15,$C$19:C19,0)),100000*ROW($B$2:$H$15)+COLUMN($B$2:$H$15)))),"\R0\C00000"),FALSE),"")
Dynamic array formulas.
 
Upvote 0
Further, if I change cell E8, the results are even more unexpected.

20 11 03.xlsm
ABCDEFGH
1DATEITEMS
29/22/2020VGICTRhh
3KKKLHLkk
4
5qqq
6qqq
7GGGqqqnnnnHOL
89/24/2020EVFqqq3445C32RDR
9zzqqq
10zzqqq
11zz
129/25/2020DGCzzVGIFMD
13zz
149/29/2020VTPBMISSS
159/30/2020hhhAAAAAA
16
17
18
19Post #:251415
203445VGIVGI
21 CTRKKK
22 hhGGG
23 KKKEVF
24 LHLDGC
25 kkVTP
26 qqqhhh
27 GGGzz
28 nnnnqqq
29 HOL3445
30 EVFCTR
31 3445LHL
32 C32nnnn
33 RDRC32
34 zzBMI
35 DGCAAA
36 FMDhh
37 VTPkk
38 BMIHOL
39 SSSRDR
40hhhFMD
41AAASSS
42 
Test2
Cell Formulas
RangeFormula
D20:D41D20=LET(a,B2:H15,r,ROWS(a),seq,SEQUENCE(r*COLUMNS(a),,0),arr,INDEX(a,MOD(seq,r)+1,seq/r+1),UNIQUE(FILTER(arr,arr<>0)))
B20:B39B20=IFERROR(INDEX($B$2:$H$15,MOD(MATCH(1,(NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",""))+1)))=1),99)))+NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",""))+1)))=1),99))))-SUM(COUNTIF($B$2:$H$15,B$19:B19)),0)-1,ROWS($B$2:$H$15))+1,CEILING(MATCH(1,(NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",""))+1)))=1),99)))+NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",""))+1)))=1),99))))-SUM(COUNTIF($B$2:$H$15,B$19:B19)),0),ROWS($B$2:$H$15))/ROWS($B$2:$H$15)),"")
C20:C42C20=IFERROR(INDIRECT(TEXT(MIN(IF($B$2:$H$15<>"",IF(ISERROR(MATCH($B$2:$H$15,$C$19:C19,0)),100000*ROW($B$2:$H$15)+COLUMN($B$2:$H$15)))),"\R0\C00000"),FALSE),"")
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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