Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 91
  • Last Modified:

I need to updated the following formula

I need to update the following array formula:

{=IF(H250="M",H10,IFERROR(TEXT(MIN(IF($W$10:$W$200=W10,LEFT($H$10:$H$200,FIND(" ",$H$10:$H$200&" ")-1)+0)),"m/d/yy")&" (E)",""))}

I am not sure if this can be done...

I need the formula to essentially work the same way....it's returning the same date in column H (the earliest date), if it finds the same name info in column W. That is, if there is no M in H250...

So here is what I need. If there is an M in H250, I need it to return the date that is in Column H that has an "M" associated with it, for all the same name info in column W.

Please see the attached example
0
wrt1mea
Asked:
wrt1mea
  • 11
  • 8
1 Solution
 
Rgonzo1971Commented:
No example
0
 
wrt1meaAuthor Commented:
Crap. I just office. Be back in 1.5 hrs
0
 
wrt1meaAuthor Commented:
Please see the attached!!!!!!!!!
C--Users-twynn-Desktop-ExExch-EXP-EXCH-8
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
wrt1meaAuthor Commented:
Hmmmm.....file is not attaching correctly. Let me try another computer.
0
 
wrt1meaAuthor Commented:
Trying to attach file....
EXP-EXCH-8-18-14.xlsx
0
 
Glenn RayExcel VBA DeveloperCommented:
Okay, the original formula was returning the earliest completion date for a given set of values in the "Name Info" column (W).  In your example, the earliest date is 9/4/14; why should it return anything else?    The "M" flag overrides that and returns the actual completion date only.
0
 
wrt1meaAuthor Commented:
Yes, that is correct....Why should it return anything else, because I need it to return the date that is being displayed for any date that has an "M" with same name places. M=Manual, fyi, and its a date that I sometimes have to manually enter.
0
 
Glenn RayExcel VBA DeveloperCommented:
So, the "earliest" completion date no longer has any meaning then?  It now sounds like the "first occurring" completion date is the one of significance.

What would be the case if the data in column H would have been in this order?
H24: 10/12/14 (E)
H25: 9/23/14 (E)
H26: 9/4/14 (E)

Would 10/12/14 (E) now be the desired result for all three values in column G (assuming the "M" flag is on)?

Additionally, what if the "M" flag is on for the next two values? That is, H265 & H266 = "M", which correspond to the date values in rows 25 and 26.  How would the manual overrride work in those cases?
0
 
wrt1meaAuthor Commented:
Getting closer!

In this example, 9/23/14 (E) would be the desired result for H24, H25, & H26 because it does have a M and the Name info is the same.
0
 
Glenn RayExcel VBA DeveloperCommented:
So, you're stating that:
If any datevalue has a corresponding "M" flag in the section below, ALL dates for the same "Name Info" place group will be overridden with that date value?

Again, what happens if two or more dates - in the same group - have an "M" flag?  This would be a conflict.

-Glenn
0
 
wrt1meaAuthor Commented:
Yes, that is what I am saying.

In theory, it should not happen with two or more dates in the same group have an "M" flag. If they do / did, I would need the earliest of the two...
0
 
wrt1meaAuthor Commented:
Any available help...???
0
 
Glenn RayExcel VBA DeveloperCommented:
I'm still trying to figure out how to resolve the possibility of two or more "M" values existing in the lower section.  Theory is great, but in the real world, stuff like this happens.
0
 
wrt1meaAuthor Commented:
Haha...I know what you are saying.

Thanks for keeping an eye on it. I really appreciate it.
0
 
Glenn RayExcel VBA DeveloperCommented:
I have a solution, but it needs a helper column added to combine the Area Names and Manual override flag ("M") to help solve.

If the helper column is in column X (to the right of the Area Names in W), then the new Comp Date array formula ([Ctrl]+[Shift]+[Enter]) is:
=IF(SUMPRODUCT(--(RIGHT($X$10:$X$200,1)="M"),--(LEFT(X10,FIND("-",X10)-1)=$W$10:$W$200))>0,OFFSET($G$9,MATCH(W10&"-M",$X$10:$X$200,0),),IFERROR(TEXT(MIN(IF($W$10:$W$200=W10,LEFT($G$10:$G$200,FIND(" ",$G$10:$G$200&" ")-1)+0)),"m/d/yy")&" (E)",""))

This also accounts for the possibility of more than one manual flag entered for a given area group; the first flag determines the date for the entire group.

Example file attached.

-Glenn
EE-EXP-EXCH-7-29-14-updated.xlsx
0
 
Glenn RayExcel VBA DeveloperCommented:
Hi,

Did you have a chance to review and or test my solution?  If so, and it will work for you, can you please properly close this question by clicking the "Accept this solution" link above my submission above that answers your question?.  This will help ensure that future searches are meaningful to other EE members.

Otherwise, let us know if you have any other issues.

Thanks,
 -Glenn
0
 
wrt1meaAuthor Commented:
Hey Glenn....I am very sorry. I went on vacation and in the hustle of trying to leave the office, I forgot to review your answer. If I make it back to the office today, I will review. Definitely by tomorrow.

Sorry for the delay as I always appreciate help!
0
 
Glenn RayExcel VBA DeveloperCommented:
Thanks; just following up on some loose threads. :-)
0
 
wrt1meaAuthor Commented:
Works Great!!!

Sorry foe the delay!
0
 
Glenn RayExcel VBA DeveloperCommented:
Glad I could help.  It was tricky.

-Glenn
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now