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