negative value

[Radhakant Panigrahi]

Hi,

I have below data with me, i have 4 columns where the "no of days" for
Employee ID (under a particular manager ID) is coming with -ve value as well
as with +ve value.

The "no of days" are automatically coming whether it is -ve or +ve comes in
the report in column "D".

I want a formula in column "E" where it will find for me the Emp ID having
+ve value that is offsetting with the same Emp ID having the -ve value

Manager Empl ID no of days -ve value comment
ID
454 12121 5 No
454 12121 15 No
454 12121 -5 Yes
454 12121 4 No
454 12121 -4 Yes
454 12121 10 No
878 45464 14 No
878 45464 -2 Yes
878 45464 2 No


Regards,
rkp

 

[JLatham]

Put this formula into cell E2 and fill it down. Adjust the ranges as needed,
I've just shown from row 2 to row 10, and I'm sure your list is longer than
just 10 entries.

=IF(C2<0,"Matching +ve is at: " &
ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2),"")

 

[JLatham]

To work properly when there is no matching +ve entry in the list, use this
formula instead:

=IF(C2<0,"Matching +ve is at: " &
IF(ISERR(ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"no
match
yet",ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"")

Once more, adjust the ranges involved to match the length of your data
columns.

 

[Radhakant Panigrahi]

Hello JLatham,

i tried the both the formullae, but did not worked...if i look into the data
i need to find the below IMP ID in column "E" where i have written "Offset"
where as your formula giving comment against the -ve value... i want to
identify the +value of IMP ID that is offsetting the -ve value of the same
EMP ID

Manager IDEMP ID No of days '-ve value' Comments Comments
454 12121 5 No Offset
454 12121 15 No
454 12121 -5 Yes Matching +ve is at: $B$2
454 12121 4 No Offset
454 12121 -4 Yes Matching +ve is at: $B$5
454 12121 10 No
878 45464 14 No
878 45464 -2 Yes Matching +ve is at: $B$10
878 45464 2 No Offset

Regards,
rkp

 

[JLatham]

The second formula worked very well for me with your sample data; there may
be some small thing keeping it from working at your end.
If you can, send me a copy of your workbook as an email attachment to
(remove spaces)
Help From @JLatham Site. com
and I will attempt to set the formula up to work properly in your workbook.

 

[JLatham]

I believe I understand better now. Try this formula instead of the other one
and let me know if this does what you need it to do. As before, this formula
would go into a cell on Row 2 and you need to change the range references to
match your data.

=IF(ISERR(ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"",IF(C2>0,"Offsetting
-ve is at: " &
IF(ISERR(ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"",ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)) & " for " & B2,""))

 

[JLatham]

And if you only want the word "offset" to appear when the conditions are met,
then:

=IF(ISERR(ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"",IF(C2>0,IF(ISERR(ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"","OFFSET"),""))