SOLVED

Returning Multiple Lines from one cell, but in a list

Copper Contributor

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? 

5 Replies
You could create a FILTER formula for this task. If you're able to provide sample data then a formula can be drawn up.

@Patrick2788 

 

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. 

Search & Return multiple.JPG

best response confirmed by VI_Migration (Silver Contributor)
Solution

@MattMartin 

 

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)

 

Patrick2788_0-1678885770366.png

 

Thanks Patrick, I will give it a go, this is potentially game changing though!

Thank you so much!!!
You're welcome!
1 best response

Accepted Solutions
best response confirmed by VI_Migration (Silver Contributor)
Solution

@MattMartin 

 

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)

 

Patrick2788_0-1678885770366.png

 

View solution in original post