Find data in various positions in a column and return the cell contents

[JeffGrant]

Hi All,

I am a bit stuck. All assistance is very graciously received and very much appreciated.

Here is my scenario.

I past a column of data into a sheet. No Problem. In the attached sheets, that is Column A.
The first 2 cells in the pasted column data, never change the type of data or the location. So I can extract that easy enough into Column C.
The next 4 cells in the extracted data, Column C, do change the row and can be anywhere in rows 14 to 40. With 3 of the 4 cells, the data is extracted all from the same pasted cell. This is no problem, if I now the row number that it gets pasted to.

I would like to automate the extraction for cells C10, C11 & C13. I am happy to manually type the data for C12, because there are several different types of data here.

For example, some of the data in C12 could be: Mdn HCP, hcp, (c1), (BM60), (bm48+), Class 2, just to name a few. I think there too many variants in this one to automate for now.

I have looked at the Aggregate/Index and Rows functions and I just don't seem to be able to put it together and make it work.

I hope I have pasted the mini-sheet correctly.

Test.xlsx
	A	B	C	D	E	F
5			Calculated Result	Required Result
6	Pasted Data		My Formula Attempt, using fixed cell locations
7						Comment
8	Canterbury	Track	Canterbury	Canterbury		Fixed Cell location
9	Good (4)	Condition	Good (4)	Good(4)		Fixed Cell location
10		Distance (m)	1200	1200		Can be anywhere between rows 14 and 40
11	1	Race Number	1	1		Can be anywhere between rows 14 and 40
12		Class		Mdn HCP		Can be anywhere between rows 14 and 40
13	2	Time	12:45 PM	12:45 PM		Can be anywhere between rows 14 and 40
14
15	3
16
17	4
18
19	5
20
21	6
22
23	7
24	1200m R1 Vinery Stud Mdn Hcp
25	12:45
26
27
28
29
Sheet1

Cell Formulas
Range	Formula
C8	C8	=INDEX(A8:A28,1,1)
C9	C9	=INDEX(A8:A28,2,1)
C10	C10	=VALUE(IF(FIND("m ",A24,1)=5,LEFT(A24,4),LEFT(A24,3)))
C11	C11	=VALUE(MID(A24,SEARCH("R",A24,1)+1,2))
C13	C13	=INDEX(A8:A34,18,1)

 

[Peter_SSs]

There seems to be little confusion about what rows the column A data is in (description says 14:40 but formulas indicate 8:28) but see if these help.
BTW, if the first two values are always the first 2 cells in A8:A28 the formulas to get those can be simple that what you currently have.
You haven't told us how to identify the relevant row data (row 24 in your example) so I have assumed it will be the only row with more than a certain number of characters (15 I used)
I have also assumed that you have the LET function. If not, that formula can be modified.

21 05 04.xlsm
	A	B	C
7
8	Canterbury	Track	Canterbury
9	Good (4)	Condition	Good (4)
10		Distance (m)	1200
11	1	Race Number	1
12		Class
13	2	Time	12:45
14
15	3
16
17	4
18
19	5
20
21	6
22
23	7
24	1200m R1 Vinery Stud Mdn Hcp
25	12:45
26
27
28
Extract

Cell Formulas
Range	Formula
C8:C9	C8	=A8
C10	C10	=LET(s,FILTER(A14:A40,LEN(A14:A40)>15),SUBSTITUTE(LEFT(s,FIND(" ",s)),"m","")+0)
C11	C11	=MID(A24,SEARCH("R",FILTER(A14:A40,LEN(A14:A40)>15),1)+1,2)+0
C13	C13	=MIN(FILTER(A14:A40,ISNUMBER(A14:A40)))

 

[JeffGrant]

Hi Peter, I feel like such a dunce. The true magnitude of my dunceness has shown through when I didn't consider c8 = a8 & c9=a9.

Regarding the other three formulas. Thank you very much. It works beautifully.

(Too close to the trees to see the forest I guess)

 

[JeffGrant]

Hi Peter, Just doing a bit more testing and C11 is not quite there. Where you have referenced MID(A24..., it is the A24 part that can move up and down. So if the text string is not in A24, it will yield a #VALUE error.

Really appreciate the help.

 

[Peter_SSs]

Where you have referenced MID(A24...

Oops, I forgot that one. Try these (also adjusted for rows 8:28)

21 05 04.xlsm
	A	B	C
7
8	Canterbury	Track	Canterbury
9	Good (4)	Condition	Good (4)
10		Distance (m)	1200
11	1	Race Number	1
12		Class
13	2	Time	12:45
14
15	3
16
17	4
18
19	5
20
21	6
22
23	7
24	1200m R1 Vinery Stud Mdn Hcp
25	12:45
26
27
28
Extract

Cell Formulas
Range	Formula
C8:C9	C8	=A8
C10	C10	=LET(s,FILTER(A8:A28,LEN(A8:A28)>15),SUBSTITUTE(LEFT(s,FIND(" ",s)),"m","")+0)
C11	C11	=LET(s,FILTER(A8:A28,LEN(A8:A28)>15),MID(s,SEARCH("R",s,1)+1,2)+0)
C13	C13	=MIN(FILTER(A8:A28,ISNUMBER(A8:A28)))

 

[Peter_SSs]

Also, I was a little confused about your original formula for the distance. Would this shorter one also work for you?

=LET(s,FILTER(A8:A28,LEN(A8:A28)>15),LEFT(s,FIND("m",s)-1)+0)

 

[JeffGrant]

Hi Peter,

thanks for that. I just put this jalopy of formula together

=VALUE(MID(FILTER(A14:A40,LEN(A14:A40)>15),SEARCH("R",FILTER(A14:A40,LEN(A14:A40)>15),1)+1,2))

which did work, but yours is so much neater

 

[JeffGrant]

yep, the shorter one works great. Thanks.

This is a whole new world for me. I grew up on fortran 66 and 77. I'm an oldie, but a goodie (so my wife says)

thanks again.

 

[JeffGrant]

Hi Peter, would you have any idea why I am getting #SPILL errors.

Test.xlsx
	A	B	C	D
3
4
5			Calculated Result
6	Pasted Data		My Formula Attempt, using fixed cell locations	Mr Excel Formulas
7
8	Murray Bridge	Track	Murray Bridge	Murray Bridge
9	Good (4)	Condition	Good (4)	Good (4)
10		Distance (m)	#VALUE!	1200
11	1			#VALUE!
12		Race Number	#VALUE!	1
13	2			#VALUE!
14		Class
15	3
16		Time	12:00 AM	12:35 PM
17	4
18
19	5
20
21	6
22
23	7
24
25	0:00
26	1200m R1 Murray Bridge Club (bm58)
27	12:35
28
29
30
31
Sheet1

Cell Formulas
Range	Formula
C8	C8	=INDEX(A8:A28,1,1)
D8:D9	D8	=A8
C9	C9	=INDEX(A8:A28,2,1)
C10	C10	=VALUE(IF(FIND("m ",A30,1)=5,LEFT(A30,4),LEFT(A30,3)))
D10:D11	D10	=LET(s,FILTER(A7:A40,LEN(A7:A40)>15),LEFT(s,FIND("m",s)-1)+0)
C12	C12	=VALUE(MID(A30,SEARCH("R",A30,1)+1,2))
D12:D13	D12	=LET(s,FILTER(A8:A40,LEN(A8:A40)>15),MID(s,SEARCH("R",s,1)+1,2)+0)
C16	C16	=INDEX(A8:A34,18,1)
D16	D16	=MIN(FILTER(A14:A40,ISNUMBER(A14:A40)))
Dynamic array formulas.

 

[JeffGrant]

Hi Peter, I fixed the #spill error by using a length >20. Using a length >10 increased the spill range to 3 cells.

Where can i learn more about this?

thanks