Populate Table with Relative Number of Relative Entries

jb007

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

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

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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
Joined
May 31, 2013
Messages
22
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

Threads
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...
  • 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...
Top