Spread cell values to multiple cells

Hello,
I am looking for a formula for the following. I have tried several ways of using various lookup formulas like index, row, column, offset, indirect etc. but all in vain.

For some reasons, I am unable to add the table from the tool bar here and can neither format text (colors, bold etc.). See attached spreadsheet. Hexcel formula.xlsere's what I am trying to do:

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
1 8 1 1 1 1 1 1 1 1 2 2 2 2 2 3 3 3 3 3 3 3 4 4 4
2 5
3 7
4 3
5

In the first column I have the values that I would like to spread in the first row. As the type of value changes in column 1 (8,5,7 and 3), the numbering changes from (1,2,3 and 4) in row 1. The numbers in row repeat by times of value in the column

By adding some helper columns and row, the attached should work.

Small mistake in the attached file.
Change the formula in F2 to the following, and drag across, to correct:

Formula:

Please Login or Register  to view this content.

Try in B1

Please Login or Register  to view this content.

Thank you Ace_XL. This formula is really simple and easy. Great fundamentals make LOGIC look like piece of cake! Thanks

Hello Ace_XL,
There is a little twist here, what logic (formula) would get me the following result:

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
1 8 2 1 1 1 1 1 1 1 1 0 0 2 2 2 2 2 0 0 0 3 3 3 3 3 3 3 0 4 4 4
2 5 3
3 7 1
4 3 2
5

The change that I made is I added a column after first column which has a number corresponding to each each number in first column. What it does is after the number of times the number in column 1 repeats, it adds number of ZEROES corresponding to the number in column 2. For example for first number (8) after repeating '1' for eight times, it adds '0' Zero two times, it adds zero '3' times after repeating '2' for '5' times, it adds '1' zero after repeating '3' for '7' times etc.

Thank you

Too many IFs..but this works

In C1 use

Please Login or Register  to view this content.

I really apologize Ace_XL for replying late, but the formula worked and thank you very much for the most prompt reply I have ever seen. Once again thank you.