Need Macro Excel Net to Zero

buDDHA - Aug 27, 2010 at 08:39 AM
 Ray - Aug 27, 2010 at 05:39 PM
Good Morning All,

I would love to hear from all the experts on this forum. I have a set of data that consists of many rows and columns. All the transaction amounts are in one column. I was wondering if there is a macro that, finds ALL the numbers that nets out to ZERO and highlight them for me.

For example:

8/1/2010 Invoice Paid ($100)
8/2/2010 A/C Payable $100
9/2/2010 Check Bounced ($25)
9/3/2010 A/C Payable $100
9/4/2010 Invoice Paid ($60)
9/5/2010 Invoice Paid ($50)
9/6/2010 A/C Payable $60

In this case, the $60 and the $100 will be on highlighted. So in this case, I can look at what invoices have been paid and what hasn't been paid.

Thank You so much!

3 responses

You have TWO $100 values in your list. Which of these should match to the ($100)? Are all of these amounts for the same account? Even if they are, without something to definatively tie these records together, ie. Invoice# then I think this is a lost cause.
I just want the number to be highlighted if they net to zero. is that possible?
Some thoughts:

What it should do: Find first negative value, go down the list to find a positive match or matches, highlight all matches. Repeat for the next negative number in the list.
Should the search ignore those values that have been previously matched?

Should a different color be used for each match found?
If there are a lot of matches using the same color for all matches could just make the list the same but in color!

How is the search going to be done, what should it look for?
Search for one set of matches based on user input?
i.e. User enters -100, macro finds all records that equal 100
or i.e. Users enters -100 and macros finds all records that sum to 100

Its more complicated than imagine.