need help to balance a long spreadsheet

Topic

New Reply

On the spreadsheet, I need to march the debit and credit throughout the month. I am now manually to compare each line data. The debit and credit may happen in different days. Also, I want to create a macro to split the debit and credit into two columns, also have a running balance for each day. Please see attached file.

Thank you for your help.

Reply | Quote

Replies

You should always provide an example of what you want
By which column(s) do you want to match for the debit/credit
Several ways to do this.

Reply | Quote

You should always provide an example of what you want
By which column(s) do you want to match for the debit/credit
Several ways to do this.

Sorry, this is my first post. I was not clear. I want column I to match +/-.
You can find the reference information on column H. E.g. line 8&9, the same reference is 03603, so I know these two transactions offset each other. But not Ll lines are close to each other, I have to scroll down the entire page to search. I wonder if there is easy way to match up two numbers.
Many thanks.

Reply | Quote

Dear Yangyang

Welcome to the lounge.

Everyone want to help and I know excel can do this, but the data provided is very difficult to understand. What is “Summarization Record”? These entires appear to cancel out other records. See amounts recorded on 9/15 for minus 249.71 and minus 513.00 and the summarization record on 9/17 of positive 762.71 they total to zero but have no common reference in Col H or any other columns. In other words without a common reference Excel could never match the 3 amounts.

Can better explain what needs to be matched?

Regards,

TomD

Reply | Quote

Hi Tom, thanks for the response. Column H is the column I use to find common reference. Summarization record is the amount that could balance several line items. I don’t expect all lines are matching up, but just want to have a macro which does the most matching. thank you.

Reply | Quote

YangYangLi,

Here’s a possible approach.
1. Setup an Advanced Filter to select Unique Vendor References. Use Range Names for Database, Criteria, & Extract
37933-AdvFilter
2. Create Dynamic RangeNames for Vendor Reference and Amount columns (one each)
3. Use a SumIf to total the transactions by Vendor Reference.
37934-AdvFilterSumIf

Test File: 37932-Answer-9991-account-balance-the-book

Look at the Defined Range Names on the Formula Tab to see how it’s done.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

yang,

You can do this easily with a pivot table. See the revised spreadsheet. I split your debits with the formula, =IF(I2>0,I2,0) and your credits with the formula =IF(I2<0,I2,0) in columns J and K respectively. Then copy down. Currently, your credits and debits are off by $60,000.00+ and you need to complete your postings. When updated, you should be all set.

HTH,
Maud

37936-pivot-table

Reply | Quote

thank you. The pivot table is helpful.

Reply | Quote

When you say you need to match what do you do when you match. Do you keep the data or if it cancels out to 0, delete all pertinent entries.
row 2 -34.56
row 22 +10
row 42 +24.56
or ??

Daily total should take into account these deletions and adjust accordingly.

Reply | Quote

When you say you need to match what do you do when you match. Do you keep the data or if it cancels out to 0, delete all pertinent entries.
row 2 -34.56
row 22 +10
row 42 +24.56
or ??

Daily total should take into account these deletions and adjust accordingly.

I still need to keep all the data that match up. I found some other help from another site. but all are helpful. I am learning a lot more through you guys. thank you very much for your follow up.

Reply | Quote