CODEWITHSUNDEEP
google-sheets-formulaunique
Chano
Chano

Reputation: 53

Make Some Columns Not Repeat Values with Google Sheets Query Formula

Supposing I have the following data on a table:

Data

And I want to return the result below:

Desired Output

Of course, query formula:=query(data!A:F,"SELECT A,B,C,F,D,E WHERE A=44910") won't achieve this b/c it will list all repeating values.

I am trying to list the results in column D and E (Location and Contact) only once instead of repeating them.

A sheet is shared here.

Upvotes: 1

Views: 433

Answers (3)

Aresvik
Aresvik

Reputation: 4620

Try this in cell A1:

=arrayformula({data!A1:C1,data!F1,data!D1:E1;filter({data!A2:C,data!F2:F,if(countifs(data!D2:D,data!D2:D,row(data!D2:D),"<="&row(data!D2:D))=1,data!D2:E,)},data!A2:A=44910)})

enter image description here

Upvotes: 1

Mike Steelson
Mike Steelson

Reputation: 15318

If you can't add columns, apply conditional formatting in E2: F with the formula:

=$B1=$B2

with blank color

Upvotes: 1

basic
basic

Reputation: 11968

Add formula to G2:

=ArrayFormula(IF(B1:B1501<>B2:B1502,{E2:E1502,F2:F1502},""))

then hide E and F columns

enter image description here

Upvotes: 1

Related Questions