If function limitations and possible alternatives

[fischerjp]

I am trying to create a program for microsoft excel that allows users to type in what the need in two columns and returns those into headings in rows that the user specified. For example
Column1 Column2
a 2
b 4
c 1
d 2
etc. The user types in what the want in those columns and it appears in a row as
a a b b b b c d d
I am using the if function but I am limited by how many ifs I can use and it stops after the 5th row. I am using =IF(B2>=1,A2) for the first row, =IF(B2>=2,A2,IF(B2=1,A3)) for the second, =IF(B2>=3,A2,IF(B2=2,A3,IF(AND(B2=1,B3>=2),A3,A4))) for the third etc. Is there any other alternative or work around to get a longer list?

[deadlyduck]

The attached might help but may not be sufficiently flexible for your needs as the formula in the cell with the result would need to be changed if your list expands.

[contaminated]

hi and welcome to the forum
Please take a few minutes to take a look at this workbook

Right click on the first sheet and you'll see this code that do this ))))
HTH

Please Login or Register  to view this content.

[fischerjp]

Maybe the attached will help clarify what I am after.

[fischerjp]

This is the actual one I am working on...

[contaminated]

Ok see file
Is this what u want?

Please Login or Register  to view this content.

[fischerjp]

Reply to contaminated,
Your macro is very close however I would like to be able to enter an entire word and have that word placed in the cell then repeated the number of times needed. Is there a way to accomplish that. For example instead of repeating just the letter a replace a with the word quiz.
Thanks

[contaminated]

For example instead of repeating just the letter a replace a with the word quiz.

But macro in post #6 does what you want

For example in A1 you have John and the number 5 next to this cell
So John would be repeated 5 time "transposically" starting from range D1

[fischerjp]

Yes that is exactly what I am looking for. How did you do that? Is there someway I can research and learn what you did. I am decent with functions but the macro you did was incredible (assuming this is a macro that allowed this to happen).

[contaminated]

Ofcource you CAN!!
Just adjust the ranges for your real file and experiment with macro )))

Please acknowledge the response you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved:
1 - Click the Edit button on your first post in the thread,
2 - Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.
If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.