Excel Numbering and Subnumbering-CodePudding

I am trying to achieve this info in column B with a function so I can fill it down a very long database, is it applicable?

Thank you so much in advance.

CodePudding user response：

For those without Office 365:

=SUMPRODUCT(1/COUNTIFS($B$2:$B2,$B$2:$B2))&"."&SUMPRODUCT(($B$2:$B2=B2)/COUNTIFS($B$2:$B2,$B$2:$B2,$C$2:C2,$C$2:C2))&"."&SUMPRODUCT((($B$2:$B2=B2)*($C$2:C2=C2))/COUNTIFS($B$2:$B2,$B$2:$B2,$C$2:C2,$C$2:C2,$D$2:D2,$D$2:D2))

One note, it requires that the data be sorted by Fruit,Item.

CodePudding user response：

If you have Excel 365 you can use FILTER and UNIQUE.

I inserted a table for the data --> with that the formulas are more self explaining.

Furthermore I added helper columns to create the single IDs per column. I prefer this method - as I think it is easier to understand than one complex formula.

The formulas are:

IDFamily: =MATCH([@Family],UNIQUE([Family]),0)

IDItem: =MATCH([@Item],UNIQUE(FILTER([Item],[Family]=[@Family])),0)

IDColor: =MATCH([@Color],UNIQUE(FILTER([Color],([Family]=[@Family])*([Item]=[@Item]))),0)

All these formulas retrieve a subset of data based on the left values (unique and filtered), e.g. "Orange, Banana, Apple" for Family "Fruits"

Then the index of the value (e.g. for apple) is returned (= 3rd item of subset) as the ID for Item Apple.

S/N: =TEXTJOIN(".",TRUE,Table1[@[IDFamily]:[IDColor]])