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 state abbreviations on the left and 7 copies of each abbreviation on the right. What would be the easiest way of copying each abbreviation a certain number of times without doing it manually?
2 Replies
Assuming your list starts from A1
=INDEX(A:A,INT((ROW()-1)/7)+1)and copy it down. If another rows, for example list if states starts from row #10 and you'd like to copy it with repeating into another column starting from row #15, when
=INDEX(A:A,INT((ROW()-15)/7)+10)and drag down. Sure better not to hardcode constants.
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
