Macro to loop through rows and strip out duplicated numbers designated by month column

Hi guys - Not sure if this is possible but I would like to create a macro that loops through rows of data and zeroizes any data that has already been captured by a a previous line.

Ive attached a file to give you an idea of the raw data Im working with and the preferable end result. Relates to projected payments. Essentially, I would like only one Year/Month column (B:Y) per ID (A) to hold data with column Z indicating when the payment occurred.

Any ideas or help you may be able to offer would be greatly appreciated.

Thanks!

Hi, DD007. Try it

Sub rtyrty()
Dim x, i&, j&, k&, n&, u&, v&, ubx&, bu As Boolean
x = Range("A1:Z" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Value
ubx = UBound(x, 2)
For n = 2 To UBound(x, 1) - 1
    u = n
    Do While x(n, 1) = x(n + 1, 1)
        n = n + 1
    Loop
    v = n
    For i = u To v
        For j = 2 To ubx - 1
            If x(i, j) <> 0 Then
                If bu Then
                    x(i, j) = 0
                Else
                    x(i, ubx) = x(1, j): bu = True
                    For k = i + 1 To v
                        x(k, j) = 0
                    Next k
                End If
            End If
        Next j
        bu = False
    Next i
    n = v
Next n
Range("A1:Z" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Value = x
End Sub

Thats awesome! Thanks Nilem. Quality work!

Hi - Thank again for your previous assistance. Wondering if I could pick your brain on a tweak and add on to the same query. How do I tweak the macro so that it uses column D as a loop reference? (Ive also added in some additional columns).

Also, ideally I would like to display 2 seperate tabs as end results. One displaying prepayment numbers in Local Currency (AQ:BS) and another tab with the same data but (AQ:BS) in GBP. The GBP tab is currently translating local ccy into GBP using v-lookups from the FX tab. Is there a more efficent macro that can do this? (20,000 rows so quite a large data tape). Please see attaced file for an idea of the relvant data and end result.

Thanks!

hi Nilem can u explain this macro so that i can also understand pls

Hi DD007. There are differences in the column headers AQ:BS on the sheets 'sample' and 'Local'(and 'GBP'). And also there are no data in the columns of FY2012 and FY2013 on a sheet 'FX'. It's not a mistake?

Good spot. Ive updated the headers in the sample tab per attached.. should be the same as the headers in Local and GBP.

FX for full year 2012 and 2013 is left blank on purpose. For the Local and GBP tabs we will have to sum the projected prepayments for each of the months corresponding to that year Eg FY2012 =Sum(AQ:BB2) rather than using a vlookup. This is for consistency so that that the monthly projections using different projected FX rates tie back to the total for years 2012 and 2013.

Thanks again.

Hi DD007. See attachment

Works like a champion! Thanks again Nilem