Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-07-2021, 07:52 AM
Dave T Dave T is offline Assign text string against groups of data Windows 7 64bit Assign text string against groups of data Office 2013
Advanced Beginner
Assign text string against groups of data
 
Join Date: Nov 2014
Location: Australia
Posts: 60
Dave T is on a distinguished road
Default Assign text string against groups of data

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
File Type: xlsm Values per group (forum post).xlsm (18.8 KB, 7 views)
Reply With Quote
  #2  
Old 04-07-2021, 10:49 AM
p45cal p45cal is offline Assign text string against groups of data Windows 10 Assign text string against groups of data Office 2019
Expert
 
Join Date: Apr 2014
Posts: 478
p45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to behold
Default

try in D3, copied down:
Code:
=INDEX({"10 kph";"5 kph centre";"OVERLOADED"},MAX(IF($A$3:$A$153=A3,MATCH($B$3:$B$153,{"10 kph";"5 kph centre";"OVERLOADED"}))))
Depending on your version of Excel you may have to array-enter this formula using Ctrl+Shift+Enter, rather then plain Enter.
Reply With Quote
  #3  
Old 04-07-2021, 04:01 PM
Dave T Dave T is offline Assign text string against groups of data Windows 7 64bit Assign text string against groups of data Office 2013
Advanced Beginner
Assign text string against groups of data
 
Join Date: Nov 2014
Location: Australia
Posts: 60
Dave T is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 04-07-2021, 07:04 PM
Dave T Dave T is offline Assign text string against groups of data Windows 7 64bit Assign text string against groups of data Office 2013
Advanced Beginner
Assign text string against groups of data
 
Join Date: Nov 2014
Location: Australia
Posts: 60
Dave T is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 04-08-2021, 03:02 AM
p45cal p45cal is offline Assign text string against groups of data Windows 10 Assign text string against groups of data Office 2019
Expert
 
Join Date: Apr 2014
Posts: 478
p45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to behold
Default

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
Reply With Quote
  #6  
Old 04-08-2021, 04:00 PM
Dave T Dave T is offline Assign text string against groups of data Windows 7 64bit Assign text string against groups of data Office 2013
Advanced Beginner
Assign text string against groups of data
 
Join Date: Nov 2014
Location: Australia
Posts: 60
Dave T is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 04-08-2021, 05:47 PM
p45cal p45cal is offline Assign text string against groups of data Windows 10 Assign text string against groups of data Office 2019
Expert
 
Join Date: Apr 2014
Posts: 478
p45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to behold
Default

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.
Reply With Quote
  #8  
Old 04-08-2021, 11:06 PM
Dave T Dave T is offline Assign text string against groups of data Windows 7 64bit Assign text string against groups of data Office 2013
Advanced Beginner
Assign text string against groups of data
 
Join Date: Nov 2014
Location: Australia
Posts: 60
Dave T is on a distinguished road
Default

Hello p45cal,

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

Regards,
Dave T
Attached Images
File Type: jpg Restrictions.jpg (43.5 KB, 10 views)
Reply With Quote
  #9  
Old 04-08-2021, 11:53 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Assign text string against groups of data Windows 7 64bit Assign text string against groups of data Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,456
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

Shouldn't this information be placed on a sheet attached to your post?
Reply With Quote
  #10  
Old 04-09-2021, 06:05 AM
p45cal p45cal is offline Assign text string against groups of data Windows 10 Assign text string against groups of data Office 2019
Expert
 
Join Date: Apr 2014
Posts: 478
p45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to behold
Default

Try:
Code:
=INDEX({"10 kph","10 kph","10 kph","5 kph","5 kph","5 kph","OVERLOADED","OVERLOADED","zzz"},MAX(IF($A$3:$A$153=A3,IFERROR(MATCH($B$3:$B$153,{"OK [Ru]","OK [Vu]","10 kph","RESTRICTION","5 kph centre","Analysed","Overloaded [Vu]","OVERLOADED"},0),9))))
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.
Reply With Quote
  #11  
Old 04-11-2021, 05:28 PM
Dave T Dave T is offline Assign text string against groups of data Windows 7 64bit Assign text string against groups of data Office 2013
Advanced Beginner
Assign text string against groups of data
 
Join Date: Nov 2014
Location: Australia
Posts: 60
Dave T is on a distinguished road
Default

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
File Type: xlsm Values per group v2 (forum post).xlsm (13.2 KB, 1 views)
Reply With Quote
  #12  
Old 04-12-2021, 05:00 AM
p45cal p45cal is offline Assign text string against groups of data Windows 10 Assign text string against groups of data Office 2019
Expert
 
Join Date: Apr 2014
Posts: 478
p45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to behold
Default

Excellent!
Reply With Quote
  #13  
Old 04-12-2021, 05:31 AM
p45cal p45cal is offline Assign text string against groups of data Windows 10 Assign text string against groups of data Office 2019
Expert
 
Join Date: Apr 2014
Posts: 478
p45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to behold
Default

You might have realised that if you put 'zzz' in cell G11, then in cell E3 you put:
Code:
=INDEX($G$3:$G$11,MAX(IF($A$3:$A$18=$A3,IFERROR(MATCH($B$3:$B$18,$F$3:$F$10,0),9))))
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).
Reply With Quote
  #14  
Old 04-12-2021, 05:50 AM
ArviLaanemets ArviLaanemets is online now Assign text string against groups of data Windows 8 Assign text string against groups of data Office 2016
Expert
 
Join Date: May 2017
Posts: 615
ArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of light
Default

It's maybe late now, but in case you can upgrade to Excel365 or Excel2019, this task will be much easier - like:
Code:
=IF(COUNTIFS($A$3:$A$153,$A3,$F$3:$F$153,"OVERLOADED")>0;"OVERLOADED";MINIFS($F$3:$F$153,$A$3:$A$153,$A3))
And when you'll use e.g. value 0 instead of "OVERLADED", it simplifies again!
Code:
=MINIFS($F$3:$F$153,$A$3:$A$153,$A3)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Search for specific data in a text string teligence Excel 1 05-20-2017 11:51 AM
How to get groups of data on each page aldumil Mail Merge 7 06-24-2015 01:28 PM
Assign text string against groups of data Sorting Groups of Data, keeping continuity carter Excel 1 04-06-2015 01:25 AM
Assign text string against groups of data Way to search for a string in text file, pull out everything until another string? omahadivision Excel Programming 12 11-23-2013 12:10 PM
Assign text string against groups of data Assign resource groups to tasks jjmclell Project 2 12-17-2012 04:28 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 06:46 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft