Finding the largest number in a column of numbers formatted as text

[vtentarelli]

In the spreadsheet is a column headed, Project ID Numbers. The format of this number is YY-XX, where the YY = the last 2 digits of the year; XX is a consecutive number beginning with 01. This is a project management tool that assigns priority numbers and project managers. The list of projects are sorted by a Priority number that is calculated by adding the score to weighted categories. Because of this sorting, the project numbers are not in order. There are projects from 2017 that are after projects 2019. What I want to do is to have a convenience factor to let the project coordinator know what the last number used for a specific year in order to assign a new number without having to manually sort or check each number. I tried using LARGE, INDEX, MATCH, VALUE, DMAX all to no success. I keep getting either a #Value or a #Num or a message that either I have too many arguments or too few arguments for the function.

Attached is an abridged version of the spreadsheet. I have entered the date in a cell above the column and tried to match the numbers in the column to RIGHT(g5,2) with no success. I would like to have the largest project number that is already assigned for that year entered into a cell, so the data entry can give the new project the next consecutive number.

[MarvinP]

Hi vtentarelli,

This formula seems to do what I think you want:

=MAX((LEFT($B$7:$B$75,2)=RIGHT($D$4,2))*(--RIGHT($B$7:$B$75,2)))

It is an Array formula and needs to be entered with a Control+Shift+Enter keystroke.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Look at the formula in this attached file.
Array Formul using parts of stings and Max.xlsx

[FlameRetired]

Although you didn't ask you might also consider replacing the formula in column E with (non-array entered)

Please Login or Register  to view this content.

[vtentarelli]

Thank you for the quick and simple reply. There was a bit of a delay in my response because I discovered another problem with the spreadsheet and I wanted to prepare a mock-up for submission, but it may take me longer than originally planned.
If appropriate, I have a question about the (*) and the (--) features in the formula. If you think acceptable, I was wondering if you could give me a brief explanation on the purpose and meaning of those symbols. I associate (*) with multiplication, but I have never seen (--) in a formula. Thank you.

[vtentarelli]

That was quite unexpected and very much appreciated. I hadn't focused on that part of the spreadsheet as this was a partially completed project dumped on me half-done. Thanks for the input.

[FlameRetired]

You're welcome. Thanks for the feedback and added rep.

Since Marvin's on line at the moment he may be preparing an explanation of the "*" and "--" parts.