How to determine all invoices with the same ID that equal to 0

  • Hi,


    I have thousands of invoices and some of the invoices are offsetting invoices equaling to 0. Is there is a VBA code or any other way we can determine or highlight all invoices with the same Booking ID equaling to 0?


    Attached are some example of invoices I have.

    I used =If(A2=A1,"",sumif(A:A,A2,C:C)) but this only works if all invoices within the booking equal to 0.


    In the attached example: The formula worked on the below example because the total of all invoices is 0.


    However, it didn't work on the others like the examples below because Not all invoices are equal to 0:


    I tried the solver but it doesn't seem to work on my end. I am not very good at coding and stuff and I would appreciate any help.


    Thank you in advance.

  • Hi Grace,


    You need to ensure you follow the rules here, so let them know you have the thread on the other forum:

    https://superuser.com/question…=1#comment2546142_1656880


    I will have another look at the code tomorrow, I ran it but it is not working on your sheet as it did on the example I put together from the pictures you posted. Not sure why.


    Anyone else interested, this is the code that was working to automate solver:


    As indicated in the post on the other forum, I have activated the solver addin and ticked the solver reference (this does not work otherwise).

  • I had made a mistake in the above, and I could not get the code to work correctly in the workbook you uploaded for some reason - I think it was the filter you had used.


    If you open this book and press the button on sheet 2 it should give the results I outlined on the other forum.


    If it is working correctly we can work on cleaning up the output formatting to meet needs, as it is the code is not very flexible. If it fails to run entirely it meansyou need to switch on the reference for solver in the visual basic developer window. ALT-F11 should open it and then TOOLS, then REFERENCES and look for SOLVER and make sure it is checked.


    NB: The code in this workbook has a minor change to the previous post on line 68.


    solver.xlsm

  • Hi Justin,


    Thank you so very much! It is actually working now! I can't thank you enough. You've saved me hours and hours of manual work.

  • Hi Grace,

    I have updated the original StackExchange thread with the final code and a link to here. If you could please head there and mark it as solved. Also if you can click on like fo the solution here and check the resolved checkbox (don't worry if you cannot find it) on this page.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!