Find the next string of the same value, return cell in that row, different column

[Wad Mabbit]

HI,

I have phone numbers in column H, they are NOT unique
I have start times in column C

In column J I wouod like the next start time of the matching number

So say, In H10 and H12 I have the same number+61411222333
In C10 and C12 are start times

In J10 I want the start time of C12

C12 in this instance being the next row from 10 that has a matching number

What I want to have is a lookup of the next appointment time for a matching number. There isn't a contiguous date field I can use so I need some magic!

Can you help?

I then hope to find a safe, reliable SMS service to set up an appointment reminder formula.

 

[Peter_SSs]

How about
Copy of Mark Runsheet 2022

.. and what precisely is the question?

 

[Wad Mabbit]

Step 1, get the 'next lesson time' formula to work, ignoring rows with no phone numbers, and maybe tweak the phone number lookup to see why I'm not consistently pulling a +614nnnnnnnn.

Step 2
Figure a way to pull the date without it being in the actual row. A bit daft, but it's cwhat I'm working with.

Objective:
To pull up the next lesson date, time and duration.

Even better would be a listing in a single cell of all future bookings. With line breaks (of course) that work if copying and pasting to a text message, and (eventual goal) using a script to run an SMS function to use the phone plan carrier (not a 3rd party SMS provider).

So look up next lesson(s), wrap the result in a confirmation text message and send it. One click, or press, or whatevs.

 

[Peter_SSs]

Firstly, I think the phone number formula needs re-working. What are you trying to do with the IF(ISNUMBER(SEARCH("61", part?
Currently it is problematic because if the phone number in column G is, for example, 0411256152 your formula returns the number as +6152

First test this with your full data to see if it better extracts the phone number (with no spaces).

=IF(ISNUMBER(FIND("+61",G6)),MID(SUBSTITUTE(G6," ",""),FIND("+61",SUBSTITUTE(G6," ","")),12),IFERROR("+61"&MID(SUBSTITUTE(G6," ",""),FIND("04",SUBSTITUTE(G6," ",""))+1,9),""))

From your sample, it appears that all phone numbers are mobile phone numbers. Is that correct for your real data.

Even better would be a listing in a single cell of all future bookings.

That would not be reasonably feasible to do with a worksheet formula with your Excel version.
Since you are going to be using a macro in the end, perhaps you should be trying to do all these extractions/concatenations with a macro instead of formulas.
However, it does look like a fairly complex task due to the inconsistency of the data so it may end up being too much to expect from a free public forum like this. However, you never know what helpers here might take on.

 

[Wad Mabbit]

That is a much more effective number conversion, thank you.

The +61 hack was because the first part didn't do well, and I am trying to cope with users who enter mobiles as 0411...., +61411...., but also just 61411... and either of those spaced, or not spaced. Your formula does the trick.

As to the complexities arising from shabby layout, if there was an easy fix, cool, if not, well, I'll be working or a relayout, like:

A DATE /Start

C DAY

D TIME

E HRS

F Finish

G Class

H Location

I DETAILS

J Phone

K COST

L Next Lesson time

M YOU

N Rec'd

O Bal

P SMS

Q Comment

Column A is a mixed Date/time , Sorta vcute, but I'm not sure it's worth having date and time in one cell, copy and paste may mess up the format.

If so, then A will become Date and B will become Time

At least that way there will be data in each row.

C is just to display the day of week and the max hourts for that day:
=IF(OR(ISBLANK(A5), WEEKDAY($A4)=WEEKDAY($A5)),"", text(WEEKDAY($A5),"DDD") & CHAR(10) & VLOOKUP(Text(weekday($A5),"ddd"),DailyHours,2,FALSE) & " hrs")

D Time is the old column of inconsistant lesson times, horrible. Just keeping it for reference.

E Hrs is lesson duration, manual entry

In F Finish, I'm using:
=IF(isnumber(A5), TIMEVALUE(A5) +(E5/24),"")

to extract the finish time

In I Details, is where the phone number is put, extracted to J Phone.

and in L Next Lesson time is:
=if(J5="" , "",IFNA("Your next lesson is at " & Text(INDEX(A6:A$1441,MATCH(J5,J6:J$1441,0)),"hh:mmam/pm on d mmmm yy") & " for " & E5 & " hours."& Char(10) & Char(10) & ReminderB,""))

Named Range ReminderB is
"Please confirm (Y or N). Note: 24hrs notice to cancel, or a fee is involved at the end."

Seems to work so far!
It wouold be nice to have the date, along with the time of the next lesson.

Having a list of future lessons would be cool, but I can work on that another time..

And thanks heaps for your time and consideration.