Create a TEXT String based on numbers in a spreadsheet

[James8761]

Hi All,

I am trying to create a spreadsheet for a cycling l club I am in. I am trying to build a text string of numbers based on results achieved.

The first one I am trying to do is look up a persons name and their results at a certain location. So for John Smith from the results below I would like a formula that looks at John Smith and Nottingham and gives the answer 3118213.

Secondly I am testing the accuracy of the scores we give. So when John Smith rides at Nottingham and he is given a score greater than 80 what is his results. The answer is 311213.

Does anyone know if this is possible? Thanks for any help.

Name	Location	Score	Place
John Smith	Nottingham	87	3
John Smith	Nottingham	125	1
John Smith	Nottingham	118	1
John Smith	Nottingham	36	8
John Smith	Nottingham	98	2
John Smith	Nottingham	105	1
John Smith	Nottingham	85	3
John Smith	London	122	1
John Smith	London	67	5
John Smith	London	61	4
John Smith	London	121	1
John Smith	London	84	3
John Smith	London	19	9
John Smith	London	100	2
Answer Needed 1. Results at Location
John Smith	Nottingham		3118213
John Smith	London		1541392
Answer Needed 2. If scored above 80 and Location
John Smith	Nottingham	>80	311213
John Smith	London	>80	1132

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[AlanY]

which excel version are you using?
better still, could you check if the TEXTJOIN() function is available on your excel?

 

[James8761]

Thanks for the reply Alan. I'm on O365 and yes I see the TEXTJOIN function.

 

[AlanY]

that will be much easier try this, 0 in Col H will give a full list

Book1
	A	B	C	D	E	F	G	H	I
1	Name	Location	Score	Place
2	John Smith	Nottingham	87	3		John Smith	Nottingham	80	311213
3	John Smith	Nottingham	125	1		John Smith	London	100	11
4	John Smith	Nottingham	118	1		John Smith	London	0	1541392
5	John Smith	Nottingham	36	8
6	John Smith	Nottingham	98	2
7	John Smith	Nottingham	105	1
8	John Smith	Nottingham	85	3
9	John Smith	London	122	1
10	John Smith	London	67	5
11	John Smith	London	61	4
12	John Smith	London	121	1
13	John Smith	London	84	3
14	John Smith	London	19	9
15	John Smith	London	100	2
Sheet7

Cell Formulas
Range	Formula
I2	{=SUBSTITUTE(TEXTJOIN(,,IF($A$2:$A$15=F2,IF($B$2:$B$15=G2,IF($C$2:$C$15>H2,$D$2:$D$15,"")))),FALSE,"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[James8761]

Thanks very much, Alan. I input that and it did exactly what I wanted.......one more question. If I just wanted results for John Smith and Nottingham what would the formula be please?

 

[AlanY]

Thanks very much, Alan. I input that and it did exactly what I wanted.......one more question. If I just wanted results for John Smith and Nottingham what would the formula be please?

you can just put 0 in H2 to get the full string, don't need to change formula

Book1
	A	B	C	D	E	F	G	H	I
1	Name	Location	Score	Place
2	John Smith	Nottingham	87	3		John Smith	Nottingham	0	3118213
3	John Smith	Nottingham	125	1		John Smith	London	100	11
4	John Smith	Nottingham	118	1		John Smith	London	0	1541392
5	John Smith	Nottingham	36	8
6	John Smith	Nottingham	98	2
7	John Smith	Nottingham	105	1
8	John Smith	Nottingham	85	3
9	John Smith	London	122	1
10	John Smith	London	67	5
11	John Smith	London	61	4
12	John Smith	London	121	1
13	John Smith	London	84	3
14	John Smith	London	19	9
15	John Smith	London	100	2
Sheet7

Cell Formulas
Range	Formula
I2	{=SUBSTITUTE(TEXTJOIN(,,IF($A$2:$A$15=F2,IF($B$2:$B$15=G2,IF($C$2:$C$15>H2,$D$2:$D$15,"")))),FALSE,"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[James8761]

Yep, thanks Alan. Say I didn't have Column C though or didn't want it anymore. (We are thinking of getting rid of that). Could I just delete part of the formula?

 

[AlanY]

you can use this instead

=SUBSTITUTE(TEXTJOIN(,,IF($A$2:$A$15=F2,IF($B$2:$B$15=G2,$D$2:$D$15,""))),FALSE,"")

 

[James8761]

That's great. Thanks very much for your help, Alan.

 

[AlanY]

you're welcome