Gathering Date Question

[dgoodrid]

Hi All,

So i have an excel sheet that has lots of different Columns and Rows (lol) with Mixed Data or Letter and Letter and Numbers, Now i know how to split a cells data containing multiple words in split via a , or space for example - Not a problem but as some fields have more data in them thatn other the results of the split are different for each line, What my ask is and i think its a long show, is there a way i can have a list of Names in 1 Column, then in the other columns search the data in the table and reporting back the information

In the Example show,

Under Pld count how many times PlayerA, PlayerN etc.. show in the Played Column
Under Gla count how many times PlayerA, PlayerN etc.. show in the Goals Column - And if Possible count and Additional Numbers after the name
Under *** count how many times PlayerA, PlayerN etc.. show in the Assist Column - And if Possible count and Additional Numbers after the name
Under Pld count how many times PlayerA, PlayerN etc.. show in the Played Column - And if Possible count and Additional Numbers after the name

So it sort of looks like this

Hope this makes sence

 

[JamesCanale]

I don't know what version of excel you have - it's kind of important here. I can't do the "If Possible" part easily if you have older excel. But it works nice in 365:

MrExcelPlayground20.xlsx
	A	B	C	D	E	F	G	H	I	J
1						Old excel - I can't count multi-goals in one game
2	Played	Goals	Assist	Conceded		Player	Pld	Gls	***	Con
3	PlayerA,PlayerB,PlayerC	PlayerA 2,PlayerB	PlayerD	PlayerZ 2		PlayerA	3	1	2	0
4	PlayerA,PlayerD,PlayerF	PlayerC,PlayerG	PlayerA,PlayerR			PlayerB	2	2	0	0
5	PlayerA,PlayerB,PlayerC	PlayerH 3,PlayerB 2	PlayerAA 2	PlayerZ 1		PlayerC	2	1	0	0
6						PlayerD	1	0	1	0
7						PlayerE	0	0	0	0
8						PlayerF	1	0	0	0
9						PlayerG	0	1	0	0
10						PlayerH	0	1	0	0
11						PlayerAA	0	0	1	0
12						PlayerZ	0	0	0	2
13
14						Excel 365
15						Player	Pld	Gls	***	Con
16						PlayerA	3	2	1	0
17						PlayerB	2	3	0	0
18						PlayerC	2	1	0	0
19						PlayerD	1	0	1	0
20						PlayerE	0	0	0	0
21						PlayerF	1	0	0	0
22						PlayerG	0	1	0	0
23						PlayerH	0	3	0	0
24						PlayerAA	0	0	2	0
25						PlayerZ	0	0	0	3
Sheet4

Cell Formulas
Range	Formula
G3:J12,G16:G25	G3	=SUM(--ISNUMBER(SEARCH($F3,A$3:A$5)))
H16:J25	H16	=LET(y,$F16,z,B$3:B$5,a,DROP(TEXTSPLIT(CONCAT(z&","),,","),-1),b,VALUE(RIGHT(a,2)),c,IFERROR(b,1),d,IF(ISERR(b),a,TRIM(LEFT(a,LEN(a)-2))),e,IFERROR(SUM(FILTER(c,d=y)),0),e)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[dgoodrid]

sprry i thought i had out it, 2019

 

[JamesCanale]

This seems okay - except where a players name is wholly contained within another player's name - like PlayerA and PlayerAA. That maybe isn't a problem in real usage.

MrExcelPlayground20.xlsx
	A	B	C	D	E	F	G	H	I	J
2	Played	Goals	Assist	Conceded		Player	Pld	Gls	***	Con
3	PlayerA,PlayerB,PlayerC	PlayerA 2,PlayerB	PlayerD	PlayerZ 2		PlayerA	3	2	2	0
4	PlayerA,PlayerD,PlayerF	PlayerC,PlayerG	PlayerA,PlayerR			PlayerB	2	3	0	0
5	PlayerA,PlayerB,PlayerC	PlayerH 3,PlayerB 2	PlayerAA 2	PlayerZ 1		PlayerC	2	1	0	0
6						PlayerD	1	0	1	0
7						PlayerE	0	0	0	0
8						PlayerF	1	0	0	0
9						PlayerG	0	1	0	0
10						PlayerH	0	3	0	0
11						PlayerAA	0	0	2	0
12						PlayerZ	0	0	0	3
Sheet4

Cell Formulas
Range	Formula
G3:G12	G3	=SUM(--ISNUMBER(SEARCH($F3,A$3:A$5)))
H3:J12	H3	=SUM(IF(ISERR(SEARCH($F3,B$3:B$5)),0,IFERROR(VALUE(MID(B$3:B$5,SEARCH($F3,B$3:B$5)+LEN($F3),2)),1)))

 

[dgoodrid]

No Your right wont happen in real life, thanks, ill give this a go and let you know, thanks in advance

 

[dgoodrid]

This seems okay - except where a players name is wholly contained within another player's name - like PlayerA and PlayerAA. That maybe isn't a problem in real usage.

MrExcelPlayground20.xlsx
	A	B	C	D	E	F	G	H	I	J
2	Played	Goals	Assist	Conceded		Player	Pld	Gls	***	Con
3	PlayerA,PlayerB,PlayerC	PlayerA 2,PlayerB	PlayerD	PlayerZ 2		PlayerA	3	2	2	0
4	PlayerA,PlayerD,PlayerF	PlayerC,PlayerG	PlayerA,PlayerR			PlayerB	2	3	0	0
5	PlayerA,PlayerB,PlayerC	PlayerH 3,PlayerB 2	PlayerAA 2	PlayerZ 1		PlayerC	2	1	0	0
6						PlayerD	1	0	1	0
7						PlayerE	0	0	0	0
8						PlayerF	1	0	0	0
9						PlayerG	0	1	0	0
10						PlayerH	0	3	0	0
11						PlayerAA	0	0	2	0
12						PlayerZ	0	0	0	3
Sheet4

Cell Formulas
Range	Formula
G3:G12	G3	=SUM(--ISNUMBER(SEARCH($F3,A$3:A$5)))
H3:J12	H3	=SUM(IF(ISERR(SEARCH($F3,B$3:B$5)),0,IFERROR(VALUE(MID(B$3:B$5,SEARCH($F3,B$3:B$5)+LEN($F3),2)),1)))

Works Perfectly, Thank you sooo Much, will safe me crap loads of time getting the stats for my Grassrouts football team, thank you again

 

[Fluff]

sprry i thought i had out it, 2019

Please don't forget to update your profile to show you are using 2019. Thanks