negative value
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(C20,IF(ISERR(ADDRE SS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"","OFFSET"),""))
"Radhakant Panigrahi" wrote:
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" wrote:
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" wrote:
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
|