Excel LET function to return filtered values of various size based on a criteria row by row
I am working with this data set:
What I need is to return the list of values corresponding to to each unique category. Seems straight forward and I generated this using excel formula
=TRANSPOSE(FILTER($B$2:$B$11,$A$2:$A$11=D2)))
by dragging down. Here's the output:
What I need eventually is to use the list of values for each of the categories, as a variable of LET function, to output everything all at once as a part of bigger formula. I have tested this set of solutions:
=LET(AllCategories,$A$2:$A$11,values,$B$2:$B$11,myCategories,D2:D4,output,MAP(myCategories,LAMBDA(x,TRANSPOSE(FILTER(values,AllCategories=x)))),output)=LET(categories,D2:D5,BYROW(categories,LAMBDA(category,HSTACK(category,FILTER($B$2:$B$11,$A$2:$A$11=category)))))=LET( uniqueCategories, UNIQUE(A2:A11), BYROW(uniqueCategories, LAMBDA(category, HSTACK(category, TRANSPOSE(FILTER(B2:B11, A2:A11=category))))) )=LET(uniqueCategories,UNIQUE(A2:A11),valuesList,MAP(uniqueCategories, LAMBDA(category, HSTACK(category, FILTER($B$2:$B$11, $A$2:$A$11=category)))), valuesList)
The goal of formulas 1-4 was to go by each unique category row by row, and return values based on category in col A. In each case I have got the #CALC! as a result.
Looking for a help here. I've googled the problem, eployed ( :) ) chatGPT to try to figure it out and still cannot solve it.
using REDUCE/LAMBDA:
=IFERROR(DROP(REDUCE("",UNIQUE(A2:A11),LAMBDA(z,y,VSTACK(z,HSTACK(y,TRANSPOSE(FILTER(B2:B11,A2:A11=y)))))),1),"")
- Not able to iterate through all the rows in 2D array in Row-Wise Binary Search
- How can I remove a specific item from an array in JavaScript?
- Get the last item in an array
- Searching for part of a string within an array
- Fastest way to convert Image to Byte array
- How to quickly find the minimum element to the right for every element of a Numpy array?
- JS sort array of pair with null and empty values at the end
- bash script to Echo multiple lists
- storing char in char array - incompatible types in assignment of char to char[]
- Inserting JSON data with 'insertAdjacentElement' instead of 'innerHTML'
- Extract associative array elements into individual variables
- How can I loop through enum values for display in radio buttons?
- Split a comma-delimited string into an array?
- How to transform array into desired output using jolt
- How to override only method signature in typescript?
- How do I remove an element from an array at a specified index but not do the operation in-place
- Using array values to select database table in laravel
- Custom Fields - Repeater dropdown - list only unique & not blank values
- Get taxonomies in a category archive page
- How can I compare a clicked elements ID with another ID?
- Unexpected memory allocation when using array views (julia)
- How can I check if a lua table contains only sequential numeric indices?
- Snowflake - challenge in accessing objects in an array
- Convert a Result Set from SQL Array to Array of Strings
- How to convert an array into an object?
- How do I know an array contains all zero(0) in Javascript
- JS create array from a sorted array of pairs
- Insert array into MySQL database with PHP
- How to explode only on the last occurring delimiter?
- Java two varargs in one method