VBA - Excel Organizing/Duplicating Data

Question

I am looking for a way to duplicate and rearrange some data that I have using a macro or a function that exists. Any help would be greatly appreciated.

Thank you in advanced!

J

The data is unique ID numbers listed in Column A like below.

Unique ID Numbers - Starting Point

I am trying to have each number duplicated 4 times in a row like the below image...

enter image description here

Gary's Student · Accepted Answer · 2014-09-14 20:19:59Z

3

With your data in column A in B1 enter:

=INDIRECT("A" & ROUNDUP(ROW()/4,0))

and copy down

Then copy column B and Paste/Special/Values back onto column A

answered Sep 14, 2014 at 20:19

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cor_Blimey Over a year ago

=INDEX(A:A,ROUNDUP(ROW()/4,0)) avoids the volatility ;)

Grendizer · Accepted Answer · 2014-09-14 20:29:28Z

1

Sub myMacro()

    ' where data is
    Dim rowData As Integer
        rowData = 2

    ' where data is duplicated 4 times
    Dim rowNew As Integer
        rowNew = 1

    ' loop through all entries of column A
    Do While Range("A" & rowData).Value <> ""

        Do While rowNew Mod 4 <> 0

            ' copy
            Range("B" & rowNew).Value = Range("A" & rowData).Value
            rowNew = rowNew + 1

        Loop

        ' last copy
        Range("B" & rowNew).Value = Range("A" & rowData).Value
        rowNew = rowNew + 1

        ' next row
        rowData = rowData + 1

    Loop

End Sub

answered Sep 14, 2014 at 20:29

Grendizer

2921 gold badge3 silver badges10 bronze badges

Collectives™ on Stack Overflow

VBA - Excel Organizing/Duplicating Data

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related