Forum Discussion
Solved
Returning Multiple Lines from one cell, but in a list
Hi Excel Folks,
I'm hoping someone more knowledgeable than me may be able to assist here.
I am trying to speed up a quotation spreadsheet and if I can make this work it will hugely decrease what is largely a manual process at the moment. I want to be able to take a list of tasks supplied via a customer (this could be 10 lines to 1000 lines) and return task specific data for each line, which is stored in a seperate Tab within the file. This is fairly straightforward in principal via lookups, but I have hit a stumbling block.
The issue I have is that the data I need to return in the look up may appear with multiple lines, and I need to return all of those lines for each task.
For example, if a customer wishes to quote for task A, B, C, D.... tasks A and B may return 1 line each, C may need to return 4 lines and D may need to return 3 lines. Standard look up will only return the first line for each task.
I almost need to do a return which pulls in any/all tasks with the corresponding task code.
I have managed to do this in a very clunky format with helper columns and assigning unique IDs to each line, but I am sure there must be a better / more efficient way to do this. The whole goal of the exercise is to make this as easy / user friendly as possible.
Can anyone help please?
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)
- You could create a FILTER formula for this task. If you're able to provide sample data then a formula can be drawn up.
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)
