Forum Discussion
How to Extract a 2-Column List from a Table
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
I'm not terribly familiar with implementing more complex functions like this. Where do I need to put this in to test it? The exact size can vary in both rows and columns from one project to another, and I would prefer if I could run it without having to adjust the size manually. But I am already filtering the OG spreadsheet down to the necessary lines via Power Query, so if I use the name of the table that creates, I can have it always take the correct amount of rows/columns?
And not to make things too much more complex, but what about creating separate columns for Item type. i.e., putting all the Prefixed 'WT' items in their own column separate from the 'FS' items, and so on?
