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.
need help to balance my worksheet
-
- NewLounger
- Posts: 7
- Joined: 18 Sep 2014, 11:58
need help to balance my worksheet
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: need help to balance my worksheet
Welcome to Eileen's Lounge!
On which fields (columns) do you want to match the transactions (apart from the +/- amount of course)?
On which fields (columns) do you want to match the transactions (apart from the +/- amount of course)?
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 7
- Joined: 18 Sep 2014, 11:58
Re: need help to balance my worksheet
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.
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.
-
- NewLounger
- Posts: 7
- Joined: 18 Sep 2014, 11:58
Re: need help to balance my worksheet
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.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- NewLounger
- Posts: 7
- Joined: 18 Sep 2014, 11:58
Re: need help to balance my worksheet
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.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: need help to balance my worksheet
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.
You'll have to enable macros - the workbook is now a .xlsm.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 7
- Joined: 18 Sep 2014, 11:58
Re: need help to balance my worksheet
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.
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.
-
- NewLounger
- Posts: 7
- Joined: 18 Sep 2014, 11:58
Re: need help to balance my worksheet
Hans, i got the marco enabled. Now it worked. I got it worked to fit my need. I think I am good for now.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: need help to balance my worksheet
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.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 7
- Joined: 18 Sep 2014, 11:58
Re: need help to balance my worksheet
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.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands