need help to balance my worksheet

[yang2]

Hi, each month I have this long worksheet, that has debit and credit, also daily balance. I have to manually match up the transactions (debit and credit) to see how much has been offset within the month. Can someone help me with it- some formula or macros to match the numbers?
Problems is debit and balance are not in same day, also not all reference are matching up.
Also, I would like to separate debit, credit and running balance into three different columns, will Marco work for this?

Many thanks.

[HansV]

Welcome to Eileen's Lounge!

On which fields (columns) do you want to match the transactions (apart from the +/- amount of course)?

[yang2]

Thanks for the quick reply Hans.
Not sure if I can explain it clearly. This is a running daily sales and receivable. For example: first line $17.14, it is a debit, then there is -17.14 later to offset it. When the transaction is posted close to each other, I can match up easily, but often I need to scroll down many lines to find the offset transaction (either debit or credit). I need t rely on the reference to match up. But I don't always have perfection reference match.

I also ant to split the amount volume into to columns, one is the positive number, the other NE is the negative number.

[yang2]

I guess apart from the +/- to match up, I have to make sure the match is correctly applied. If happens they are several 25 dollars debit, and 25dollas credit, I then use reference volume to check if my pair matching is correct.

[HansV]

Which column represents reference volume?

[yang2]

Column H information is the reference I us to match up each lines. For example, line 8 and line 9, both columns have 030603. That means these two transaction offset each other. Thank you.

[HansV]

See if the attached version goes in the right direction. I used formulas in columns J:L to split the data. There is a macro to populate column M with numbers that indicate which rows match.

You'll have to enable macros - the workbook is now a .xlsm.

9991 account balance the book.xlsm

[yang2]

Woo, you are so cool! Definitely the right direction. However, i am on the other side of your world. I don't even know how to enable the macro. Please advise.
I saved the file as xlsm file, still i am not able to go into the macro to see the details.

To make it simple, I am not splitting the column I into debit and credit column any more. I only want a macro to populate Column M or another column with numbers that indicate which rows match. Can you please help me with that. I want to step into the macros and copy paste the macro and paste into a new worksheet each month? so i can run the macro. at the end of each month, i will have all data, then it will have more lines to match/offset.

this is first time, i use this lounge. so impressed. Many thanks expert Hans.

[yang2]

Hans, i got the marco enabled. Now it worked. I got it worked to fit my need. I think I am good for now.

[HansV]

The thread need help to balance a long spreadsheet in the Windows Secrets Lounge is related to this thread. A different approach was suggested there.

[yang2]

Yes, that's me too. I posted same question into that site as well. I was given these two sites years ago by one of my instructors, so first time i asked question, i posted into both. So impressived that both sites have so many active members who are incredibally knowledgable, and helpful. Thank you.

[HansV]

You're very welcome!