Supposing I have the following data on a table:
And I want to return the result below:
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.
Add formula to G2:
=ArrayFormula(IF(B1:B1501<>B2:B1502,{E2:E1502,F2:F1502},""))
then hide E and F columns
If you can't add columns, apply conditional formatting in E2: F with the formula:
=$B1=$B2
with blank color
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)})
