Assigning the same occurrence count to listed items that have been multiplied

[Marbleking]

Hi,

I have a list of items and a running count of the number of occurrences of each item in the list. I also have a formula that multiplies the number of occurrences of each item in the list with any given multiplier, so that the list expands accordingly.

Now I need to modify the formula for counting the number of occurrences of each item so that each item that has been expanded by the given multiplier gets the same count number as it had before, for both the original occurrence and the extra occurrences that have been added as a result of the multiplier.

The counting formula I use, which I'd like to be modified, is this one for the original list in range A2:A8:

Please Login or Register  to view this content.

Please see attached workbook. I have colored the cells to make the point clearer.

Best regards,
Marbleking

[Bo_Ry]

Maybe try

=INDEX($A$2:$B$8,SEQUENCE(ROWS(A2:A8)*H1,,,1/H1),{1,2})

[Marbleking]

Thanks Bo_Ry,

Is it possible to only make the formula dependent on the expanded item list in column D and with a reference to the multiplier in cell H1?

Regards,
Marbleking

[Bo_Ry]

Then try

=INT(MMULT((D2:D22=TRANSPOSE(D2:D22))*(ROW(D2:D22)>TRANSPOSE(ROW(D2:D22))),ROW(D2:D22)^0)/H1)+1

[Marbleking]

Great, thanx!

Regards,
Marbleking