CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
chop
chop

Reputation: 5

Eliminating duplicate rows and summing data

I am looking for a formula for a Google Sheet.

My goal is to:

*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

Upvotes: 0

Views: 227

Answers (2)

player0
player0

Reputation: 1

try:

=QUERY(A1:F, 
 "select A,B,C,D,E,sum(F) 
  where F>0 
  group by A,B,C,D,E 
  label sum(F)'Net quantity'", 1)

enter image description here

Upvotes: 0

pgSystemTester
pgSystemTester

Reputation: 9932

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

Sample sheet here.

Upvotes: 0

Related Questions