Using Unique, Filter and ChooseCols dynamically

[Peter Davison]

I have two sheets of data -
Data Analysis
Report Writer
In Row 11 on both sheets there are headings

I want to know if I can use the contents of what is in Cells D11 to Z11 on the Report Writer sheet to determine the ChooseCols in the Unique Filter array formula below.
The formula I am using brings back some columns correctly but not others.
I think it is looking at respective columns and bringing them back in the wrong place (If I am right)

This is my formula -

=UNIQUE(FILTER(INDEX('Data Analysis'!$B$12:$HZ$1000,
SEQUENCE(ROWS('Data Analysis'!$B$12:$HZ$1000)),
FILTER(COLUMN('Data Analysis'!$B$11:$HZ$11),
(ISNUMBER(MATCH('Data Analysis'!$B$11:$HZ$11,'Report Writer'!$D$11:$Z$11,0)))*('Data Analysis'!$B$11:$HZ$11<>""))),
'Data Analysis'!$B$12:$B$1000<>""))

Any help would be appreciated

 

[Jeffrey Mahoney]

What are in the headings of each sheet? Can you show a portion of the tables using XL2bb?

 

[hagia_sofia]

I want to know if I can use the contents of what is in Cells D11 to Z11 on the Report Writer sheet to determine the ChooseCols in the Unique Filter array formula below.

If there are no duplicate column names within those two sheets and column names match between them then you could use CHOOSECOLS together with XMATCH to return what you need and do it dynamically.

 

[Fluff]

How about

=UNIQUE(FILTER(CHOOSECOLS('Data Analysis'!$B$12:$HZ$1000,XMATCH($D$11:$Z$11,'Data Analysis'!$B$11:$HZ$11)),'Data Analysis'!$B$12:$B$1000<>""))

 

[Peter Davison]

Thank you for helping -
I tried the formula below in Cell 'Report Writer'!D12
=UNIQUE(FILTER(CHOOSECOLS('Data Analysis'!$B$12:$HZ$1000,XMATCH($D$11:$Z$11,'Data Analysis'!$B$11:$HZ$11)),'Data Analysis'!$B$12:$B$1000<>""))
But this returns #N/A
I think it needs a filter included ('Report Writer'!$D$11:$Z$11<>"") in the formula
I tried this and it seems to work okay
=UNIQUE(FILTER(CHOOSECOLS('Data Analysis'!$B$12:$HZ$1000,FILTER(XMATCH($D$11:$Z$11,'Data Analysis'!$B$11:$HZ$11),$D$11:$Z$11<>"")),'Data Analysis'!$G$12:$G$1000<>""))
Do you feel that is the correct way to do this?

 

[Peter Davison]

Further to the above I have added a sort option (See below) -
=SORT(SORT(UNIQUE(FILTER(CHOOSECOLS('Data Analysis'!$B$12:$HZ$1000,FILTER(XMATCH($D$11:$Z$11,'Data Analysis'!$B$11:$HZ$11),$D$11:$Z$11<>"")),'Data Analysis'!$G$12:$G$1000<>"")),2),IFERROR(MATCH("Y",$D$6:$Z$6,0),1))

Fluff - If I remember correctly you very kindly gave me a formula to sub total my results for another routine using the LET function below
=LET(d,SORT(UNIQUE(CHOOSECOLS(FILTER('User Analysis'!$G$12:$AAC$1000,'User Analysis'!$G$12:$G$1000<>""),1,2,3,4,13,22))),u,UNIQUE(INDEX(d,,2)),SORTBY(VSTACK(d,CHOOSE(SEQUENCE(,6),u,"","","",SUMIFS('User Analysis'!S:S,'User Analysis'!H:H,u),SUMIFS('User Analysis'!AB:AB,'User Analysis'!H:H,u))),VSTACK(SEQUENCE(ROWS(d)),XMATCH(u,INDEX(d,,2),0,-1))))

Would it be possible to adapt this LET formula using the sheets and cells contained in first code at the top summing any column with Numeric's?
If this is possible that would be great.
Thank you as always for your support.

 

[Peter Davison]

I did manage to get this far, but wondered if there was an easier formula.
Also I could do with it leaving sub totalling blank if the fields are not numeric (Is this possible)?
Here is my formula -
=IFERROR(LET(d,SORT(SORT(UNIQUE(FILTER(CHOOSECOLS('Data Analysis'!$B$12:$HZ$1000,FILTER(XMATCH($B$11:$Z$11,'Data Analysis'!$B$11:$HZ$11),$B$11:$Z$11<>"")),'Data Analysis'!$G$12:$G$1000<>"")),2),IFERROR(MATCH("Y",$D$6:$Z$6,0),1)),u,UNIQUE(INDEX(d,,2)),SORTBY(VSTACK(d,
CHOOSE(SEQUENCE(,25),
u,
"",
"",
"",
SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(F$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u),
SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(G$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u),
SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(H$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u),
SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(I$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u),
SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(J$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u),
SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(K$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u),
SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(L$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u),
SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(M$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u),
SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(N$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u),
SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(O$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u),
SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(P$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u),
SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(Q$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u),
SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(R$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u),
SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(S$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u),
SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(T$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u),
SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(U$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u),
SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(V$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u),
SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(W$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u),
SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(X$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u),
SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(Y$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u),
SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(Z$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u),)),
VSTACK(SEQUENCE(ROWS(d)),XMATCH(u,INDEX(d,,2),0,-1)))),"")

 

[Fluff]

I tried this and it seems to work okay

Personally I would do it like

=UNIQUE(FILTER(CHOOSECOLS('Data Analysis'!$B$12:$HZ$1000,XMATCH(FILTER($D$11:$Z$11,$D$11:$Z$11<>""),'Data Analysis'!$B$11:$HZ$11)),'Data Analysis'!$B$12:$B$1000<>""))

As your other question is completely different, it needs a new thread.