Join/Collect (Distinct?) Help with Combining Separate Line Items Based on Unique Values

Adam Barber

So I'm having an issue (lack of understanding) on how to use join/collect to turn a sheet like this:

Into one that combines Qty/Supplies into one line item based on data from multiple other columns.

End result like this:

I would like to get it so that Supplies/Qty get merged when Department, ID Number, Drop Off Location, and Use Date are the same. But if any of those are different, to remain a separate line item.

Is this even possible? I'm out of my depth on this one.

Thank you all in advance,

Adam

prime_nathaniel

@Adam Barber there is no simple answer for your date parameter, since its variable in dimension (number of rows) and it is also dynamic by other factors (like department). So you can't just build out a calendar easily to then fill out with it.

If you are willing to let go of qty and item being concatenated, you can just use reports with multiple group and summary fields.

Sheet will be your source data sheet
fields would be all the fields you have in your summary with qty and item as two separate columns
group by item, date, team
summarize quantity sum

Adam Barber

Unfortunately letting go of Qty would defeat the purpose. The end goal is to be able to get these line items in a convenient enough package to print labels from the information. Without having multiple labels per order with redundant information (i.e. ID#, Dept., Use Date, etc.).

If it's not possible, it's not possible. At least it helps me resolve with why I couldn't figure it out.