VBA or formula auto populate the match

    I have an excel worksheet where I have deposit posted as credit and Income posted as Debit on daily basis. I need to add a formula to get the matching results and show me whats are the outstanding values that didnt match , either its credit or Debit. It should populate the ones in a different cell to see the outstanding values. can someone please help

    The data is filled in coloum

    A ( Description)

    B( DEBIT AND CREDIT VALUES)

    c ( IS WHERE NEED THE FORMULA TO GIVE ME RESULTS ) FOR EXAMPLE MATCHED AND HIGHLIGHTED GREEN , NOT MATCHED SPIT THE VALUE THAT DIDNT MATCH


    Any suggestion how to formula or do a vba code. the total transaction line could be 500 to 1000 . please help

    Quote

    ?( FOR EXAMPLE MATCHED AND HIGHLIGHTED GREEN

    in your file nothing is green? Only "Formula" has a yellow background.

    Not looking into A there are 2 matching values and they could be matching by chance.

    So I expect that in column A I can find the info that matches. But how?


    1) what is the matching criteria

    2) fill in the expected result and may be explain why

    I am confuesd to what are you asking. this is a reconcilation that is done recived bank deposit(CR) and posted the debit on the books(DB). the reprots are too large and i can go through each item and match debit and credit

    DEBIT AND CREDIT dont always come on same days its scatterd


    example is what my report looks like , if it matches it can color green , if it didnt match it can color red. can we do something like that?

    sorry if I am confusing. I need a formaula or condition formatting that can help

    PLEASE fill in the expected result and may be explain why


    At the moment I can guess that the firstDeposit (row 2) matches the first Income (row 4) = offset of 3

    This offset is not correct anymore if something like "Withdrawl.." is included or the


    Matching of numbers is only possible if they match.


    can it be that you uploaded a different file? I cannot find "EXAMPLE MATCHED AND HIGHLIGHTED GREEN" too.

    The only I can see is that if you find a value in Debit which matches exactly a value in Credit then you colored it GREEN.


    You have different colors in Debit and Credit. What do they mean

    example: orange - Does this means they belong together but still not completely compensated?


    What I am still looking for is something like a unique key (like e.g. an order number) that shows that Debit and Credit belong together.


    I still do not know what result you expect with the formula

    the different colors than green , means it didnt match at all or its combined to multiple line items. SO If we can create a formual that syas

    match line 2 IN CELL K2 DEBIT AMOUNT 4060.54 , IN COLOUM M and see if there is a credit side of the same amount. if yes than mark green, if no than mark yello or red. and if you see there is a depsoit on the credit side of that same amount

    to find matching values is not a problem, but the rest can only be marked in another color if requird. I still see no possibility to mark unmatched items in different colors like you did. Without criteria any debit can belong to any credit.


    Proposal: If you provide a sample file then the structure should be the same as your original (including header). The best is to provide the original file with reduced and randomized confidential data and remove unnedded worksheets. Then there is a big chance that it will work without changes.


    As I can see DEBIT is Colum K and Credit is Column M (according your picture)

    you can use following formulas for conditional formatting


    select column K and add this formular for conditional formatting

    =IFERROR(MATCH( -$K1;$M:$M;0);FALSE)

    similar for column M. Select column M and add

    =IFERROR(MATCH( -$M1;$K:$K;0);FALSE)


    cheers

    thank you would it be helpfull if I sent you this file - the other not matched doesnt need to be in different colors. its can be same color like yellow Highlight .

    only matched can be green. wil lthat work. let me know if sending you this file would help. this would be a huge help for me and than kyou so much go bless you

    there were two pronblems that I have not seen

    1) I forgot to mention that I have the German layout --> ";" must be exchanged with "," for English layout

    2) In the picture I did not check taht you have no negative values because you used two columns but in your first sample file you used pos and neg values in one column.


    I changed it in your latest sample file and attached.


    one thing to be mentioned:

    If you have the same debit more often then all will be green if you find only one on Credit. So if this could happen then I could provide only a VBA solution. May be there is also a formula possible but I don't know

    If you change the background color on "not green" cells this is ok and will sustain.


    sample Book1.xls


    hope this is sufficient

    cheers

    Thank you so much sir, Is there a VBA code too we can setup this. The same amount of debit multiple times scenario you mentioned might be the case for some reconcilation that I have to do in other file. is there something you can help in sharing a quote and using this as an example file.


    the report format is the same


    I think that would be helppfull for orther files. Please let me know. And thank you for helping

    You did not give me much information. Therefore I have some assumptions

    1) your files look similar. They all have a column "Date", a column "debit" and a column "credit"

    2) you are able to copy the code into the other files


    I added code in ThisWorkbook "Workbook_SheetChange( ... )"

    Everytime something is updated in any sheet the sheet is checked on the three specific columns mentioned above.

    If the columns are found all values will be checked


    I also added a class module. This is only used to hold some values.


    Therefore if you have other files you have to copy the class (same name!) and also the "Workbook_SheetChange( .. )" to the new file.


    sample Book1.xlsm

    cheers

    you are so kind and helpful , thank you so much. Is it ok If I reach out to you for future questions, or challenges related to excel or VBA .thank you ponce again

    future questions and related- no problem. May be I will not solve it but give you some hints to do it yourself.


    books: this depends on your experience in programming in any language. But I do not think they are better or worse. The only differences are in the content but they will not be for free :)

    There is a lot of free stuff on the internet with a a lot of samples for free. For example:

    Paul Kelly

    Chris Newman

    John Michaloudis

    Mynda and Phil Treacy

    and many more. For specific problems use any search engine. You will also find a lot on Youtube.

    I know nothing is free in this world. N oexperince in Programming. But let me know the recomendation that would help me to learn.

    thank you

Participate now!

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

Register Yourself Login