Tidy up data

[Al Mackay]

I've been given a spreadsheet which I have to tidy up each day, which
hopefully with your help this could be automated??

Each line is made up like the following:
A B C
Row (Odd Number) Always "object:" Data 1
Row (Even Number) Unique Number * Data 2

What I want to be able to do is combine the data together from row 2
within row 1, e.g.

Data from even number rows will be added on from column D onwards
Is this possible through VBA?

Many thanks for your help with this, Al.

([email protected])

 

[Tom Ogilvy]

Yes it is possible Al.

Where do you want the result? On a new sheet.

Does the data start on Row 1?

 

[Al Mackay]

Hi Tom

Thanks for replying to this - in answer to your questions.

Where do you want the result? On a new sheet.
- On a new sheet would be preferable.
Does the data start on Row 1?
- The data does start in Row 1.

Thanks Tom, Al.

 

[Tom Ogilvy]

Sub CopyData()
Dim lastRow as long, i as Long
Dim sh as Worksheet
Set sh = Worksheets("Sheet2") ' destination sheet
Dim rw as Long
rw = 1
lastRow = Cells(rows.count,1).End(xlup).Row
for i = 1 to lastRow - 1 Step 2
cells(i,1).Resize(1,3).copy Destination:=Sh.Cells(rw,1)
cells(i+1,1).Resize(1,10).copy Destination:=sh.Cells(rw,4)
rw = rw + 1
Next
End Sub

Change 10 to the number of columns that you want copied from the even rows.

If you don't want to start at column A, then change the individual 1 in
cells(i+1,1) to indicate which column to start copying from.

the sheet with the original data should be the activesheet when you run the
macro.