# Populate Table with Relative Number of Relative Entries

#### jb007

##### New Member
Ha! I bet the title made you think something like: "What the hell is this dude trying to do ?", right ?

Hello fellows,
What I'm trying to create is a system using either a function or a macro that would give me the following result.

Input:
4 A
3 B
5 C
2 D
6 E

This means that I want 4 "A", 3 "B", 5 "C", 2 "D" and 6 "E" on the same row, in columns next to one another.
=> Output: A A A A B B B C C C C C D D E E E E E E
Important: The letters must be in the suggested order and for that exact number of times !!

1)I am actually working with numbers instead of letters, but I reckon it would be the same approach really.

2) In the example above you can only see 20 (4+3+5+2+6) letters, but I'm actually using more than 800 numbers, and they change based on other manual input, and so does their frequency.
( i.e. next time I might need 24 "x", 3 "y" and 17 "z")

3) I have tried 2 approaches so far:

a) Using nested IFs:

This would look something like:
=IF(Columns(\$A:B)<4,"A",if(Columns(\$A:B)<3,"B",if(Columns(\$A:B)<5,"C",...etc..)))) --> instead of 4, 3 and 5 having cell references, of course

But even 64 IFs (maximum number Excel 2010 can support) are not enough, and I would really use something else rather than a formula that fills two thirds of my screen.

b) Create another sheet from where to VLOOKUP the number (letter) that I want.
In the example above, I need to have in my table:
"A" from columns 1 to 4
"B" from columns 5 to 7
"C" from columns 8 to 12
"D" from columns 13 to 14
"E" from columns 15 to 20

Therefore, in the other sheet that I created, there would automatically be 5 tables generated one next to the other
Table 1: from 1 to 4 => columns 1 2 3 4 -> are filled with the letter "A"
Table 2: from 5 to 7 => columns 5 6 7 -> are filled with the letter "B"
Table 3: from 8 to 12 => columns 8 9 10 11 12 -> are filled with the letter "C"
Table 4: from 13 to 14 => columns 13 14 -> are filled with the letter "D"
Table 5: from 15 to 20 => columns 15 16 17 18 19 20 -> are filled with the letter "E"

Potential problem: Since the frequency of each letter is not fixed, I need to create very large (large number of columns) tables.
(i.e. each of the 5 tables could have like 50 columns, because there is the possibility of having 50 "A"s, which would require a table that has 50 columns, all filled with "A")

Now, if I have a 50 columns space in table 1, and I only need 4 "A", the first table would only contain
-> the 1 2 3 4 columns, all filled with "A" (the rest of 46 columns would remain empty)
-> the 2nd table would contain the 5 6 7 columns, all filled with "B" (the rest 50-3=47 columns remain empty)
-> until the last table would contain the last 6 columns filled with "E" ( the rest 50-6=44 columns remain empty)

Actual problem: As previously stated, I'm working with an 800-something column table, and using this approach, I ended up creating a 8,000 column table (because most of it is empty, as you can see) in the second sheet, used for VLOOKUP-ing data.
And by the way, Excel has around 16,000 columns sheets, so it's obvious that another approach is needed, but I am a bit stuck.
I feel that there is a faster and clever way of achieving this, but the solution is not obvious to me at the moment.

PS: The reason for which I created the very large 8,000 columns table in the second sheet that contains all the columns (from 1 to 800) that I need, is that I want to simply be able to use VLOOKUP in the main sheet, w/o using any Nested IFs.

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### NeonRedSharpie

##### Well-known Member
Two options:

VBA code would be very easy to write for this

Second, look into =REPT. If your sample (small sample) data begins in A1:

Code:
``=REPT(B1,A1)&REPT(B2, A2)&REPT(B3, A3)&REPT(B4, A4)&REPT(B5, A5)``

#### jb007

##### New Member
The second options would be a bit clumsy, because in reality, instead of letters I'm using numbers (with many decimals)
For example I need to have: 1.312321 1.312321 1.312321 2.8809 2.8809 2.8809 2.8809 .. etc

Could you help me with the VBA code please ?

#### jb007

##### New Member
I managed to write the VBA code that gets the job done.
Thanks for the idea, NeonRedSharpie

Replies
0
Views
33
Replies
9
Views
43
Replies
2
Views
31
Replies
4
Views
43
Replies
0
Views
14

1,109,434
Messages
5,528,742
Members
409,832
Latest member
Misspears10

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...