Match positive and negative values withing subgroups

  • Hi,


    I have a sheet which contains credits and debits for several accounts:


    [TABLE="class: cms_table"]

    [tr]


    [td]

    Customer

    [/td]


    [td]

    Amount

    [/td]


    [/tr]


    [tr]


    [td]

    ABC

    [/td]


    [td]

    2000

    [/td]


    [/tr]


    [tr]


    [td]

    XYZ

    [/td]


    [td]

    3000

    [/td]


    [/tr]


    [tr]


    [td]

    XYZ

    [/td]


    [td]

    1000

    [/td]


    [/tr]


    [tr]


    [td]

    ABC

    [/td]


    [td]

    1000

    [/td]


    [/tr]


    [tr]


    [td]

    ABC

    [/td]


    [td]

    -5000

    [/td]


    [/tr]


    [tr]


    [td]

    XYZ

    [/td]


    [td]

    1000

    [/td]


    [/tr]


    [/TABLE]



    What I need to do is match negative and positive values within each account and highlight them. I have been able to put together a VBA code from google and this forums to match within all rows.



    I need to adapt the code so as to do matching within accounts. Example within ABC and XYZ separately.There will be 100s of accounts.


    Please help!!

  • Re: Match positive and negative values withing subgroups


    Apologies Stephen, for not being clear. I just figured that this will work. Putting the formula in a new column marks the ones that need to be highlighted. Somehow, It is not working when I enter the formula in conditional formatting.


    However, I would love a VBA solution for two reasons:


    1. I have changed my code to highlight using different color each time. This will help because I am looking at a set of over 25,000 rows and many highlights will cause confusion.


    2. In the next step, I wish to offset several credits against a single debit. In the example, ABC -5000 would offset ABC 1000 and ABC 2000 both. Not bothered about the balance.

  • Re: Match positive and negative values withing subgroups


    Stephen, there seems to be a small problem with the formula you mentioned. It highlights multiple debits against a single credit.
    e.g. it highlights all ABC 1000 against a single ABC -1000.

  • Re: Match positive and negative values withing subgroups


    Quote from StephenR;710736

    Yes, it would. I think on reflection I would use VBA for this. You need someone much better than me at formulae if you want that approach!


    Can you post a sample workbook and I will try some code?


    Apologies for the late response. My company network blocks uploading any files so I have pasted a sample.


    [TABLE="width: 188"]

    [tr]


    [td]

    Diwan Furnishings

    [/td]


    [TD="align: right"]235900[/TD]

    [/tr]


    [tr]


    [td]

    VIP Industries

    [/td]


    [TD="align: right"]18000[/TD]

    [/tr]


    [tr]


    [td]

    Diwan Furnishings

    [/td]


    [TD="align: right"]11500[/TD]

    [/tr]


    [tr]


    [td]

    Diwan Furnishings

    [/td]


    [TD="align: right"]-11500[/TD]

    [/tr]


    [tr]


    [td]

    Okaya Batteries

    [/td]


    [TD="align: right"]43700[/TD]

    [/tr]


    [tr]


    [td]

    Diwan Furnishings

    [/td]


    [TD="align: right"]11500[/TD]

    [/tr]


    [tr]


    [td]

    VIP Industries

    [/td]


    [TD="align: right"]-18000[/TD]

    [/tr]


    [tr]


    [td]

    VIP Industries

    [/td]


    [TD="align: right"]18000[/TD]

    [/tr]


    [/TABLE]


    Thanks for the help.

  • Re: Match positive and negative values withing subgroups


    I did a mock up and this appeared to work:

    Can you download files?