Extracting a phrase from a dynamic column.

[gsdanger]

Good Morning gurus of this forum.

I have used your expertise in the past with very pleasing results. You guys are the best!
Apologies for the inputted version of my spreadsheet. I am unable to follow the instructions for using the add-in option mentioned. Hope you don't mind.
I now have another scenario that I just cannot work out (I'm still a numpty!), and am hoping you can assist me with a solution.
In cell E10, I have the following formula: =INDEX($G$1:$H$16, MATCH(C2, $G$1:$G$16,0),LEFT($H$1:$H$16,FIND(".",$H$1:$H$16)-1)).

A B C D E F G H

1		Nr.				Nr.	The Database Area…
2		1				1	2/10
3		2					3/12
4		3					1/11
5							In Equal Class. Ran very well last start and is a big chance in this race.
6						2	2/11
7							4/17
8							5/11
9			The Results				6/11
10			1	#VALUE!			4/9
11			2	#VALUE!			3/10
12			3	#VALUE!			In weaker class. Did well last start and a repeat of that effort will see him in the money.
13						3	9/11
14							5/13
15							3/7
16							In stronger class. Will need to improve on the current form to have any chance in this race.
				I'm looking to extract the three below phrases from h1:h16, and place the matched phrase in cell references c10 to c12.
				In Equal Class.
				In weaker class.
				In stronger class.
				Do I use the INDEX, MATCH, LEN, FIND and LEFT functions to achieve this?

Naturally, it's not working. Please give me some direction in regards to the correct formulas to use to achieve the extraction of the phrases.
As you can see, the H column is dynamic. That is, the number of rows pertaining to each individual number (1,2,3 in this case) will be different depending
on the number of rows within each number's record.
Thank you, in anticipation of your assistance.

Kind Regards......gsdanger

 

[StephenCrump]

	A	B	C	D	E	F	G	H
1								The Database Area…
2								2-Oct
3								3-Dec
4								1-Nov
5								In Equal Class. Ran very well last start and is a big chance in this race.
6								2-Nov
7								Apr-17
8								5-Nov
9								6-Nov
10								4-Sep
11								3-Oct
12								In weaker class. Did well last start and a repeat of that effort will see him in the money.
13								9-Nov
14								May-13
15								3-Jul
16								In stronger class. Will need to improve on the current form to have any chance in this race.
17			In Equal Class
18			In weaker class
19			In stronger class
20
Sheet1

Cell Formulas
Range	Formula
C17:C20	C17	=IFERROR(INDEX(LEFT(H$2:H$16,FIND(".",H$2:H$16)-1),SMALL(IF(ISNUMBER(FIND(".",H$2:H$16)),ROW(H$2:H$16)-ROW(H$2)+1),ROWS(C$17:C17))),"")

 

[gsdanger]

	A	B	C	D	E	F	G	H
1								The Database Area…
2								2-Oct
3								3-Dec
4								1-Nov
5								In Equal Class. Ran very well last start and is a big chance in this race.
6								2-Nov
7								Apr-17
8								5-Nov
9								6-Nov
10								4-Sep
11								3-Oct
12								In weaker class. Did well last start and a repeat of that effort will see him in the money.
13								9-Nov
14								May-13
15								3-Jul
16								In stronger class. Will need to improve on the current form to have any chance in this race.
17			In Equal Class
18			In weaker class
19			In stronger class
20
Sheet1

Cell Formulas
Range	Formula
C17:C20	C17	=IFERROR(INDEX(LEFT(H$2:H$16,FIND(".",H$2:H$16)-1),SMALL(IF(ISNUMBER(FIND(".",H$2:H$16)),ROW(H$2:H$16)-ROW(H$2)+1),ROWS(C$17:C17))),"")

Good day Stephen,
Thank you for your prompt reply.
I have input your code into my spreadsheet, however it does not extract anything into the cells I specified.
The code runs through and responds with the "" at the end. Can you please have a look at the code, to see if it works correctly.
Much appreciated Stephen...
Thank You....regards...gsdanger

 

[StephenCrump]

You'll need to adjust the formula depending on your layout. Note too that the ROWS(C$17:C17) part of the formula is based on the first row that the formula is used, in my case row 17.

Can you please post your layout, and the formula(e) you're using?

 

[gsdanger]

You'll need to adjust the formula depending on your layout. Note too that the ROWS(C$17:C17) part of the formula is based on the first row that the formula is used, in my case row 17.

Can you please post your layout, and the formula(e) you're using?

Hello again Stephen,
My layout is exactly as my example I posted earlier, and the formula I attempted to use was annotated previously.
I have input your formula into cell c17 and nothing is extracted into this cell.
Your formula matches my layout. I have inserted the words "Not Working" between the last set of quotation marks, just to
display that the formula is not responding to any previous requirements.
Please refer to my previous layout.
Hopefully you can assist me with this problem.
Kind Regards.....gsdanger

 

[StephenCrump]

If you click Post #2 where indicated below, you can paste into cell A1 of a blank worksheet

If you do this, is the formula working with results as shown in Post #2? If so, it suggests there is some difference between my formula (or layout) and what you're using?

 

[gsdanger]

If you click Post #2 where indicated below, you can paste into cell A1 of a blank worksheet

If you do this, is the formula working with results as shown in Post #2? If so, it suggests there is some difference between my formula (or layout) and what you're using?

View attachment 58638

Hi Stephen,
Thanks for your response. Unfortunately I have no idea of how to implement what you suggest.
I have clicked on the area you requested and copied into a blank worksheet, with no success.
I have decided to accept my inability to do this (I'm a numpty!) and thank you for your efforts.
please accept my apologies for wasting you valuable time and efforts.

Kind Regards....gsdanger

 

[StephenCrump]

Here's my workbook: NumptyProof?

 

[gsdanger]

Here's my workbook: NumptyProof?

Hello again Stephen,
I have downloaded your NumptyProof file into my spreadsheet and have attempted to open and access your formulas, to massage them into my area.
Unfortunately, after I have downloaded your file, and opened the spreadsheet, I have no formulas within, just the column H data and the 3 results at c17 to 19.
Again, I am hugely frustrated with my inability to follow simple instructions from the experts (you guys). I don't know of any alternatives I have to achieve what I want. It seems that your solution does not meet my requirements (through the eyes of this numpty!).
Again I am giving up in frustration. I have been trying to solve this problem for some months, by experimentation with various formulas, with no joy, hence, as a last resort I have come here, where the experts reside, to achieve my goal.
Unfortunately, the experts (you guys) are a couple of levels above my reasoning and understanding, so, again thank you for your time, effort and expertise and especially your patience with me.

Kind Regards....gsdanger.

 

[bebo021999]

Hi gsdanger
For excell ver2019 and earlier, array formula from Stephen would required confirm with Ctrl-Shift-Enter combination.
Below are my 2 non-array formulas with different approach.
1) Extract data, exclude value/date rows, but include data with/without "." partial match:

=IFERROR(INDEX(LEFT(H$2:H$16,FIND(".",H$2:H$16)-1),AGGREGATE(15,6,(ROW($H$2:$H$16)-MIN(ROW($H$2:$H$16))+1)/ISERROR($H$2:$H$16+0),ROWS($1:1))),"")

2) Extract data, with "." partial match:

=IFERROR(INDEX(LEFT(H$2:H$16,FIND(".",H$2:H$16)-1),AGGREGATE(15,6,(ROW($H$2:$H$16)-MIN(ROW($H$2:$H$16))+1)/NOT(ISERROR(LEFT(H$2:H$16,FIND(".",H$2:H$16)-1))),ROWS($1:1))),"")

Book1
	A	B	C	D	E	F	G	H
1								The Database Area…
2								02/10/2022
3								03/12/2022
4								01/11/2022
5								In Equal Class. Ran very well last start and is a big chance in this race.
6								02/11/2022
7								01/04/2017
8								05/11/2022
9								06/11/2022
10			In Equal Class	In Equal Class				04/09/2022
11			In weaker class	In weaker class				03/10/2022
12			In stronger class	In stronger class				In weaker class. Did well last start and a repeat of that effort will see him in the money.
13								09/11/2022
14								01/05/2013
15								03/07/2022
16								In stronger class. Will need to improve on the current form to have any chance in this race.
Sheet1

Cell Formulas
Range	Formula
C10:C14	C10	=IFERROR(INDEX(LEFT(H$2:H$16,FIND(".",H$2:H$16)-1),AGGREGATE(15,6,(ROW($H$2:$H$16)-MIN(ROW($H$2:$H$16))+1)/ISERROR($H$2:$H$16+0),ROWS($1:1))),"")
D10:D14	D10	=IFERROR(INDEX(LEFT(H$2:H$16,FIND(".",H$2:H$16)-1),AGGREGATE(15,6,(ROW($H$2:$H$16)-MIN(ROW($H$2:$H$16))+1)/NOT(ISERROR(LEFT(H$2:H$16,FIND(".",H$2:H$16)-1))),ROWS($1:1))),"")