How do I filter (or sort) repeated cell value combinations?-CodePudding

I sell seven different books (call them books A, B, C, D, E, F, and G). An order consists of a combination of these seven books at a time, resulting in multiple orders that are the same, but with many variations. For example, one order may be for just A, two for A and B, three for A, B, F, and G, and so on.

I have my data set out in Google Sheets so that books A to G are in columns (N to T, if that helps) and I mark them as "1" when the book is included in an order, and "blank" if not. I want to sort them so I can pack and ship all matching orders at the same time. For example:

A
A
A
AB
ACG
ABCD
ABCD
ABDEFG
ABDEFG

I have been using the filter function to do this for the past three years (it only shows up each combination at a time), but it is incredibly time-consuiming to set up all of the necessary filters required for all possible combinations of the seven books. Is there a way to do this more easily?

CodePudding user response：

Assuming that the values A, B, C... are in the range N1:T1, use byrow() and filter(), like this:

=query( 
  byrow( 
    N2:T, 
    lambda( 
      row, 
      if( 
        sum(row), 
        join("", filter(N1:T1, row)), 
        iferror(1/0) 
      ) 
    ) 
  ), 
  "where Col1 is not null order by Col1", 0  
)

This gives you a sorted list of all combinations in the data, repeating each combination as many times as that combination exists in the data.

To sort the sheet using these values, choose View > Freeze > 1 row and put this formula in row 1 of a free column:

={ 
  "Sort key"; 
  byrow( 
    N2:T, 
    lambda( 
      row, 
      if( 
        sum(row), 
        join("", filter(N1:T1, row)), 
        iferror(1/0) 
      ) 
    ) 
  ) 
}

Then select the new column and choose Data > Sort sheet > Sort sheet by column.

CodePudding user response：

Another approach not that different, depending on the kind of information you may need is to have a total amount per combination:

=QUERY(BYROW(N2:T,LAMBDA(each,IFERROR(JOIN(" ",FILTER(N1:T1,each=1))))),"SELECT Col1,COUNT(Col1) where Col1 is not null group by Col1 order by Col1")

That would give you a list of the orders and the amount of each one: