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