Repeating formula in a specific number of row

Closed
Report
-
 Trowa -
Hello,
I'm trying to develop a formula that will repeat a result in a specific number of rows. For example, if there was a number "2" in A2 and a "45" in H2, I would like the number 45 to populate twice in the same column in two different rows (for example column J, rows 3 and 4). How can that be done with excel formulas?

1 reply

Hi CP007,

If you are going to put the result in J3 and below use this formula in J3:
=ALS($A$2>=1;$H$2;"")
Now copy the formula down depending on how big your number in A2 can become.
If A2 cannot be bigger then 10; copy the formula down till J12.
Add 1 to the 1 in the formula for each cell you go down.
So In J4 the formula will be:
=ALS($A$2>=2;$H$2;"")
In J5:
=ALS($A$2>=3;$H$2;"")

If the number in A2 is something like 100, this way can be a lot of work.
Use a free column and type 1 in the top cell and 2 in the cell below,
select the two cells and drag them down till you have 100.
If your free column is L, change the formula into this:
=ALS($A$2>=L1;$H$2;"")
Drag the formula down 100 cells and you are done.
You can hide column L for a clean look.


Let me know if this solution works for you.

Best regards,
Trowa
0