Home > Software design >  Eliminating duplicate rows and summing data
Eliminating duplicate rows and summing data

Time:12-08

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

enter image description here

  • Related