Matching negative and positive values in a file on the same date

[Sreeja]

Hi All,

I have a group of negative and positive numbers and out of that I just need to highlight those numbers for which the total is zero. Moreover, there could also be two or more positive numbers which equals to one single negative number and the sum of that would be zero. Hence need to identify all such numbers from the list.

1

$1,307.32

Sum of Sr# 1 & 4 = 0

2

$28.44

Sum of Sr# 2 &3 = 0

3

($28.44)



4

($1,307.32)



5

$14.22

Sum of Sr# 5 & 6 = 0

6

($14.22)



7

$14.22



8

$1,307.32

Sum of Sr# 8,9 & 10 = 0

9

$14.22



10

($1,321.54)



11

$1,307.32



12

$1,307.32



13

$1,307.32



14

$1,307.32



15

$700.00



16

$750.00



17

$550.00

Sum of Sr# 17,18 & 19 = 0

18

$57.32



19

($607.32)



20

$389.76

Sum of Sr# 20 & 21 = 0

21

($389.76)



22

$500.00

Sum of Sr# 22, 23 & 24 = 0

23

$153.66



24

($653.66)



The numbers in the bold above needs to be highlighted in any color [say green].

 

[prasaddn]

hi,

pls upload the same in excel file so that layout can be easily understood.

Regards,
Prasad DN

 

[Sreeja]

Hi,

Attached is the file.

Thanks,

Sreeja

 

[NARAYANK991]

Hi ,

This is not so simple , especially since your workbook has no dates , though your thread title mentions them.

Narayan

 

[Sreeja]

Hi,

The dates have been replaced by the serial number as to explain the summation of two values as the dates are same as mentioned in the thread title itself.

I understand it is not so simple and hence looking for an help on this. I have attached the excel file along with the dates. Hope that helps.

Thanks!

Sreeja

 

[NARAYANK991]

Hi ,

Does it mean that the matching will never happen across dates ? Will an entry always be matched with another entry having the same date ?

Narayan

 

[Sreeja]

Hi ,

Does it mean that the matching will never happen across dates ? Will an entry always be matched with another entry having the same date ?

Narayan

Hi,

Yes, the matching will never happen across dates. The matching has to be done only with the values, irrespective of the dates.

This has to be taken as if the payment is being received but with a break up interest an principal amount.

Sreeja.

 

[NARAYANK991]

Hi ,

Matching the two amounts to one payment is difficult ; can you confirm whether the 3 amounts involved will always be consecutive amounts ?

Narayan

 

[Sreeja]

Hi ,

Matching the two amounts to one payment is difficult ; can you confirm whether the 3 amounts involved will always be consecutive amounts ?

Narayan

It is not necessary that the amount will be consecutive amounts. It can be after a gap of two payments/ debits as well.

The amount has to be matched according to the credit amount available corresponding to the debit.

 

[NARAYANK991]

It is not necessary that the amount will be consecutive amounts. It can be after a gap of two payments/ debits as well.

The amount has to be matched according to the credit amount available corresponding to the debit.

Hi ,

I doubt if this can be done.

Narayan

 

[bosco_yip]

A formula system way to find the best number set to a check sum

1] In B1 select Check Sum from the dropdwon list.

2] B2 will give a total of highlighted data in range B4:B27 being the best number set to a check sum.

3] C2, indicated the result of B2 is a "Exact Match" or "Closest Match" together with matching %

4] Pressed F9 for recalculation, if B2 got a "Closest Match" in order to obtain a better result.

5] Helper Column E to Column AA can be hidden.

Regards
Bosco

 

[Sreeja]

A formula system way to find the best number set to a check sum

1] In B1 select Check Sum from the dropdwon list.

2] B2 will give a total of highlighted data in range B4:B27 being the best number set to a check sum.

3] C2, indicated the result of B2 is a "Exact Match" or "Closest Match" together with matching %

4] Pressed F9 for recalculation, if B2 got a "Closest Match" in order to obtain a better result.

5] Helper Column E to Column AA can be hidden.

Regards
Bosco

Thank you very much for your help!

Could you please confirm if the same can be done through conditional formatting as the formula is huge and we actually need to color code the matching sets of negative and positive values. Also, the values are not know to us beforehand so we will not be able to pre-input the amount as in column B1.