Identify the same group composition

[Inacio11]

Problem: Compare among the whole list which “Order” has the same composition and add a “Family Group” (letter) to be possible ranking the most often used.
Thanks in advance !

 

[Vogateer]

There might be better approaches, but my thought would be to create a list of that Part-list Groupings and place a number or letter next to it.

For instance, I would add a column to the right of the Family_group column that would sort and list all the parts for that order. If the Order column were in A and Part List column in E, you could get that Part List like this:

=IF($A2<>OFFSET($A2,-1,0),TEXTJOIN(",",TRUE,SORT(FILTER(B2:B7, A2:A7=A2))), OFFSET(E2,-1,0))

That should give you a list of the part numbers separated by a comma.

Then you could create a Part List group by using Unique in some other column. Part-list Grouping formula, adusting "1000" to go to the bottom of that column:

=UNIQUE(FILTER(E2:E1000,(E2:E1000<>"")))

Then I'd put a number or letter to the left of that unique list, and then do an INDEX-MATCH in the Family_group column to get that number or letter for the grouping. Does that make sense?

 

[Inacio11]

Hello @Vogateer. Thanks for your reply !
I've tried according to your suggestion but I can not reach the solution required.
So, in order to make more clear the request I did some update as follow acc. to image.
Thanks in advance !

 

[Inacio11]

on time...

 

[Vogateer]

Could you paste your data into a table here so I can copy it?

 

[Vogateer]

With some adjustment it seems to be working for me. Please let me know whether this is giving the wrong results.

 

[Vogateer]

L2:

=UNIQUE(FILTER($F$2:$F$100,($F$2:$F$100<>"")))

K2: 1, then K3 through K100 (for example):

=IF(L3<>"",K2+1, "")

Create Column F header for the Parts List, then F2:

=IF($B2<>OFFSET($B2,-1,0),TEXTJOIN(",",TRUE,SORT(FILTER($C$2:$C$100,$B$2:$B$100=$B2))),"")

E2 down to E100 (for example):

=IF($F2<>"",INDEX($K$2:$K$100, MATCH($F2,$L$2#,0)), "")

H2 through H100 is just equal to K2 through K100.

I2:

=IF($H2<>"",COUNTIF($E2:$E100, H2), "")