Finding a group of specific values and Showing First and Last Value

K

Kazaard

New Member
Joined
Apr 30, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi Guys,

Im new here and was wondering if someone could help me please.

So the sheet contains the times of booking slots. eg 12:45,13:00,13:15,13:30, these times can be anywhere in the row. So the first time would be the start time of the booking and the last time would be the end of the booking. So if there is 4 time slots essentially they are booking 45min of timeslots between the times. I am looking for a formula or VBA that will give me the first and last times in order to read it into another sheet as a list of bookings.
So the cells EA and EB would say 12:45 and 13:30. If anyone can help I would really appreciate it.
Copy of BCC Book In form.xlsm
DEDFDGDHDIDJDKDLDMDNDODPDQDRDSDTDUDVDWDXDYDZEAEBECEDEEEFEGEHEIEJ
27Booking 1Booking 2Booking 3Booking 4Booking 5
2820212223242526272829303132333435363738394041
2912:4513:0013:1513:3013:4514:0014:1514:3014:4515:0015:1515:3015:4516:0016:1516:3016:4517:0017:1517:3017:4518:00StartEndStartEndStartEndStartEndStartEnd
3012:45:0013:00:0013:15:0013:30:00                17:45:0018:00:00
31                      
32                      
33                      
34                      
35                      
36         15:00:0015:15:0015:30:0015:45:00         
37                      
38                      
39                      
40                      
41                      
42                      
43                      
44                      
45                      
46                      
47                      
48                      
49                      
Team1
Cell Formulas
RangeFormula
DE30:DZ49DE30=IF(OR(AND(DD30="O",Z30="O"),AND(Z30="O"),AND(Y30="O")),DE$29,"")
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Hi & welcome to MrExcel.
How about
VBA Code: 
Function Kazaard(Rng As Range) As Variant
   Dim Ary As Variant
   Dim i As Long, j As Long
   ReDim Ary(1 To Rng.Count)
   
   For i = 1 To Rng.Count
      If Rng(i) <> "" Then
         If i = 1 Or i = Rng.Count Then
            j = j + 1
            Ary(j) = Rng(i)
         ElseIf Rng(i - 1) = "" Or Rng(i + 1) = "" Then
            j = j + 1
            Ary(j) = Rng(i)
         End If
      End If
   Next i
   ReDim Preserve Ary(1 To j)
   Kazaard = Ary
End Function
used like
+Fluff 1.xlsm
DEDFDGDHDIDJDKDLDMDNDODPDQDRDSDTDUDVDWDXDYDZEAEBECEDEEEFEGEHEIEJ
27Booking 1Booking 2Booking 3Booking 4Booking 5
2820212223242526272829303132333435363738394041
2912:45:0013:00:0013:15:0013:30:0013:45:0014:00:0014:15:0014:30:0014:45:0015:00:0015:15:0015:30:0015:45:0016:00:0016:15:0016:30:0016:45:0017:00:0017:15:0017:30:0017:45:0018:00:00StartEndStartEndStartEndStartEndStartEnd
3012:45:0013:00:0013:15:0013:30:00                17:45:0018:00:0012:45:0013:30:0017:45:0018:00:00
31  13:15:0013:30:0013:45:0014:00:0014:15:00 14:45:0015:00:0015:15:00  16:00:0016:15:0016:30:0016:45:00 17:15:0017:30:0017:45:00 13:15:0014:15:0014:45:0015:15:0016:00:0016:45:0017:15:0017:45:00
3212:45:0013:00:00 13:30:0013:45:0014:00:00 14:30:0014:45:0015:00:00  15:45:0016:00:00   17:00:0017:15:00   12:45:0013:00:0013:30:0014:00:0014:30:0015:00:0015:45:0016:00:0017:00:0017:15:00
Main
Cell Formulas
RangeFormula
DE30:DZ32DE30=IF(OR(AND(DD30="O",Z30="O"),AND(Z30="O"),AND(Y30="O")),DE$29,"")
EA30:ED30,EA32:EJ32,EA31:EH31EA30=Kazaard(DE30:DZ30)
Dynamic array formulas.
 
Upvote 0
Solution
Another option with a formula
+Fluff 1.xlsm
DEDFDGDHDIDJDKDLDMDNDODPDQDRDSDTDUDVDWDXDYDZEAEBECEDEEEFEGEHEIEJ
27Booking 1Booking 2Booking 3Booking 4Booking 5
2820212223242526272829303132333435363738394041
2912:45:0013:00:0013:15:0013:30:0013:45:0014:00:0014:15:0014:30:0014:45:0015:00:0015:15:0015:30:0015:45:0016:00:0016:15:0016:30:0016:45:0017:00:0017:15:0017:30:0017:45:0018:00:00StartEndStartEndStartEndStartEndStartEnd
3012:45:0013:00:0013:15:0013:30:00                17:45:0018:00:0012:45:0013:30:0017:45:0018:00:00
31  13:15:0013:30:0013:45:0014:00:0014:15:00 14:45:0015:00:0015:15:00  16:00:0016:15:0016:30:0016:45:00 17:15:0017:30:0017:45:00 13:15:0014:15:0014:45:0015:15:0016:00:0016:45:0017:15:0017:45:00
3212:45:0013:00:00 13:30:0013:45:0014:00:00 14:30:0014:45:0015:00:00  15:45:0016:00:00   17:00:0017:15:00   12:45:0013:00:0013:30:0014:00:0014:30:0015:00:0015:45:0016:00:0017:00:0017:15:00
33                       
Main
Cell Formulas
RangeFormula
DE30:DZ33DE30=IF(OR(AND(DD30="O",Z30="O"),AND(Z30="O"),AND(Y30="O")),DE$29,"")
EA30:ED30,EA33,EA32:EJ32,EA31:EH31EA30=LET( Txt,"<k><m>"&TEXTJOIN("</m><m>",0,"",DE30:DZ30,"")&"</m></k>", St,FILTERXML(Txt,"//m[string-length()=0]/following-sibling::m[string-length()>0 and position()= 1]"), Ed,FILTERXML(Txt,"//m[string-length()=0]/preceding-sibling::m[string-length()>0 and position()= 1]"), Rws,ROWS(St)*2, IFERROR(INDEX(CHOOSE({1,2},St,Ed),INT(SEQUENCE(,Rws,,0.5)),MOD(SEQUENCE(,Rws,2),2)+1),"") )
Dynamic array formulas.
 
Upvote 0
Fluff said:
Hi & welcome to MrExcel.
How about
VBA Code: 
Function Kazaard(Rng As Range) As Variant
   Dim Ary As Variant
   Dim i As Long, j As Long
   ReDim Ary(1 To Rng.Count)
  
   For i = 1 To Rng.Count
      If Rng(i) <> "" Then
         If i = 1 Or i = Rng.Count Then
            j = j + 1
            Ary(j) = Rng(i)
         ElseIf Rng(i - 1) = "" Or Rng(i + 1) = "" Then
            j = j + 1
            Ary(j) = Rng(i)
         End If
      End If
   Next i
   ReDim Preserve Ary(1 To j)
   Kazaard = Ary
End Function
used like
+Fluff 1.xlsm
DEDFDGDHDIDJDKDLDMDNDODPDQDRDSDTDUDVDWDXDYDZEAEBECEDEEEFEGEHEIEJ
27Booking 1Booking 2Booking 3Booking 4Booking 5
2820212223242526272829303132333435363738394041
2912:45:0013:00:0013:15:0013:30:0013:45:0014:00:0014:15:0014:30:0014:45:0015:00:0015:15:0015:30:0015:45:0016:00:0016:15:0016:30:0016:45:0017:00:0017:15:0017:30:0017:45:0018:00:00StartEndStartEndStartEndStartEndStartEnd
3012:45:0013:00:0013:15:0013:30:00                17:45:0018:00:0012:45:0013:30:0017:45:0018:00:00
31  13:15:0013:30:0013:45:0014:00:0014:15:00 14:45:0015:00:0015:15:00  16:00:0016:15:0016:30:0016:45:00 17:15:0017:30:0017:45:00 13:15:0014:15:0014:45:0015:15:0016:00:0016:45:0017:15:0017:45:00
3212:45:0013:00:00 13:30:0013:45:0014:00:00 14:30:0014:45:0015:00:00  15:45:0016:00:00   17:00:0017:15:00   12:45:0013:00:0013:30:0014:00:0014:30:0015:00:0015:45:0016:00:0017:00:0017:15:00
Main
Cell Formulas
RangeFormula
DE30:DZ32DE30=IF(OR(AND(DD30="O",Z30="O"),AND(Z30="O"),AND(Y30="O")),DE$29,"")
EA30:ED30,EA32:EJ32,EA31:EH31EA30=Kazaard(DE30:DZ30)
Dynamic array formulas.
Click to expand...
Hi Fluff,

Thank you very much for the prompt response, that looks like it does exactly what I wanted it to do. How would I apply this to another set of data after so say if I had another time allocation slots after the current booking slots for a different event (on the left)and had booking times at the end, In total I am looking to book 3 events so it will be x3 so it will be the same format with the times and booking times at the end one after the other.
 
Upvote 0
I'm afraid I don't understand what you are asking. Can you post some sample data along with expected outcome.
 
Upvote 0
Fluff said:
I'm afraid I don't understand what you are asking. Can you post some sample data along with expected outcome.
Click to expand...
Copy of BCC Book In form.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAV
515:0015:1515:3015:4516:0016:1516:3016:4517:0017:15Booking 1Booking 2Booking 315:0015:1515:3015:4516:0016:1516:3016:4517:0017:15Booking 1Booking 2Booking 315:0015:1515:3015:4516:0016:1516:3016:4517:0017:15Booking 1Booking 2Booking 3
6
7StartEndStartEndStartEndStartEndStartEndStartEndStartEndStartEndStartEnd
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Sheet1


Hi sorry for being unclear, so the format would be something like the above, so the first exmaple would be pretty much the finished version as there are times 08:00-18:00 and 5 booking slots. The above is the simplified version where ive only shown the times of 15:00-17:15 and 3 booking slots. I cant seem to upload the full sheet only small snipets on here.

Many Thanks in advance.
 
Upvote 0
With that layout you would use
Excel Formula: 
=Kazaard(A8:J8)
in K8 & then
Excel Formula: 
=Kazaard(Q8:Z8)
in AA8.
Or have I misunderstood.
 
Upvote 0
Fluff said:
With that layout you would use
Excel Formula: 
=Kazaard(A8:J8)
in K8 & then
Excel Formula: 
=Kazaard(Q8:Z8)
in AA8.
Or have I misunderstood.
Click to expand...
Thank you very much that makes sense, I take it I would declare this at the bottom of your formula and substitute the line "Kazaard=Ary" with all the above mentioned.

Many Thanks
 
Upvote 0
No you don't need to change the UDF, you just feed it the cells you want it to look at, like I showed.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Massive Data Analysis needing a helper column
Replies
9
Views
621
Fluff
Fluff

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,022
Latest member
RobertV1609

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top