Hello All,



I have been trying to work this out for a while and probably have not given my question the correct title. The formula to lookup a text string and apply to everything in that group also has me stumped.

I have a range of bridge plan numbers in column A.
Some bridges have multiple spans, hence the plan number may appear more than once.
Each span may produce different speed restrictions that the vehicle travelling over it should travel at.
The worst restriction is shown as 'OVERLOADED', followed by '5 kph centre' and finally '10 kph'.

Column D is an example of what I am after.

Is there a formula that will:

1. In a column return the worst case i.e. if one span is OVERLOADED then it will apply OVERLOADED to all other spans of that plan number.
2. In the same column or another column return the worst case i.e. if one span is '5 kph centre' then it will apply '5 kph centre' to all other spans of that plan number.
3. By default if it is '10 kph' all of the spans within the group will also be '10 kph'

So if I was using a nested if formula it would return 'OVERLOADED' then '5 kph centre' and finally '10 kph'.

The MAX or MIN examples in columns I & J are what I am after but it is text, not numerals that I would like returned.

Regards, Dave T

Attached Files

Values per group (forum post).xlsm (18.8 KB, 11 views)

try in D3, copied down:
Depending on your version of Excel you may have to array-enter this formula using Ctrl+Shift+Enter, rather then plain Enter.

Hello p45cal,

Very impressive and I cannot thank you enough for your solution.

I spent quite some time searching for a solution or a similar example, but was struggling to come up with the terms to search for. I suspected the MAX function was along the lines of what I needed but every thing I found was for numerical results.

I really appreciate your help.

Regards, Dave T

Hello p45cal,

Minor edit where I changed your use of the semi-colons to commas.

I can live with the results of your formula, but just out of curiosity can you add more criteria and if so how do you do it ???

For example I have been trying to add the following:
"RESTRICTION","OK [Ru]","OK [Vu]","Analysed","Overloaded [Vu]"
without success.

I usually filter out everything above and only retain 'OVERLOADED', '5 kph centre' & '10 kph', but I was curious how they could be added.

Having said that it might just be easier to just return a blank if the column group does not contain any of the the three criteria I am after.

Regards, Dave T

Could you re-attach your file with a few cells (anywhere) with the likes of this picture?
With just 3 possiblities the formula with hard-coded values in was manageable, but as the number of possibilities increases it gets more unwieldy. There are a couple of things we can do: (a) move the possibilities to ranges on a sheet somewhere, (b) be a bit cleverer with the formula!
2021-04-08_105138.png

Hello p45cal,

Thanks for the reply and offer to improve/add to what you have already provided.

I must admit I did try to add an extra hard coded value, which did not work. I thought I must be doing something wrong, which is why I asked about adding an extra value.

As the file I use is an .xlsx and consists of over 30 workbooks I would prefer not to go down the macro path or add a table of lookup values.

Your original, hard coded, three value formula does what I require and is simple to follow and easy to copy down a column.

I suppose if the formula could be made "... a bit cleverer ...", my only comment would be that if it does not match the three already hard coded values then return a blank cell.

I am more than happy with what you have provided so far and unless making a change to return a blank cell is a quick, easy addition I am happy to leave it as is and mark it solved again.

Regards, Dave T

Asking you to add a range of cells is a path to being a bit cleverer with the formula! It will make it very clear to me the order of severity and what you want to appear in all circumstances.

Hello p45cal,

Attached are the outputs placed in order of severity.
Hopefully this is enough.

Regards,
Dave T

Attached Images

Restrictions.jpg (43.5 KB, 16 views)

Shouldn't this information be placed on a sheet attached to your post?

Try:Note that:
1. There's a different number of possibilities inside each {}; 8 in one 9 in the other.
2. The last one of the 9, currently "zzz" is what appears if ANY of the spans for a given bridge is unrecognised or blank. Adjust this for what you want. If you want it to be blank leave it as "" (with NO space between the double-quotes).
3. The IFERROR part returns 9, the last value. If you want to add more values you'll need to alter this.
4. I haven't tried to 'be clever' with the formula.
5. If you want blanks in column B to be treated differently, say so.

Hello p45cal,

Wow, a very impressive modification.

I have been changing your formula by switching parts around and editing the expected result.

In my attached workbook I used data validation in column B to quickly change the restriction type to test the formula.
The small table to the right are the restrictions in order of severity and the expected result from the formula.

As I said in a previous post, I was quite happy with your original formula and it would have done what I was after, but this modification has taken it to another level.

Greatly appreciated, Dave T

Attached Files

Values per group v2 (forum post).xlsm (13.2 KB, 5 views)

Excellent!

You might have realised that if you put 'zzz' in cell G11, then in cell E3 you put: and copy down it will give you the same result.
What's more, if you then edit the formula, select the $G$3:$G$11 part and press F9 on the keyboard, then select the $F$3:$F$10 bit and press F9 on that too you'll get a formula remarkably like the one in cell D3 (bar semicolons instead of commas).

It's maybe late now, but in case you can upgrade to Excel365 or Excel2019, this task will be much easier - like:
And when you'll use e.g. value 0 instead of "OVERLADED", it simplifies again!