CODEWITHSUNDEEP
vbaexcel
ubuntuuber
ubuntuuber

Reputation: 732

VBA - Excel Organizing/Duplicating Data

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

Upvotes: 0

Views: 71

Answers (2)

Grendizer
Grendizer

Reputation: 292

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

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

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

Upvotes: 3

Related Questions