Excel 2010: Index, Match and Continue Subtract Values until - Microsoft Community

-

i have data below need compare 2 columns (a , c), find exact match using columns , c (i.e. "string") , when matches found take string1 value (column b) , continue subtract string (column d) until matches found (that example, string1 "b1-d1, b1-d2, b1-d3"). further in case if (b1=d1) should called "false".

any calculation match appreciated dealing 5000 entries find if (b1=d1, b1>d1 or b1<d1).

a         b       c         d             string1   500    string1    300  string2   1000   string1    400  string3   1500   string1    600                   string2    750                   string2    845                   string2    1350                   string3    1200                   string3    1400                   string3    1700                    string3    1775

***changed office product moderator***

(that example, string1 "b1-d1, b1-d2, b1-d3").


further in case if (b1=d1) should called "false".

i'm not sure mean "called false", see formula g1.

f1:  =b1-sumif($c$1:$c$5000,a1,$d$1:$d$5000)
g1:  {=iferror(match(a1&b1,$c$1:$c$5000&$d$1:$d$5000,0)>0,false())}

drag formulas down till have data in column a:b

andreas.



Office / Excel / Windows other / Office 2010



Comments

Post a Comment

Popular posts from this blog

Getting ErrorCode: 120018 when trying to access Microsoft account - Microsoft Community

-
split thread. hi, we 've got user exact same issue: "ce mot de passe été utilisé trop souvent dans le passé. choisissez une chaîne plus difficile à deviner. (which means: " we've seen password many times before. choose harder guess. ") errorcode: 120018 correlationid: 4ab3a486-a11a-4ead-983c-c1c391c3e7d5" it happens when tries update password "login.microsoftonline.com" portal. we tested more 5 different new passwords (passwords never seen before). office 365 synchronized ad; user's account not locked or whatsoever, account set able change password cannot reset it. thanks in advance. ***post moved moderator appropriate forum category.*** hi all,   thanks replies.   generally, password length greater 10 characters. understand users may want have longer passwords. please submit feedback improve our products , services. understandi
Read more

The message was sent to a distribution list ‎(DL)‎ - Microsoft Community

-
hi all, i posting question have faced issue last week, migration done exchange 2007 o365. there 1 distribution list in on prem & in o365 group (synced aadc). 1 migrated user send email dl before & after migration, strange thing that, before migration ok, after migration user got message as " delivery has failed these recipients or distribution lists: *** email address removed privacy ***   this message has many recipients. microsoft exchange not try redeliver message you. please try resending fewer recipients, or provide following diagnostic text system administrator". *** email address removed privacy *** #550 5.5.3 resolver.adr.reciplimit; many recipients ## i have checked same message id in o365 message trace & found following... 8/16/2017 11:17:55 am receive message received by: db4pr05mb397 message received by: db4pr05mb397 8/16/2017 11:17:56 am expand dl the message sent distrib
Read more

Exchange Mail Defer Reason: LED=450 4.4.318 Connection was - Microsoft Community

-
split thread. hello bertel, kindly note that we have used hybrid configuration wizard (hcw) exchange 2013 office 365, , completed. this wizard created send/receive connectors, default didn't touch them , didn't edit configuration. the outbound send connector office 365 on premise server, forward emails our real ip. and on error mentioned ip: "reason: [{led=450 4.4.318 connection closed abruptly ‎(suspiciousremoteservererror)‎};{msg=};{fqdn=our fqdn};{ip=our real ip};{lrt=7/11/2017 2:47:21 pm}]. outboundproxytargetip: 205.177.180.161. outboundproxytargethostname: our fqdn" i guess traffic forwarded office 365 our firewalls refused here on our enviroment. my question is: rules or policies should applied on firewall sides ? opened 25 traffic no luck. please advise, thanks in advance, hassan al kak hi hassan, office 365 ip lists may changed , please have check, make
Read more