Forum Discussion
Returning Multiple Lines from one cell, but in a list
I'll step you through my solution.
Dynamic named item 'Data'. It expands/contracts based on number of entries in Column A:
=LET(a, Data!$A$2:$G$100000, nonblank, COUNTA(TAKE(a, , 1)), TAKE(a, nonblank))Dynamic named item 'Tasks'. Same idea as the above. It will expand/contract based on number of requested tasks entered.
=LET(a, Summary!$A$4:$A$10000, nonblank, COUNTA(a), TAKE(a, nonblank))An array constant - 'Header'. Nothing fancy here. It will be appended to the data pull.
={ "TASK NUMBER", "TASK DESCRIPTION", "TASK HOURS", "PART REQUIRED", "PART NUMBER", "PART QTY", "PART PRICE (EACH)" }Lambda 'PullTasks'. It runs each of the requested task numbers through a FILTER w/Data. Results are stacked vertically.
=LET(filtered, FILTER(Data, TAKE(Data, , 1) = v), VSTACK(a, filtered))The formula you'll see in the sheet:
=REDUCE(Header,Tasks,PullTasks)
Thanks Patrick - I suppose a picture speaks a thousand words, I should have led with that, apologies!
I am sure there is a way to do it, I just haven't been able to get it to work without stepping it out into several parts, creating unique ID fields which is making it lengthier and more complex than I would like. The whole idea is the input / return section is easy that anyone can pick it up.
Hopefully this will make a little more sense. I have oversimplified it but hopefully gets the idea across.
I'll step you through my solution.
Dynamic named item 'Data'. It expands/contracts based on number of entries in Column A:
=LET(a, Data!$A$2:$G$100000, nonblank, COUNTA(TAKE(a, , 1)), TAKE(a, nonblank))
Dynamic named item 'Tasks'. Same idea as the above. It will expand/contract based on number of requested tasks entered.
=LET(a, Summary!$A$4:$A$10000, nonblank, COUNTA(a), TAKE(a, nonblank))An array constant - 'Header'. Nothing fancy here. It will be appended to the data pull.
={
"TASK NUMBER",
"TASK DESCRIPTION",
"TASK HOURS",
"PART REQUIRED",
"PART NUMBER",
"PART QTY",
"PART PRICE (EACH)"
}
Lambda 'PullTasks'. It runs each of the requested task numbers through a FILTER w/Data. Results are stacked vertically.
=LET(filtered, FILTER(Data, TAKE(Data, , 1) = v), VSTACK(a, filtered))The formula you'll see in the sheet:
=REDUCE(Header,Tasks,PullTasks)
- Thanks Patrick, I will give it a go, this is potentially game changing though!
Thank you so much!!!- You're welcome!