Skipping equal numbers in VBA loop

Question

I am attempting to convert UAV coordinate data from XML to an excel table, which involves transposing three rows of coordinates in to one. The data is currently set out as follows [Column letters indicated in ()]:

(A)        (B)
Mark     Coordinate
1          -97.373773
1          124.34848
1          225
2          -97.746343
2          123.48343
2          225
3          -97.342533
3          123.23454
3          225

I would like to create a loop which copies all the coordinate and elevation data for each of the "mark" data and pastes it in to one line, as follows:

(A)        (B)           (C)            (D)
1         -97.373773    124.34848       225
2         -97.746343    123.48343       225
3         -97.342533    123.23454       225

Can someone give me some insight as to how to create a loop which will skip the duplicate "mark" numbers in the A Column, so that I can copy and paste the relevant info in to one row?

Cheers in advance!

Answer 1

This will transfer your two A:B columns over to the right in D:G.

Sub xfer_coords()
    Dim a As Long, r As Long, rw As Long
    With ActiveSheet
        For r = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            If .Cells(r, 1).Value = a Then
                .Cells(rw, Columns.Count).End(xlToLeft).Offset(0, 1) = _
                  .Cells(r, 2).Value
            Else
                a = .Cells(r, 1).Value
                rw = .Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Row
                .Cells(rw, 4) = a
                .Cells(rw, 5) = .Cells(r, 2).Value
            End If
        Next r
    End With
End Sub