+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    04-25-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    5

    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!
    Attached Files Attached Files

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

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

    Hi, DD007. Try it
    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-25-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    5

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

    Thats awesome! Thanks Nilem. Quality work!

  4. #4
    Registered User
    Join Date
    04-21-2012
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    14

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

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

  5. #5
    Registered User
    Join Date
    04-25-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    5

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

    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!
    Attached Files Attached Files

  6. #6
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

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

    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?

  7. #7
    Registered User
    Join Date
    04-25-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    5

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

    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.
    Attached Files Attached Files

  8. #8
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

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

    Hi DD007. See attachment
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    04-25-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    5

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

    Works like a champion! Thanks again Nilem

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1