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
Capture.JPG
Thanks
Last edited by Aussiebear; 04-15-2025 at 12:13 PM.
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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)
Last edited by p45cal; 04-15-2025 at 12:53 PM.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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.=FILTER(Table1, INDEX(Table1, 0, H2) = G2)
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
@p45cal
1. Thanks
2. This part I can grasp fairly easily since I look at your other postings (at least a little)
Capture.JPG=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
'Capture1.JPG
5. Thanks again
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Then you might not like this one either.....
=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)))
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
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
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Yes, I wasn't going to clutter things up even more by including IFERRORs and such like sprinkled liberally here and there.Originally Posted by Paul_Hossler
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Posting Permissions
Reply With Quote
