CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup
Mellon Symphonic
Mellon Symphonic

Reputation: 23

Google Query Language - Adding column on a group By

Excuse me this is my first post. I tried to make a nice table like others do but I struggled with the formatting Please consider the following spreadsheet:

table image

Table image

Google spreadsheet: https://docs.google.com/spreadsheets/d/1t9Jvn4cJ4PGdJrEg7oiC6UsY2WaJdSw-d15IWpp3lUg/edit#gid=0

what I would like to get is lower price and lowest price seller per product : Where I am 

=query(datarange,select col(A),min(col2) group by col(A),-1)

I do not get how I can get add the lowest seller because I do add it then I need to group by it. By reading another post it seems arrayformula is the way to get but I do not get how I should proceed...

Upvotes: 2

Views: 100

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34275

I like to do these using sort and sortn:

=sortn(sort(filter(A2:C,A2:A<>""),1,true,2,true),999,2,1,true)

The idea is that you sort so that the lowest price for each product comes first using sort, then drop the duplicates using sortn, leaving only the row with the lowest price for each product.

enter image description here

Upvotes: 0

player0
player0

Reputation: 1

try:

=ARRAYFORMULA({"seller"; IFNA(VLOOKUP(F3:F&G3:G, {A:A&B:B, C:C}, 2, 0))})

0

or in F2:

=ARRAYFORMULA({QUERY(A1:C, 
 "select A,min(B) where C is not null group by A", 1), 
 ARRAY_CONSTRAIN({"seller"; IFNA(VLOOKUP(TRANSPOSE(QUERY(TRANSPOSE(QUERY(QUERY(A2:C,
 "select A,min(B) where C is not null group by A", 0), 
 "offset 1", 0)),,999^99)), {A2:A&" "&B2:B, C2:C}, 2, 0))}, COUNTA(QUERY(A1:C, 
 "select A,min(B) where C is not null group by A", 1)), 1)})

Upvotes: 1

Related Questions