Forum Discussion

SneakyFunk's avatar
SneakyFunk
Copper Contributor
Apr 30, 2025

Excel - Pivot with permutations & combinations

Hello everyone, I've got a problem with excel that I've not managed to solve and search has not been too helpful either since I'm not sure how to put the exact issue into words.  I will be inspecti...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 30, 2025

If here

entire range under headers (and before count) is named as data, formula could be

=LET(
   n,   ROWS(data),
   ind, SEQUENCE(n),
   SCAN(0, ind,
     LAMBDA( _, i,
       SUM(
         BYROW( data,
           LAMBDA( line, --AND( SORT( CHOOSEROWS( data, i ), , ,1) = SORT( line , , ,1) ) )
         )
      )
     )
  )
)

 

m_tarler's avatar
m_tarler
Bronze Contributor
Apr 30, 2025

I think he wanted to sort and filter to 'unique' lines also so maybe:

=LET(in, Data, r, ROWS(in), c, COLUMNS(in),
in_sorted, SORT(DROP(REDUCE("", SEQUENCE(ROWS(in)),LAMBDA(p,q, VSTACK(p, HSTACK( INDEX(in,q,1), SORT(DROP(CHOOSEROWS(in,q),,1),,,1))))),1),SEQUENCE(COLUMNS(in))),
out_list, UNIQUE(in_sorted),
out_counts, BYROW(out_list,LAMBDA(r,SUM(--(MMULT(--(r=in_sorted),SEQUENCE(c,,1,0))=c)))),
HSTACK(out_list,out_counts))

 

Resources