Extract and Move Data from one row to another

[Celticshadow]

Hi Forum

In relation to this previous question and solution successfully given (Extract Data from Row) may I ask forum if they could help me out further with a jockey/trainer report that I receive each day also. The report shows each jockey and trainer with their corresponding strike rates and then underneath each combination are then shown those combinations times and horses entered for races that same day. What I would like to do is take each horse along with the race time and place it in the same horizontal line as the combinations and at the end of the row.

I have attached a google docs link to hopefully show what I would like the formula to do. If I may also ask would it be possible to show how the formula is working as I do have other reports that would need something similar and if I knew what the formula is doing then I might have a better understanding and hopefully attempt something myself. Many thanks in advance.

Regards

Jockey Trainer Rpt

docs.google.com

PS The solution is for an excel sheet and google docs is shown for explanation only.

 

[Peter_SSs]

Is this what you are after?

Jockey Trainer Rpt.xlsx
	A	B							I	J	K	L	M
1	Track	Jockey							Strike% (track)
2	Exeter	Bament, Mr J J							100%	4.08 Looking For Larry (IRE)
3	4.08 Looking For Larry (IRE)
4	Exeter	Dingle, Rex							100%	4.43 Kocktail Bleu (FR)
5	4.43 Kocktail Bleu (FR)
6	Chepstow	Stevens, Miss A							100%	4.30 Pride Of Paris (IRE)
7	4.30 Pride Of Paris (IRE)
8	Navan	Harvey, A							100%	4.20 Street Value (IRE)
9	4.20 Street Value (IRE)
10	Navan	OBrien, P M							100%	3.12 Comeonarchie (IRE)
11	3.12 Comeonarchie (IRE)
12	Exeter	Godfrey, Ben							100%	4.43 Ilostasock (IRE)
13	4.43 Ilostasock (IRE)
14	Chepstow	Jones, Ben R							50%	3.55 Decorated
15	3.55 Decorated
16	Exeter	Cobden, Harry							50%	4.08 Haas Boy (FR)
17	4.08 Haas Boy (FR)
18	Exeter	Bass, David							50%	3.33 Strackan
19	3.33 Strackan
20	Navan	Lavery, Mr D G							50%	4.55 Noticebox
21	4.55 Noticebox
22	Chepstow	Jones, Ben R							42.86%	1.20 Ben Solo (IRE)	3.20 House Of Stories (IRE)	4.30 River Voyage (IRE)
23	1.20 Ben Solo (IRE)
24	3.20 House Of Stories (IRE)
25	4.30 River Voyage (IRE)
26	Navan	Byrnes, Mr P							41.67%	4.20 Whateys Quest (IRE)
27	4.20 Whateys Quest (IRE)
28	Exeter	Boinville, Nico							39.13%	1.30 As The Fella Says (IRE)	3.33 Jeriko Du Reponet (FR)
29	1.30 As The Fella Says (IRE)
30	3.33 Jeriko Du Reponet (FR)
31	Exeter	Cobden, Harry							33.33%	1.30 Sorceleur (FR)	2.30 Regents Stroll (IRE)	3.33 Mofasa	4.43 Jeriko Place (FR)
32	1.30 Sorceleur (FR)
33	2.30 Regents Stroll (IRE)
34	3.33 Mofasa
35	4.43 Jeriko Place (FR)
36
Sheet1

Cell Formulas
Range	Formula
J2:J21,J23:J27,J29:J30,J32:J35,J31:M31,J28:K28,J22:L22	J2	=IF(I2="","",TRANSPOSE(A3:INDEX(A3:A$100,IFERROR(MATCH("?*",B3:B$100,0)-1,COUNTA(A3:A$100)))))

 

[Celticshadow]

Hi Peter_SSs

Many thanks for your above formula, I am delighted to say that it works an absolute treat and I am extremely grateful for your help and expertise which as usual is invaluable.

I understand the formula in so far as it looks for the percentage in I2, and then the transposing of the data in column A, However I am unsure as to what the rest of the formula is saying and doing from the IFERROR part onward. I would be grateful if you have the time to explain it further at your convenience but if not then no worries as your solution itself is more than generous.

Regards

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

I am unsure as to what the rest of the formula is saying and doing from the IFERROR part onward. I would be grateful if you have the time to explain it further at your convenience but if not then no worries as your solution itself is more than generous.

When the formula finds a value in column I, it needs to grab all the values in column A down to the line before the next 'section'. That 'section' starts when a new jockey appears in column B (at least that's how I did it)
Mostly the values you want are found with this
A3:INDEX(A3:A$100,MATCH("?*",B3:B$74,0)-1)
That is, start at the row below the formula and then match the next non-blank (?* are wildcards meaning find at least one character) in column B and come up one row
Then transpose to write the results across the row.

The problem is the last section where we have a value in row 31 in column I but no "next jockey" in column B. In this case the formula snippet above returns an error so you wouldn't get those last 4 blue values returned, only an error. The IFERROR then says in that case instead of looking for the next jockey, just count how many rows below row 31 have values in column A. this returns 4 so you get those last 4 results after all.

Instead of IFERROR, I possibly should have used the specific error #NA and it would have been as below, but no big deal
=IF(I2="","",TRANSPOSE(A3:INDEX(A3:A$100,IFNA(MATCH("?*",B3:B$100,0)-1,COUNTA(A3:A$100)))))