Create ascending number

[Yaghoub61]

Hi all,

I have four number: 5,4,3,2


I would like be result like this is the only one column.please

1,2,3,4,5,1,2,3,4,1,2,3,1,2


please, without VB & helper column

Thank you so much

[hemesh]

copy paste below in E2 then hold control and shift together then hit enter to make it array formula and drag down

Please Login or Register  to view this content.

Right now your data is in 4 rows As from A2 to A4 if you increase the data set as from A2 to A7 then change the references accordingly like you need to change red part to 6 as you have data now in 6 rows and change the blue part to 7

[Kaper]

Just for curiosity:

As you can see the formula is rather very complicated. While with VBA it would be much easier.

Why have you asked for no VBA and no helper columns?

[hemesh]

@ Yaghoub61, Thanks for the rep !

If that takes care of your problem then take a moment to mark the thread as solved by going to thread tools and marking it as solved.

[Yaghoub61]

Excellent!

Thank you so much for help Hemesh & Kaper

[hemesh]

you are welcome Yaghoub!

[Ghozi Alkatiri]

at D2
=IFERROR(IF(ROW(1:1)>SUM($A$2:$A$5),"",IF(COUNTIF(D$1:D1,D1)=IFERROR(RANK(D1,$A$2:$A$5),0),1,D1+1)),1)
copy down

[hemesh]

Ghozi, nicely tailored solution but this will work with the question only if we do change the numbers as 5,8,10,2 then result will be different.

This is a kind of good problem and I am happy that you stepped in as I was also thinking of some other solution.