Auto populate range of rows and columns with text based on absolute value

**mojian** · 11-21-2017, 10:19 AM

Greetings!

I need some help in the following scenario:

I am trying to populate a range of rows and columns with a predefined text and value (absolute). I have the excel sheet template where the scenario is explained in detail in the below link.

https://www.dropbox.com/s/s9y3tijuhr...ATOR.xlsx?dl=0

In summary:

AI = 12
AO = 11
DI = 8
DO = 8

Will result in the following:
------------------------------------------> Range of 10 cells, if value exceeds 10, then continue on new row as shown below
1 2 3 4 5 6 7 8 9 10
|AI |AI |AI |AI |AI |AI |AI |AI |AI |AI|
|AI |AI |AO|AO |AO|AO|AO|AO|AO|AO|
|AO|DI |DI |DI |DI |DI |DI |DI|DI|DO|
|DO|DO|DO|DO|DO|DO|DO|

Any help is highly appreciated!!

Thanks in advance!

**alvin-chung** · 11-21-2017, 10:52 AM

hi mojian, first of all I think your result doesn't display correctly as there should be 12 "AO" instead?

nevertheless, assume your sample data are in cells B5:C8 below are the formula that you can try copy/paste into cells B10:K13

B10=MID(REPT($B$5,$C$5)&REPT($B$6,$C$6)&REPT($B$7,$C$7)&REPT($B$8,$C$8),(ROWS($B$10:$B10)-1)*10*2+(COLUMNS($B$10:B$10)*2-1),2)

note: the assumption here is your text always 2 chars

**mojian** · 11-22-2017, 03:27 AM

Wow! Thanks a million alvin-chung!

I had no idea the functions you used even exist in Excel!! With this I have now a new direction where I can try to solve this issue! Once I am able to jump down to a new row automatically, I will post your solution here!

Once again, highly appreciate your genius!

**mojian** · 11-22-2017, 05:15 AM

Greetings World!

Thanks to alvin-chung, the solution has been completed!
I have attached the final result.

You, sir alvin-chung, deserve a medal!

Best regards!

PS: Link to download the file: https://www.dropbox.com/s/1rf64oxhh9...ATOR.xlsx?dl=0