Forum Discussion
How to Extract a 2-Column List from a Table
Above is a sample of the table I am trying to extrapolate from. To simplify what I am looking to do, is I want to create a list of every item in Columns 3+ with its associated Date in Column...
B_Coy assuming you have 365 then try this:
=LET(in,A2:E21,
cols,TOROW(HSTACK(SEQUENCE(COLUMNS(in)-2,1,1,0),SEQUENCE(COLUMNS(in)-2,1,3))),
twoCols, WRAPROWS(TOROW(CHOOSECOLS(in,cols)),2),
UNIQUE(FILTER(twoCols,TAKE(twoCols,,-1)<>0,"")))in line 1 you change the range to the whole table (or just use the table name if it is formatted as a table.
line 2 creates a list like 1,3,1,4,1,5,1,6... for the size of the table
line 3 pulls the columns in the order defined in line 2 and then reformats it back to 2 columns
line 4 filters all the lines with 0 / blank values and for unique values