Printable View
I'm still trying to learn Tables and some of the new 365 functions
I have FILTER sort of sorted out, but I want to pull selected column(s) from the FILTERed range into another dynamic range
Attached is sample table, the results of FILTER based on entered value (Green), and a column number entered (Yellow)
I'd like to just a dynamic array in the Blue
Attachment 31930
Thanks
Headers (S1):
=CHOOSECOLS(Table1[#Headers],1,H2)
databody (S2):
=FILTER(CHOOSECOLS(Table1,1,H2),Table1[AAA]=G2,"No "&G2)
and in one (V1):
=LET(ColmAFltr,G2,ColmNo,H2,VSTACK(CHOOSECOLS(Table1[#Headers],1,ColmNo),FILTER(CHOOSECOLS(Table1,1,ColmNo),Table1[AAA]=ColmAFltr,"No "& ColmAFltr)))
full lambda (not needed but for development) (Y1):
=LAMBDA(ColmAFltr,ColmNo,VSTACK(CHOOSECOLS(Table1[#Headers],1,ColmNo),FILTER(CHOOSECOLS(Table1,1,ColmNo),Table1[AAA]=ColmAFltr,"No "& ColmAFltr)))(G2,H2)
Put the red part as a name into Name Manager leaving you with (AB1):
=MyNewTable(G2,H2)
This is challenging Paul. Would the following concept do the job?
Sorry but unable to correct the formula. "0" should be the greek symbol theta (Zero with a slash across it.) The intent is to create the filter criteria. It compares the values in the selected column (determined by H2) with the value in G2. Only rows where this comparison is TRUE are included in the filtered result.Code:=FILTER(Table1, INDEX(Table1, 0, H2) = G2)
@p45cal
1. Thanks
2. This part I can grasp fairly easily since I look at your other postings (at least a little)
Attachment 31933Code:=FILTER(CHOOSECOLS(Table1,1,H2),Table1[AAA]=$G$2,"No "&G2)
3. The LAMBDA I only understand at a conceptual level, and I have a personal aversion to very long formulas since I can never get the ('s and )'s and ['s and ]'s right
4. Not important but I noticed that if there was not a match, the LAMBDA formulas failed ugly
'Attachment 31934
5. Thanks again
Then you might not like this one either.....:devil2:
Code:=LET(ColmAFltr,G2, ColmNo,H2, FilterCol, INDEX(Table1[#Headers], 1, 1), FilterColData, INDEX(Table1, 0, 1), FilterCol2, _
INDEX(Table1[#Headers], 1, ColmNo), FilterCol2Data, INDEX(Table1, 0, ColmNo), VSTACK(CHOOSECOLS(Table1[#Headers], 1, ColmNo), _
FILTER(CHOOSECOLS(Table1, 1, ColmNo), FilterColData = ColmAFltr, "No " & ColmAFltr)))
I LOVE it
Now I don't have to count sheep at night
I can just count parens and brackets until .... Z-Z-Z-z-z-z
Yes, I wasn't going to clutter things up even more by including IFERRORs and such like sprinkled liberally here and there.Quote:
Originally Posted by Paul_Hossler