Returning highest milestone for ID with INDEX?

Please update your profile to show your Excel version, as xx doesn't tell us anything helpful!!

Maybe this:


="MS"&MAX(IF($C$4:$C$8=G4,RIGHT($D$4:$D$8,1)+0))

IF you are using Excel 365, enter is required. However, if you are using an earlier version, you will need to enter it as an array formula.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

In D, you may also need ot use ; instead of ,

I'll look at this again in the morning. Beer time now. However, do take a moment to update your profile to show O365 as your current version.

AND... post an Excel sheet to SHOW me what you mean.

Also... if your MS numbers can become greater than 9... (i.e. 2+ digits) include those in your revised sample.

I suspect that this STILL is incorrect... as you did not explain the significance of MS10, MS11, etc....


="MS"&MIN(IFERROR(AGGREGATE(14,6,SUBSTITUTE($D$4:$D$11,"MS","")+0/($C$4:$C$11=$G4),{1,2}),10^10))

If this is incorrect, please update your sheet with a FULLY representative sample.

Mmm. One more go...

=LET(a,SUBSTITUTE($D$4:$D$11,"MS",""),b,($C$4:$C$11=$G4),"MS"&MAX(IFERROR(AGGREGATE(IF(AGGREGATE(14,6,a/b,1)=9,15,14),6,a/b,{1,2}),1/10^10)))

Microsoft version incompatability! Your version of Excel 365 obviously doesn't support LET. I am away from my PC for about an hour. I'll fix it then.

According to Microsoft, LET is available in Excel for Microsoft 365, Excel for Microsoft 365 for Mac & Excel for the web. No idea why it's not working for you!!

=IF(G4="","","MS"&MAX(IFERROR(AGGREGATE(IF(AGGREGATE(14,6,SUBSTITUTE($D$4:$D$11,"MS","")/($C$4:$C$11=$G4),1)=9,15,14),6,SUBSTITUTE($D$4:$D$11,"MS","")/($C$4:$C$11=$G4),{1,2}),1/10^10)))

Providing the "rules" do not change and that 9 is the only "problematic" MS value... you can add 5 & 6 in without any further changes.

I make my own beer (to Rheinheitsgebot standards... of course), so there's never a shortage here...

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

Awesome, are you selling your own beer as well?

What if I would like to add a Milestone that is called "No Onboarding" (Text Only), which shall be treated silimar to how we treat MS9.

Is there a way to include it?

And for some reason, when I transfer the Formula into my List, I get the value "MS0,0000000001" --> EDIT: this should usually only happen, if there are values for an ID, that do not contain "MS", right?

It seems that there is a bit of "mission creep" going on now. Please upload a FULLY representative sample along with expected results.

I'm starting to lose myself here... so this really needs to be 100%!!


=IF(G4="","","MS"&MAX(IFERROR(AGGREGATE(IF(AGGREGATE(14,6,SUBSTITUTE(SUBSTITUTE($D$4:$D$11,"MS",""),"No Onboarding",1/10^10)/($C$4:$C$11=$G4),1)=9,15,14),6,SUBSTITUTE(SUBSTITUTE($D$4:$D$11,"MS",""),"No Onboarding",1/10^10)/($C$4:$C$11=$G4),{1,2}),1/10^10)))

Not sure what you mean... but make sure this is included:

=IF(G4="","", at the start.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

Can you check if the following functions are available on your version of Office 365:

TEXTJOIN
SORT
FILTERXML

If you're using a Mac... forget it, just tell me you're a Mac user.

One more query... Please DOUBLE check that if you have LET in your Excel version

Bummer. This is a nightmare formula, but it seems to do what's needed.

Scrap that. This looks much better...


=IFERROR("MS"&SUBSTITUTE(LARGE(IFERROR(LARGE(FILTERXML("<A><B>"&TEXTJOIN("</B><B>",TRUE,FILTER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($D$4:$D$14,"MS9","MS0.1"),"MS",""),"No Onboarding",""),$C$4:$C$14=G4))&"</B></A>","//B[.*0=0]"),{1,2}),""),1),0.1,9),"")

or (slightly shorter):

=IFERROR("MS"&SUBSTITUTE(LARGE(IFERROR(LARGE(FILTERXML("<A><B>"&TEXTJOIN("</B><B>",TRUE,FILTER(SUBSTITUTE(SUBSTITUTE($D$4:$D$14,"MS9","MS0.1"),"MS",""),$C$4:$C$14=G4))&"</B></A>","//B[.*0=0]"),{1,2}),""),1),0.1,9),"")

If the one at Post 30 is OK... use it. It'll be much gentler on your PC. If you have any issues, shout. However, for now...

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

I woke at 04:00 today and started to think about this one... again. Unusually, I remembered what I was thinking when I woke again a few minutes ago!!

FINAL iteration:

=SUBSTITUTE(IFERROR("MS"&MAX(FILTERXML("<A><B>"&TEXTJOIN("</B><B>",TRUE,FILTER(SUBSTITUTE(SUBSTITUTE($D$4:$D$14,"MS9","MS0.1"),"MS",""),$C$4:$C$14=G4))&"</B></A>","//B[.*0=0]")),""),0.1,9)

Away from PC. This is a bit of a nightmare!! One quick check... are calculation options set to manual? Did it work OK on MY sheet when you changed things?

It doesn't help... but might have explained... Back at PC soon.

OK. So, it works PERFECTLY for me. When you open MY file... do you see everything in GERMAN or in ENGLISH?

Copy/paste the formula that YOU see in H4 of MY file at Post 33. I am wondering if you need a little language-related tweak in the XPath bit of the formula, namely: "//B[.*0=0]"

Then do the same for the same cell in H4 of THIS file. The only difference is that I used a slightly different construction for the Xpath.

Also... use formulas/evaluate formula and tell me at what stage it breaks down. EXACYLY what does it say on the click BEFORE an error message is shown.

Please double-check your file. Are you sure you have described it correctly (wrong row???)

Late to the party again.

This in J4 (of attached) and filled down.

Formula:

Please Login or Register  to view this content.

Most strange behaviour!!

=IFERROR("MS"&SUBSTITUTE(MAX(FILTERXML("<A><B>"&TEXTJOIN("</B><B>",TRUE,FILTER(SUBSTITUTE(SUBSTITUTE($D$4:$D$14,"MS9","MS0.1"),"MS",""),$C$4:$C$14=G4))&"</B></A>","//B[number()=.]")),0.1,9),"")

Go with FR's. I can't understand what's happening!!

OK

I think I got it right this time.

Try this one. As long as the Milestone figures are always prefixed "MS" this will work.

Formula:

Please Login or Register  to view this content.

I still see the correct answer in the purple cells in your sheet. Incidentally, you ignored my Qs back ay Post 38...

OK I think I understand.

Formula:

Please Login or Register  to view this content.

	C	D	E	F	G	H	I
1	General Table				Dashboard
2
3	Solution-ID	Milestone			Solution-ID
4	1	MS4			1	MS4
5	2	MS2			2	MS2
6	2	MS9			3	MS9	<- should be MS9
7	3	MS9			4	MS4
8	3	MS9			5
9	3	No Onboarding			6	MS9	<- should be MS9
10	3	No Onboarding			7
11	4	MS4
12	5	No Onboarding
13	6	MS9
14	6	MS9

I think I may have fixed it!! However, I do not have the first idea why it made a difference!!!

=SUBSTITUTE(IFERROR("MS"&MAX(FILTERXML("<A><B>"&TEXTJOIN("</B><B>",TRUE,FILTER(SUBSTITUTE(SUBSTITUTE($D$4:$D$14,"MS9","MS0.1"),"MS",""),$C$4:$C$14=G4))&"</B></A>","//B[number()=.]")),"")&"",0.1,9)

Selecting Attachment 739638 results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator".

This proposal employs a helper column (E) which may be moved and/or hidden for aesthetic purposes.
The helper column is populated using: =IFERROR(AGGREGATE(14,6,(RIGHT(D$4:D$15)+0)/((RIGHT(D$4:D$15)+0)<=6)/(C$4:C$15=C4),1),AGGREGATE(15,6,RIGHT(D$4:D7)+0/(C$4:C$15=C4),1))
The output is produced using: =IFERROR("MS"&INDEX(E$4:E$15,MATCH(G4,C$4:C$15,0)),"")
Let us know if you have any questions.

JeteMc... can I ask you t look at the file at Post 53. Does it work OK for you?? It's fine here, but seems to fall over on the OP's system.

Glen, I can see the outputs of the formulas, which are correct, when I open the file and even when I select "Enable Editing". However, when I make any change, such as changing D4 from MS4 to MS3 all of the outputs disappear. I am using the 2019 version which does not support FILTERXML nor FILTER. As codingflix is using the 365 version, my issue may not be codingflix's.

OK. Thanks. I'll pester someone else with 365...

You're Welcome. I was glad to take a look and hope that the issue can be sorted. I hope that you have a blessed day.