0

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! enter image description here enter image description here enter image description here enter image description here

1 Answer 1

0

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

results


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

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) '' ")

Sum Invoice value, group by Supplier


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))), ",$", )})

duplicates to csv

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.