Finding matching positive and negative numbers

[Rambu]

I want to match the data of positive and negative and sort out the residual value on the basis of reference Id's.For e.g
Reference ID and Amount
4567 -$10
4567 $5
4567 $2
4567 $3
4567 $5
6754 $3
6754 $4
6754 -$12
6754 $6
6754 $2

Above two reference ID after calculating in 4567 the residual amount is 5 and
simultaneously in reference ID 6754 residual is 3.So is there any solution that values settingoff to zero should get highlighted or which are not getting offset amount highlighted on the basis of reference ID. Not only the same value offset also by the breakup amount adding up setoff as above example.

 

[Joe4]

Welcome to the Board!

Have you tried the SUMIF function, where you can sum all of the values that meet a certain criteria, i.e. sum all of the values in column B where column A is equal to 4567.
See: MS Excel: How to use the SUMIF Function (WS)

 

[Rambu]

Welcome to the Board!

Have you tried the SUMIF function, where you can sum all of the values that meet a certain criteria, i.e. sum all of the values in column B where column A is equal to 4567.
See: MS Excel: How to use the SUMIF Function (WS)

4567 is a reference number my friend and I want the particular cells to be highlighted or which not getting settled.

 

[Joe4]

Sorry, I misunderstood that. That is a bit more complex.
If there is only one value per Reference number that is not getting settled, I can come up with a way to do that.

Let's say that your headers are in row 1, and your data is in the range A2:B11.
Then, you could select the range A2:B11, go to the Conditional Formatting menu, select "New Rule", select the "User a formula to determine which cells to format" option, and enter in the following formula:

=AND($B2=SUMIF($A$2:$A$11,$A2,$B$2:$B$11),COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$C2)=1)

and then choose your desired formatting option.

Then it would highlight the outlier, like this:

Keep in mind, as I said, this only works if there is only one outlier per Reference ID. If there may be more than one, I do not know how to do that (that would make things a LOT more complex).

 

[Rambu]

Sorry, I misunderstood that. That is a bit more complex.
If there is only one value per Reference number that is not getting settled, I can come up with a way to do that.

Let's say that your headers are in row 1, and your data is in the range A2:B11.
Then, you could select the range A2:B11, go to the Conditional Formatting menu, select "New Rule", select the "User a formula to determine which cells to format" option, and enter in the following formula:

=AND($B2=SUMIF($A$2:$A$11,$A2,$B$2:$B$11),COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$C2)=1)

and then choose your desired formatting option.

Then it would highlight the outlier, like this:
View attachment 68889

Keep in mind, as I said, this only works if there is only one outlier per Reference ID. If there may be more than one, I do not know how to do that (that would make things a LOT more complex).

It's not working. My data have 5000 line items. Please me out

 

[Joe4]

It's not working. My data have 5000 line items. Please me out

Does it work on the data sample you posted for us?
It should. If it doesn't, then you probably have not applied it correctly (may need to adjust the formula if your range is different).

If you are able to get it to work on your sample, but not on your real data, there are a few things to check:
- Have you adjusted the last row number in the formula to reflect your actual row end? (it will not be 11!)
- Also note what I said in my previous post:

Keep in mind, as I said, this only works if there is only one outlier per Reference ID.

 

[Rambu]

Does it work on the data sample you posted for us?
It should. If it doesn't, then you probably have not applied it correctly (may need to adjust the formula if your range is different).

If you are able to get it to work on your sample, but not on your real data, there are a few things to check:
- Have you adjusted the last row number in the formula to reflect your actual row end? (it will not be 11!)
- Also note what I said in my previous post:

Thanks it worked in sample one, results not coming due to formula provided there in $C2 is mentioned it ,in last it will be $B2 . But I'm in my sheet due to large amount of data correct figures are coming. Any other ways ?

 

[Rambu]

Thanks it worked in sample one, results not coming due to formula provided there in $C2 is mentioned it ,in last it will be $B2 . But I'm in my sheet due to large amount of data correct figures are coming. Any other ways ?

I have same data like mentioned in sample but the data line item are more in my sheet.

 

[Joe4]

If it works on the sample data, then that means the logic should be good, and it should probably work on your larger data.

Sometimes people "oversimplify" their questions when posting them here.
That is generally a bad idea (unless you are REALLY comfortable table the reply and modifying it for your actual data structure).
You should post your actual data layout/structure "as-is", and then the answer we provide should work for your actual data.

What is the real structure of your data? What is the exact range that data resides in?
What is the exact formula you are using (after your modifications)?

 

[Joe4]

It looks like have made a few attempts, where you are you quoting my last post, but not adding in any replies/answers.
Can you answers the questions I asked?
I can only help you modify it if I know your actual data layout.