Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygoogle-query-language

Using Query in Google Sheets, how display a pair only once?


I am trying to group a pair and display it once. A pair can occur in several ways and is currently displayed for each different occurrence.

Removed the bad example of dice.

My actual problem is more complicated than the following example, but this is to create a simple/short set of data to work on.

Breeding combinations of flowers: https://docs.google.com/spreadsheets/d/1-Kkr3P3_LRhFcmhNpf3JJwo5aXu1fp11muRBUxmShKU/edit?usp=sharing

Finished cell input to accomplish the desired output: (F26 in the sheet)

    ={"Can be produced by:"\ "And:"\ "Entries:";
    ARRAYFORMULA(QUERY(IF(D3:D=F4; IF(C3:C<B3:B; {C3:C\ B3:B}; {B3:B\ C3:C}); 
    ); 
    "select Col2,Col1,count(Col1) 
    where Col1 is not null
    group by Col1,Col2
    order by count(Col1) desc
    label count(Col1)''"; 0))}

Solution

  • ={A1:C1;
     ARRAYFORMULA(QUERY(IF(C:C=6, IF(A:A<B:B, {A:A, B:B}, {B:B, A:A}), ), 
     "select Col1,Col2,count(Col1) 
      where Col1 is not null
      group by Col1,Col2
      label count(Col1)''", 0))}
    

    enter image description here


    ={"Can be produced by:"\ "And:"\ "Entries:";
     ARRAYFORMULA(QUERY(IF(D3:D=F4; IF(C3:C<B3:B; {B3:B\ C3:C}; {C3:C\ B3:B}); ); 
     "select Col1,Col2,count(Col1) 
      where Col1 is not null
      group by Col1,Col2
      order by count(Col1) desc
      label count(Col1)''"; 0))}
    

    0