-1

I'm needing to turn this list in excel into a list that changes to the next row every 5 rows. Any help would be very much appreciated!

So I'm trying to turn

VS1M001WT1
VS1M090RD1
VS1M116BN1
VS1M116NP1

into

VS1M001WT1
VS1M001WT1
VS1M001WT1
VS1M001WT1
VS1M001WT1
VS1M090RD1
VS1M090RD1
VS1M090RD1
VS1M090RD1
VS1M090RD1
VS1M116BN1
VS1M116BN1
VS1M116BN1
VS1M116BN1
VS1M116BN1
VS1M116NP1
VS1M116NP1
VS1M116NP1
VS1M116NP1
VS1M116NP1
SNapperMx
  • 1
  • In other words, you have a list of unique values, and you want to "duplicate" that list where each value gets listed 5 times? – BruceWayne Mar 08 '17 at 04:23
  • @BruceWayne essentially, yes! – SNapperMx Mar 08 '17 at 04:26
  • 4
    I would vote to close as a duplicate, you can find your solution [in this thread](https://stackoverflow.com/questions/20619453/copy-excel-cell-x-number-of-times). Put this in column B, or wherever you want your new list: `=INDEX(A:A,INT((ROW()-1)/5)+1)`. (Assuming your list is in column A). – BruceWayne Mar 08 '17 at 04:30
  • @BruceWayne Sick! That seems to do it. Thanks for helping with that! – SNapperMx Mar 08 '17 at 04:40
  • 1
    Possible duplicate of [Copy EXCEL cell x number of times](http://stackoverflow.com/questions/20619453/copy-excel-cell-x-number-of-times) – Pᴇʜ Mar 08 '17 at 09:09

1 Answers1

0

Implement this macro. Suppose your list is in column A and you want the desired result in column B.

Sub aa()
rowCnt = Cells(Rows.Count, "A").End(xlUp).Row - 1
Range("a1").Select

For j = 0 To rowCnt
ActiveCell.Offset(j, 0).Activate
Selection.Copy
Range("b1").Select
ActiveCell.Offset(5 * j, 0).Select
Selection.PasteSpecial
ActiveCell.Copy

For i = 1 To 4
ActiveCell.Offset(1, 0).PasteSpecial
ActiveCell.Copy
Next i
Range("a1").Select
Next j
End Sub
aspan88
  • 607
  • 6
  • 18