I am looking for a formula for a Google Sheet.
My goal is to:
- Eliminate duplicate lines based on the text in Column B (Barcode).
- Sum Net Quantities (Column F) based on the text in Column B (Barcode).
- Create a result that contains all of the columns.
*Note - Excluding Columns B (Barcode), some of the fields have differing Product Titles, Titles, SKU data in the cells even though the barcode is the same. That is OK…and it happens because the data is coming in from two different data sources. My needs are to still have an entry even if those fields are different. The entry can correspond to any of the row data that had that barcode.
This is a sample starting data set:
Vendor | Barcode | Product title | Title | SKU | Net quantity |
---|---|---|---|---|---|
Allparts | 645208006555 | 2 Way (On-On) Flat Mini Switch | Chrome | EP-0081-010 | 1 |
Allparts | 645208043000 | 2 Way (On-On) Round Mini Switch | Chrome | EP-4181-010 | 1 |
Allparts | 645208043024 | 2 Way (On-On) Round Mini Switch | Black | EP-4181-003 | 2 |
Allparts | 645208042997 | Allparts 3 Way (On-On-On) Round Mini Switch | Chrome | EP-4180-010 | 1 |
Allparts | 645208043024 | 2 Way (On-On) Round Mini Switch | Black | EP-4181-003 | 2 |
Allparts | 645208042997 | 3 Way (On-On-On) Round Mini Switch | Chrome Switch | EP-4180-010 | 1 |
The following is a sample result:
Vendor | Barcode | Product title | Title | SKU | Net quantity |
---|---|---|---|---|---|
Allparts | 645208006555 | 2 Way (On-On) Flat Mini Switch | Chrome | EP-0081-010 | 1 |
Allparts | 645208043000 | 2 Way (On-On) Round Mini Switch | Chrome | EP-4181-010 | 1 |
Allparts | 645208043024 | 2 Way (On-On) Round Mini Switch | Black | EP-4181-003 | 4 |
Allparts | 645208042997 | Allparts 3 Way (On-On-On) Round Mini Switch | Chrome | EP-4180-010 | 2 |
I have tried using the UNIQUE function with QUERY, but have not successfully been able to include the additional columns of data
CodePudding user response:
Unique will only return rows that are truly unique. As you pointed out, your other columns are not all the same for what you want as unique. It would be easier if you just excluded those columns but if you truly wish to keep those columns you'll need to specify something to come back. You could use these two formulas to get the first row for the other columns.
=UNIQUE(A2:B)
then in the next column:
=filter({VLOOKUP(I2:I,$B:$F,{2,3,4},0),sumif(B:B,I2:I,F:F)},I2:I<>"")