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.
[TABLE="width: 324"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Location[/TD]
[TD]Score[/TD]
[TD]Place[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Nottingham[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Nottingham[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Nottingham[/TD]
[TD="align: right"]118[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Nottingham[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Nottingham[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Nottingham[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Nottingham[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]London[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]London[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]London[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]London[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]London[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]London[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]London[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Answer Needed 1. Results at Location[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Nottingham[/TD]
[TD][/TD]
[TD="align: right"]3118213[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]London[/TD]
[TD][/TD]
[TD="align: right"]1541392[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]Answer Needed 2. If scored above 80 and Location[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Nottingham[/TD]
[TD]>80[/TD]
[TD="align: right"]311213[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]London[/TD]
[TD]>80[/TD]
[TD="align: right"]1132[/TD]
[/TR]
</tbody>[/TABLE]

 

[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