Forum Discussion
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 inspecting laptops and noting down the issues with them in the columns in front of it. The issues will be noted in a random order based on physical observation of the asset. The format of this will be something like this:
So here in column F I have the count of the model number having the exact same set of issues even if the order of nothing these issues is different. I've highlighted the ones having the same issues with the same color for ease of understanding.
The sheet could contain hundred of rows so a summary like this would be really beneficial. I've tried to put in writing the problem the best I can and hopefully someone can help me with this. If you guys need any more information for this please let me know and I'll provide the same.
3 Replies
An alternative could be as shown in the attached file. This solution works in legacy Excel such as Excel 2013. If the result in columns J to O needs to be returned without empty rows then there is a formula as well.
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) ) ) ) ) ) ) )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))
