Macro Help: Macro for Copying Certain Values Using Certain Criteria

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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)



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.



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! :)))
 

Attachments

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top