Forum Discussion

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 30, 2025

Re: Excel - Pivot with permutations & combinations

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) ) )
         )
      )
     )
  )
)

 

1 Reply

  • m_tarler's avatar
    m_tarler
    Silver 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))