Data Parser

Can someone create a VBA routine which will take data in sheet1 and convert it into sheet2? See attached example. Row 1 in sheet1 will always contain the column headings I need to parse. The data is in row 2 through 1000's. Sheet2 shows the end-state. I can have several hundred headings in the first row of sheet1. On some 2 to n rows, there can be multiple variables with the same field name. The one farthest to the right is the one that needs to be saved, all other similar can be ignored. I'm a VBA novice and this problem is a bit out of my league.

Kind Regards,

Tony

This is how I read your problem.

Sub test()
    Dim a, b, i As Long, ii As Long, iii As Long
    Dim maxRow As Long, maxCol As Long
    a = Application.Trim(Sheets("sheet1").Cells(1).CurrentRegion.Value)
    ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
    For ii = 1 To UBound(a, 2)
        If a(1, ii) = "" Then maxCol = ii - 1: Exit For
        b(1, ii) = a(1, ii)
        For i = 2 To UBound(a, 1)
            For iii = UBound(a, 2) To 2 Step -1
                If a(i, iii - 1) = a(1, ii) Then
                    b(i, ii) = a(i, iii): Exit For
                End If
        Next iii, i
    Next
    With Sheets("sheet2").Cells(1).Resize(UBound(a, 1), maxCol)
        .CurrentRegion.ClearContents
        .Value = b
    End With
End Sub

Thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.