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

1
votes

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))}
1
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygoogle-query-language
Sure @player0, I added URL to a small subset example copy of the sheet. – Björn Nevitar Johansson

1 Answers

1
votes
={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