Creating an Array of a players bowling scores

[xgeek]

Hi Guys,

I have created a spreadsheet to log my families 10 pin bowling scores. I am having trouble creating an array of scores from a range based on the players name.
Below is an example of what I want to do. When I add a new game it will get inserted at B11in the first image and all other games move down 6 rows.

On the second image I want to select a name from the dropdown box (this works ok).
I then want to create an array of that players scores in the first sheet and insert them in to a column starting at AA8 in the second sheet based on the name selected in the dropdown box.

So using the example below, If I select Sophie in the dropdown box. I want it to search column B in the first sheet for a name match and then put all her scores in that row (starting with the last game in that row) into an array. The result I am after would look like column AA in the second image and the list below.

Many thanks in advance for any assistance or suggestions.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=TOCOL(SORTBY(FILTER(Sheet1!C11:F100,Sheet1!B11:B100=D2),SEQUENCE(,4,4,-1)),1)

 

[xgeek]

Hi & welcome to MrExcel.
How about

=TOCOL(SORTBY(FILTER(Sheet1!C11:F100,Sheet1!B11:B100=D2),SEQUENCE(,4,4,-1)),1)[/
[/QUOTE]

=TOCOL(SORTBY(FILTER(Sheet1!C19:F100,Sheet1!B19:B100=D6),SEQUENCE(,4,4,-1)),1)

Many thanks it sort of works. I changed D2 to D6 as that is the cell with the players name in. When Sophie is selected the array data is correct with regards to the scores but also has 2 names added to the end:-

When I select my name in the dropbox box the score data is correct but this also gets added to the end of the array:-

It always seems to return 9 rows even if games played is less than 9. Also I added some dummy data to increase my amount of games to 12 and it only returned the last 9 game scores rather than all 12 scores.

It's so close but beyond me how to fix it.

Cheers

 

[Fluff]

In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[xgeek]

I hope this helps. You can click B10 to select a players name.

Many thanks in advance.

Bowling Scores.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
10		Sophie
17		Scores	Thursday, 27 July 2023
18		Players	Game1	Game 2	Game 3	Game 4	Strikes	Avg	Spares	Avg	Gutters	Avg	Total Points	Avg per Game
19		Sophie	75	74							11	5.50	149	75	74
20		Roxy	100	92			2	1.00	3	1.50	11	5.50	192	96	75
21		Stephen	88	40									128	64	116
22		Dummy	4	3	2	1							10	3	121
23		Scores	Tuesday, 25 July 2023												66
24		Players	Game1	Game 2	Game 3	Game 4	Strikes	Avg	Spares	Avg	Gutters	Avg	Total Points	Avg per Game	91
25		Sophie	66	121	116				4	1.33			303	101	93
26		Emily	75	92	66				5	1.67			233	78	Stephen
27		Stephen	102	83	106				4	1.33			291	97	Sophie
28		Dummy	8	7	6	5							26	7
29			Thursday, 13 July 2023
30		Players	Game1	Game 2	Game 3	Game 4	Strikes	Avg	Spares	Avg	Gutters	Avg	Total Points	Avg per Game
31		Emily	87	88									175	88
32		Sophie	93	91									184	92
33		Stephen	73	125									198	99
34		Dummy	12	11	10	9							42	11
Sheet1

Cell Formulas
Range	Formula
L19:L22,J31:J34,H31:H34,L31:L34,J25:J28,H25:H28,L25:L28,J19:J22,H19:H22	L19	=IF(K19>0,K19/COUNTA($C19:$F19),"")
M19:M22,M31:M34,M25:M28	M19	=IF(C19>0,SUM(C19:F19),"")
N19:N22,N31:N34,N25:N28	N19	=IF(C19>0,AVERAGE(C19:F19),"")
O19:O27	O19	=TOCOL(SORTBY(FILTER(Sheet1!C19:F100,Sheet1!B19:B100=B10),SEQUENCE(,4,4,-1)),1)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C6:C10	Cell Value	top 1 values	text	NO
G6:G10	Cell Value	top 1 values	text	NO
H6:H10	Cell Value	top 1 values	text	NO
I6:I10	Cell Value	top 1 values	text	NO
J6:J10	Cell Value	top 1 values	text	NO
K6:K10	Cell Value	top 1 bottom values	text	NO
L6:L10	Cell Value	top 1 values	text	NO
M6:M10	Cell Value	top 1 values	text	NO
N6:N10	Cell Value	top 1 values	text	NO

 

[Fluff]

Thanks for that, delete all the formulae in col O & then re-enter the formula in O19 only & do not use Ctrl Shift Enter.

 

[xgeek]

Ok done that and now it goes over 9 games, but it still adds rogue data at the end of the column. If you change the player name in B10 to say Dummy it returns this:-

Any ideas?

Cheers

 

[Fluff]

Check that you don't have rogue data below row 34

 

[xgeek]

Check that you don't have rogue data below row 34

Yep that was it. So sorry. Many thanks for all your help.

 

[Fluff]

You're welcome & thanks for the feedback.