1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Macro Help: Macro for Copying Certain Values Using Certain Criteria

Discussion in 'Business Applications' started by manimefrancess, Jan 13, 2013.

Thread Status:
Not open for further replies.
  1. manimefrancess

    manimefrancess Thread Starter

    Joined:
    Jan 13, 2013
    Messages:
    1
    Hello!

    I've been tasked to compile in a sheet the monthly ending balances (from July to Dec) of customer receivables for 493 CUSTOMERS. That's a lot! I already started, but I know continuing until the end is crazy and even impossible. So please help?

    Below is a picture of the first sheet containing the ledger data of the 493 customers dated July to December. I just blocked the center and changed the customer names in an attempt to maintain confidentiality.

    As an example, for Customer 1, I need the July, Aug, Sept, Oct, Nov, and Dec ending BALANCES (last column) of the receivables.

    (I ALSO ATTACHED FILES OF THE FF 2 PHOTOS WHICH I UPLOADED IN FLICKR JUST IN CASE)

    [​IMG]

    For july 31, since the first entry is already 08.15 or August 15, this implies that the July balance must be 0 which I have to input into the July 31 column in the second sheet as shown in the second picture.

    For Customer 1 August 31 2012, the closest date to August 31/8.31 entry indicates a balance of 0 so this must be the August 31 balance.

    For customer 1 Sept 31 2012 to November 2012, no entries are inputted for these months (9-11) so the August 31 balance mustn't have changed and is the standing balance as of November 31.

    For Customer 1 December 31 2012, the outstanding balance is 0.

    Oh, as an added note, just in case, debit adds to the balance and credit deducts from it.

    [​IMG]

    I've had to manually mine the ending balances for 90+ customers so far. HUHUHU. I just know macros and YOU will save me. Help me please?

    I really should learn macros for excel. I will soon. It will definitely make life much easier.

    Thank you very much! :)))
     

    Attached Files:

  2. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    It will be easier if you make sheet1 like a database table.
    That is
    Column A is all customer name with no blank rows.
    Column B is the invoice date
    Debit and Credit can be in 2 col or 1 col.

    Try not to "beautify" your source data with empty rows and columns, it makes summarisation difficult.

    When all is done, you may use sumif to sum all values before a particular date.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1085025

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice