Extract values for earliest/latest times

[Kpbeard]

I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the use of helper cells. I've not been able to find the right mixture of functions to do this yet. Any help would be greatly appreciated. Sample image of columns A & B and what I'm trying to achieve.
Thank you to anyone who responds.

i

 

[MuchToLearn]

Hi navic

In this particular section of formula:

=IF(MID(INDEX($A$1:$A$21,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($A25,B$1:B$21)),0),0)),2,5)=" -",LEFT(INDEX($A$1:$A$21,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($A25,B$1:B$21)),0),0)),5),LEFT(INDEX($A$1:$A$21,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($A25,B$1:B$21)),0),0)),5)))*1

For extracting the text before the “- “. What’s the reason both of the Value_if_true & Value_if_false are set to the same for this IF function?

and in this section

=TEXT(TRIM(IFERROR(RIGHT(INDEX($A$1:$A$21,MAX(ISNUMBER(SEARCH("*"&$A25&"*",B$1:B$21))*ROW(B$1:B$21))),LEN(INDEX($A$1:$A$21,MAX(ISNUMBER(SEARCH("*"&$A25&"*",B$1:B$21))*ROW(B$1:B$21))))-FIND("- ",INDEX($A$1:$A$21,MAX(ISNUMBER(SEARCH("*"&$A25&"*",B$1:B$21))*ROW(B$1:B$21))))),""))*1,"hh:mm")

For getting the part of text after "-". Why is "*"&$A25&"*" being used instead of just $A25? I tested using $A25 only, doesn’t seem to affect the result. I confirmed in some other simple formulas that * makes value in A25 into a wild card for SEARCH. There must be some reasons the wildcard being used?


Hi Kpbeard,
I did some experiments, if rows inserted within range of formula, the $A$1 would remain $A$1 for the range in the formula, insert above the row outside range of formula, would end up like your case. Click the ranges inside your formula and you can see if formula refer to right group of cells range.
If I may ask. Is your data going to increasing in rows indefinitely? Are the data manually entered or exports from other system of some sort? so output range and structure would remain the same.

 

[navic]

For extracting the text before the “- “. What’s the reason both of the Value_if_true & Value_if_false are set to the same for this IF function?

You can use a simpler ARRAY formula.

Before minus
=LEFT(INDEX($A$1:$A$21;MATCH(TRUE;INDEX(ISNUMBER(SEARCH($A25;B$1:B$21));0);0));5)*1
After minus
=RIGHT(INDEX($A$1:$A$21;MAX(ISNUMBER(SEARCH("*"&$A25&"*";B$1:B$21))*ROW(B$1:B$21)));5)*1
Format as Time

Also, you can use the 'TRIM function' just in case to remove excess unnecessary characters.

Why is "*"&$A25&"*" being used instead of just $A25?

So to consider all possible situations (if the condition "e.g. Haleema to 'name?/Haleema/name?2', is in the middle of the text)
Probably, I used the 'FIND' function first and then 'SEARCH' function. That's probably the cause (I don't remember anymore)

I'm sorry but I don't have more time to work on this task.
View tutorial and follows the steps that are shown there.
Also, you have a file to download there.
This task for me is over. I got more lost in all these questions and calculations.

 

[Kpbeard]

You can use a simpler ARRAY formula.

Also, you can use the 'TRIM function' just in case to remove excess unnecessary characters.


So to consider all possible situations (if the condition "e.g. Haleema to 'name?/Haleema/name?2', is in the middle of the text)
Probably, I used the 'FIND' function first and then 'SEARCH' function. That's probably the cause (I don't remember anymore)

I'm sorry but I don't have more time to work on this task.
View tutorial and follows the steps that are shown there.
Also, you have a file to download there.
This task for me is over. I got more lost in all these questions and calculations.

Navic, Thank you very much for all the time you put into this. I apologize for all the questions, I'm just trying to understand and learn from people like you. I greatly appreciate what you've done. Many thanks.

 

[navic]

@Kpbeard

I'm just trying to understand and learn from people like you.

No problem, I helped as much as I could and as much as my modest knowledge allows.

@MuchToLearn

What’s the reason both of the Value_if_true & Value_if_false are set to the same for this IF function?

I apologize, I am obliged to give you a more detailed explanation of this part of the formula (Today I have some time to study)

The base formula for this part is the formula below (You can see it in the tutorial in step 3 in the 'J14' cell)

=TRIM(IF(MID(J2,2,5)=" -",LEFT(J2,5),LEFT(J2,5)))*1

I made one mistake of mine here.
"MID (J2,2,5)" should be replaced by "MID (J2,5,2)"

The goal was to consider two options of the number of all characters preceded by a minus character.
Option 1 - If there are 5 characters before the minus sign (9:00 -....)
Option 2 - If there are 6 characters before the minus sign (13:00 -....)
This is why I used the LEFT function for both arguments. So no matter how many characters of text there are before the minus sign, when I multiply the result by ‘number 1’, I will have a time value.

Respect to both of you

 

[MuchToLearn]

Hi navic,

I understand, I too can’t stay at this forum the whole time.
Thanks for clearing this up and the tutorials file you created. It’s really helpful in showing how you approach the problem. Using your method as the main basis, I can’t help it trying a slightly different formulation, to see if that also work on this example or not...

=IFERROR(TEXT(TRIM(LEFT(INDEX($A$1:$A$21,MATCH("*"&$A61&"*",B$1:B$21,0)),(FIND("-",INDEX($A$1:$A$21,MATCH("*"&$A61&"*",B$1:B$21,0)))-1)))*1,"hh:mm")&" - "&TEXT(TRIM(RIGHT(INDEX($A$1:$A$21,MAX(ISNUMBER(SEARCH($A25,B$1:B$21))*ROW(B$1:B$21))),LEN(INDEX($A$1:$A$21,MAX(ISNUMBER(SEARCH($A25,B$1:B$21))*ROW(B$1:B$21))))-FIND("- ",INDEX($A$1:$A$21,MAX(ISNUMBER(SEARCH($A25,B$1:B$21))*ROW(B$1:B$21))))))*1,"hh:mm")&"("&TEXT(TRIM(RIGHT(INDEX($A$1:$A$21,MAX(ISNUMBER(SEARCH($A25,B$1:B$21))*ROW(B$1:B$21))),LEN(INDEX($A$1:$A$21,MAX(ISNUMBER(SEARCH($A25,B$1:B$21))*ROW(B$1:B$21))))-FIND("- ",INDEX($A$1:$A$21,MAX(ISNUMBER(SEARCH($A25,B$1:B$21))*ROW(B$1:B$21))))))*1-TRIM(LEFT(INDEX($A$1:$A$21,MATCH("*"&$A61&"*",B$1:B$21,0)),(FIND("-",INDEX($A$1:$A$21,MATCH("*"&$A61&"*",B$1:B$21,0)))-1)))*1,"HH:MM")&")","")

As long as the time intervals are structure the same way, I think the formula should work.

 

[Kpbeard]

MuchToLearn, yes your formula worked as well. Thank you for your suggestions. I have been keeping my head in my studies with five classes this semester which is why I wasn't active this past week, and my uni was hit with the Covid last week so there are a lot of us who are now in quarantine until after 6 November.

Navic, thank you so very much for your tutorial(s) as they are GREATLY appreciated.

I would like to toss my own modified version out there to get comments on to see if this works for you.

=IFERROR(TEXT(LEFT(INDEX($A$1:$A$23,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($A27,B$1:B$23)),0),0)),5)*1,"hh:mm am/pm")&" - "&TEXT(RIGHT(INDEX($A$1:$A$23,MAX(ISNUMBER(SEARCH("*"&$A27&"*",B$1:B$23))*ROW(B$1:B$23))),5)*1,"hh:mm am/pm")&" ( "&TEXT(SUM(LEN(B$1:B$23)-LEN(SUBSTITUTE(B$1:B$23,$A27,"")))/LEN($A27)*0.5/24,"hh:mm")&" )","")

 

[MuchToLearn]

Hi Kpbeard,

You know more than me about Excel when I was studying at Uni. I would probably accept using the helper columns, not that I use Excel that much around the time.
I have quickly pluck in you formula, got correct output in some, but others are showing blank. But don't worry, just keep working on it, it's best if you test the result in your own copy. It took myself many attempts to get my own final result.

 

[Kpbeard]

MuchToLearn, Thank you for your response. I've been able to get the results in all fields for all names in my spreadsheet on both Mac and Windows operating systems. I've learned a lot from you and novic who with both your inputs put me onto this modified version which works great for me. Here is the break down for the formula I'm using.

The first part, based on the name in column A, gives me the first hour that appears (in this case column F). Enter the formula.

=IFERROR(TEXT(LEFT(INDEX($A$1:$A$23,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($A49,F$1:F$23)),0),0)),5)*1,"hh:mm am/pm"),"")

The second part, based on the name in Column A, gives me the last hour that appears (in this case column F) Enter as an array.

=IFERROR(TEXT(RIGHT(INDEX($A$1:$A$23,MAX(ISNUMBER(SEARCH("*"&$A49&"*",F$1:F$23))*ROW(F$1:F$23))),5)*1,"hh:mm am/pm"),"")

And the last part, based on the name in column A, gives the sum of the entries times 0.5 (in this case column F) Enter as an array.

=IFERROR(" ( "&TEXT(SUM(LEN(F$1:F$23)-LEN(SUBSTITUTE(F$1:F$23,$A49,"")))/LEN($A49)*0.5/24,"hh:mm")&" )","")

And when I put it all together and enter as an array I have the following formula. The only addition is the &" - "& in the middle of the formula.

=IFERROR(TEXT(LEFT(INDEX($A$1:$A$23,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($A49,F$1:F$23)),0),0)),5)*1,"hh:mm am/pm")&" - "&TEXT(RIGHT(INDEX($A$1:$A$23,MAX(ISNUMBER(SEARCH("*"&$A49&"*",F$1:F$23))*ROW(F$1:F$23))),5)*1,"hh:mm am/pm")&" ( "&TEXT(SUM(LEN(F$1:F$23)-LEN(SUBSTITUTE(F$1:F$23,$A49,"")))/LEN($A49)*0.5/24,"hh:mm")&" )","")

Thank you both very much for leading me to a solution that I'm very happy with. I greatly appreciate both your help in resolving this issue as well as this forum to post such inquiries.

Cheers to both of you!