How to combine cells of data in Googlesheet to a single/consolidate row and remove duplicates?

Question

I'm trying to consolidate all the invoices that we get from the same contractor to a single row that will have the business name, all the memos/invoices for the week in one cell along with the added total of all the memos/invoices.

I did a SUMIFS() for the total, then I combined all the memos using ARRAYFORMULA(TEXTJOIN()), THEN I DID REMOVE DUPLICATES - BUT THAT MESSES UP THE INVOICES(COLUMN L)

See attachments.

Thank you in advance!

Answer 1

This answer combines the results of two discrete queries. One to sum the invoice values, another to create csv entries for invoices from duplicate suppliers.

Result

---

Use this query:

=query({query({A2:E},"select Col1, Sum(Col4) where Col1 <> '' group by Col1 label sum(Col4) '' "),ARRAYFORMULA({QUERY(QUERY({A2:B, B2:B}, "select Col1,max(Col2) where Col1 is not null group by Col1 pivot Col3"), "select Col1 offset 1", 0), REGEXREPLACE(TRIM( TRANSPOSE(QUERY(TRANSPOSE(QUERY(QUERY({A2:B&",", B2:B}, "select max(Col2) where Col1 is not null and Col2 <> ',' group by Col1 pivot Col3"), "offset 1", 0)),,999^9))), ",$", )})}, "select Col1,Col4,Col2 label Col1 'Supplier', Col4 'Invoice #s', Col2 'Total'")

---

Sample Data

---

Breakdown - #1: Sum Invoice value, group by Supplier

=query({A2:E},"select Col1, Sum(Col4) where Col1 <> '' group by Col1 label sum(Col4) '' ")

---

Breakdown - #2: Combine duplicate rows in column as comma separated values

=ARRAYFORMULA({QUERY(QUERY({A2:B, B2:B}, "select Col1,max(Col2) where Col1 is not null group by Col1 pivot Col3"), "select Col1 offset 1", 0), REGEXREPLACE(TRIM( TRANSPOSE(QUERY(TRANSPOSE(QUERY(QUERY({A2:B&",", B2:B}, "select max(Col2) where Col1 is not null and Col2 <> ',' group by Col1 pivot Col3"), "offset 1", 0)),,999^9))), ",$", )})

Hat Tip: @player0 Combine duplicate rows in column as comma separated values - Google Query on StackOverflow