Search code examples
excelvbadatasetcopy-paste

Copy & Paste strings into a single column X times, one string at a time

I am trying to create an Excel dataset, and am looking for VBA code to do the following. I already have the ID & Name columns, but need to copy/paste the State column like so:

ID Name State
1 Homer Simpson Arizona
2 Ned Flanders Arizona
3 Barney Gumble Arizona
4 Waylon Smithers Arizona
1 Homer Simpson Utah
2 Ned Flanders Utah
3 Barney Gumble Utah
4 Waylon Smithers Utah
1 Homer Simpson California
2 Ned Flanders California
3 Barney Gumble California
4 Waylon Smithers California
1 Homer Simpson New York
2 Ned Flanders New York
3 Barney Gumble New York
4 Waylon Smithers New York

I have the states on the same worksheet/tab as followed:

State
Arizona
Utah
California
New York

Thank you.

Solution

  • Formula in C2 and copy down

    =IF(A2=1,INDEX($F$2:$F$17,COUNTIF($A$1:A2,1)),C1)

    enter image description here