Populate Table with Relative Number of Relative Entries


New Member
May 31, 2013
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.

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.

Some videos you may like

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).


Well-known Member
Jul 14, 2014
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:

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


New Member
May 31, 2013
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 ?

Watch MrExcel Video

Forum statistics

Latest member

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...
    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...
    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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...