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]

Something like this?

22 09 02.xlsm
	C					H		J
1
2	a					9		n
3	b					2		h
4	c					1		g
5	d					3		p
6	e					6		r
7	f					8		none
8	g					1		m
9	h					2		none
10	i					5		j
11	j					5		none
12	k					4		l
13	l					4		none
14	m					1		q
15	n					9		o
16	o					9		s
17	p					3		none
18	q					1		none
19	r					6		none
20	s					9		none
21
22
Find next

Cell Formulas
Range	Formula
J2:J20	J2	=IFNA(INDEX(C3:C$30,MATCH(H2,H3:H$30,0)),"none")

 

[Wad Mabbit]

You are a genius!

 

[Wad Mabbit]

You are a genius!

Now, for an embellishment?

In column A, I have dates. But not sequential.

E.g. A5, A14, A29, A26
In H10 and H12 I have number matches. The formula you gave works beautifully..

J10 has..
=IFNA(INDEX(C11:C$1310,MATCH(H10,H11:H$1310,0)),"")

Can I snag the date for line 10 (which isn't actually in line 10 as its not my sheet). So A6 to A13 are blank, the nearest backwards match is A5 (23 Aug 22).

Yeah, it's messy, but the sheet is set in a 'minimalist' fashion (with the least effort to actually putting useful info in haha).

With a day's date in A, followed by some bookings that day.

Then the next day in A, bookings for THAT day, and so forth.

 

[Wad Mabbit]

Now, for an embellishment?

In column A, I have dates. But not sequential.

E.g. A5, A14, A29, A26
In H10 and H12 I have number matches. The formula you gave works beautifully..

J10 has..
=IFNA(INDEX(C11:C$1310,MATCH(H10,H11:H$1310,0)),"")

Can I snag the date for line 10 (which isn't actually in line 10 as its not my sheet). So A6 to A13 are blank, the nearest backwards match is A5 (23 Aug 22).

Yeah, it's messy, but the sheet is set in a 'minimalist' fashion (with the least effort to actually putting useful info in haha).

With a day's date in A, followed by some bookings that day.

Then the next day in A, bookings for THAT day, and so forth.

Oh, hehe, I forgot to ask, back to the original question, how to ignore blank matches in H?

I.e. There is a match of two blank cells (row may be used to put in a space, a break, a comment etc.)

 

[Wad Mabbit]

Plan C?
I just realised I could use a hidden helper column to echo the prior date in each row...

Would that help, or be unnecessary?

 

[Peter_SSs]

Hard to follow in a number of disjointed posts.
What about a small but representative set of dummy sample data and the expected results with XL2BB and explain again in relation to that sample data?

 

[Wad Mabbit]

Sorry, can't use XL2BB
Attached is a pic..
J305: =if(H305="" , "",IFNA(INDEX(C306:C$1450,MATCH(H305,H306:H$1450,0)),""))

H314: =IFERROR(IF(isnumber(search("61",$G314)),"+"&MID(SUBSTITUTE($G314," ",""),SEARCH("61",SUBSTITUTE($G314," ","")),11) ,IFERROR("+61"&MID($G314,SEARCH("04",$G314)+1,10),MID(SUBSTITUTE($G314," ",""),SEARCH("04",SUBSTITUTE($G314," ","")),11))),"")

 

[Peter_SSs]

Sorry, can't use XL2BB
Attached is a pic..

Sorry cannot copy from an image - and in any case way too much manual typing and likelihood of mis-transcribing anyway.

 

[Wad Mabbit]

How about
Copy of Mark Runsheet 2022