Customer accounts advanced filtering

[markswan111]

Hello guys, need your help,

I have a very well organized functional sheet for managing my company's customers purchases and debts and credits, and I use advanced auto filtering to view a certain customer account to see there account balance (debts or credits).
however my problem is that I can't find a way where I can filter for accounts with a debt balance or in other words a balance above 0, which means they haven't paid there account yet, currently I have to place each customer's name to filter and find out if they have any debts or credits remaining.

the sheet I enter my data on is structured as basically, client name, debt, credit, and the net balance (=credit-debit), and it sums on top how much debts or credits total is the yield of all customers accounts, and I can advance filter for each customer's individual account by filtering with customers name.

I need to find a way that will enable me to filter for accounts with remaining balances (debts or credits) only , and have excel list for me the customers names with a debt balance only, along with there debt balance.

I have no problem with a set up where the results will be copied to a new separate sheet for this function.

Please kindly if anyone can help provide me of how to do so, and many thanks in advance for your great help and your great forum.

Mark Swan

[David A Coop]

Hi Mark,

It's a bit difficult without seeing your data. Can you post an example?

Do you have to use Advanced Auto Filtering? If you use the regular filter, you have a filter arrow on each column, so you can filter on Customer (say Col A) then on Debit/Credit balance.

I hope this helps, please let me know!

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Reply, then Click on Go Advanced and click on the Paper Clip OR scroll down to Manage Attachments.

If this has been of assistance, please advise.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[markswan111]

Dear Mr.David,

Many thanks for your fast reply and many thanks for this great forum, as you have requested I am including a clear sample file of my sheet.....

as you can see in my Daily sales sheet, I enter the sales data with customers names and payments (debit,credit) for each customer, on top I have cell O4 which sums the total that is owed to my company, which is in this case ($175,000), however I can't find the right filter or vba to be able to filter a list of the clients that owes this debits that totaled to the $175,000.
currently I can put the name of each client and punch the search criteria macro button and it give me the client's name and remaining balance.

however how can I develope a filter to be able get a list of customers with debts only and exclude the 0 balance customers with a push of a button ?
or maybe get a list of all customers with there remaining balances even if it is 0, insteas of having to find who owes the money manually.

I am ready for any questions if anything is not clear enough, and I am including the complete sample file.

I do not mind to set up this function on a separate new sheet if it is easier, as long as I get the feed from the same daily sales sheet.

many thanks again in advance........

Mark Swan

[markswan111]

Dear David,

I have attached the file for you as you have requested, but I have not heard from you, please let me know either way so I can see help else where in case you do not have time to help me with my VBA.

Many thanks againn

Mark Swan

[markswan111]

Dear David,

I have attached the file for you as you have requested, but I have not heard from you, please let me know either way so I can see help else where in case you do not have time to help me with my VBA.

Many thanks againn

Mark Swan

[David A Coop]

Sorry for the delay, I have been away from my computer for a few days.

Try this:

Please Login or Register  to view this content.

Note: You can make a small change to your other macros to get rid of the scrolling instructions.

Please Login or Register  to view this content.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".