Forum Discussion
Vlady
Oct 24, 2019Copper Contributor
Copy State Abbreviations in One Column Multiple Times
Hello, I have a list of state abbreviations and each abbreviation needs to be copied a certain number of times in one column. The attached photo is an sample of what I am trying to do, with a list of...
Ajay K. Singh
Oct 24, 2019Brass Contributor
It is fun doing it but not really worth it unless, you have to do it many times.
Step by step guide:
Insert another leftmost column, so you have a new blank Col A.
Fill it with numbers 1-50 (assuming no territories.)
Insert two more columns before column C
On new C1, put in 1
Go to cell F1 and put in the value that a state has to be repeated - Say 7.
On C2, type in the formula: =C1+1/$F$1
copy this formula down to 350 times (= F1*50 states)
on D1, write the formula =ROUNDDOWN(C1)
Copy this all the way down.
On E1 write the formula: =VLOOKUP(D1,$A$1:$B$50,2)
Copy this formula all the way down.
Hide columns A, C, and D.
The results with alphabets look like this:
| a | a | How many times do you want to repeat? | |||
| b | a | 8 | |||
| c | a | ||||
| d | a | ||||
| e | a | ||||
| f | a | ||||
| g | a | ||||
| h | a | ||||
| i | b | ||||
| j | b | ||||
| k | b | ||||
| l | b | ||||
| m | b | ||||
| n | b | ||||
| o | b | ||||
| p | b | ||||
| q | c | ||||
| c | |||||
| c | |||||
| c | |||||
| c | |||||
| c | |||||
| c | |||||
| c | |||||
| d | |||||
| d | |||||
| d | |||||
| d |