Auto fill in rows based on cell values

[circularloop]

Hello guys,

Looking some help in excel formula (not macros). I have group of units based on their sizes. e.g. type X=2 units, type Y=1 units, Z=3 units and so on filled in excel as below;

[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Z[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

so based on the value in column B as above, I want all rows beyond 5 to populate/list each item repetitively as following;
[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]2[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3[/TD]
[TD]Z[/TD]
[/TR]
</tbody>[/TABLE]


any help on this is greatly appreciated.

 

[Joe4]

Welcome to the Board!

Using VBA is definitely the easiest way to go. I don't know if there is a way to do it without it. If there is, it is probably going to use some complex functions, and you will need to figure out exactly how many rows to copy those formulas down for.

 

[circularloop]

Thanks Joe. May be 500 rows as max. But yeah I'm looking with formulas which is a challenge.

 

[Joe4]

It is very hard to replicate "loop-like" situations without VBA. I cannot think of a way to do it.
If you figure it out, be sure to post back here. I would love to see how you do it.