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...
The formulas mtarler and that which I use here are Excel 365 dynamic array formulas. They are entered on the worksheet grid in the top left cell of the dynamic range they will populate. I have interpreted the requirement of return the final item from each column along with its date.
= LET(
date, TAKE(Table2,,1),
items, DROP(Table2,,2),
d, BYCOL(items, LAMBDA(col, XLOOKUP(TRUE, LEN(col)>0, date, , ,-1))),
v, BYCOL(items, LAMBDA(col, XLOOKUP(TRUE, LEN(col)>0, col, , ,-1))),
TRANSPOSE(VSTACK(v, d))
)mtarler 's formula will also work perfectly well with a Table as input. I show a copy in the attached workbook.
There is sense in Patrick2788 's suggestion of completing the job in PQ and using one computing environment rather than two.
I believe B_Coy mentioned some cleanup is being done on the data (even before an 'unpivot' is done through formulas or PQ).
It might help to see what the raw data looks like even before PQ gets to it.
- Yeah, I have gotten to the goal of 2 columns. My one remaining need is to remove recurring instances of the same Item ID. My need is to know when the first of every item is scheduled. All recurring instances of that particular item are irrelevant and I would like to have them removed from the list. Preferably within PG if possible, as I have managed to do everything else within that tool.
