FILTER and extract a column

[Paul_Hossler]

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

31930

Thanks

[p45cal]

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)

[Aussiebear]

This is challenging Paul. Would the following concept do the job?


=FILTER(Table1, INDEX(Table1, 0, H2) = G2)


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.

[Paul_Hossler]

@p45cal

1. Thanks

2. This part I can grasp fairly easily since I look at your other postings (at least a little)


=FILTER(CHOOSECOLS(Table1,1,H2),Table1[AAA]=$G$2,"No "&G2)

31933

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

'31934

5. Thanks again

[Aussiebear]

Then you might not like this one either.....:devil2:


=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)))

[Paul_Hossler]

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

[p45cal]

4. Not important but I noticed that if there was not a match, the LAMBDA formulas failed uglyYes, I wasn't going to clutter things up even more by including IFERRORs and such like sprinkled liberally here and there.