Compare 2 Cells With Numbers Which Have A Seperator

[honkin]

I have 2 columns of data, Y & Z; they are times of goals scored. I want to create a column which will compare the data in those 2 columns and tell me whether the Home or Away team scored first. The issue I have is that many games do not simply have 1 goal, or 1 goal from each team. Often one team will have multiple goals as will the other.

Here is how the cells look right now if multiple goals

Y Z
23; 44; 72 12; 37

So, in this situation, Z had the earliest goal at 12, so Away is what I would want to see.

I can write a simple

=IF(Y2<Z2,"HOME","AWAY)

but how to have the formula compare only the first number in each cell? When I run it now, 65; 73 in Y and 85 in Z tells me Away scored first, when clearly Home scored in the 65th minute

Is there a way for Excel to compare only the first number in each cell?

cheers

 

[Peter_SSs]

See if this does what you want.

20 03 21.xlsm
	Y	Z	AA
1	Home	Away	First
2	23; 44; 72	12; 37	Away
3	23; 44; 72		Home
4			N/A
5		12; 37	Away
6	12; 37	23; 44; 72	Home
7	15		Home
8		12	Away
First

Cell Formulas
Range	Formula
AA2:AA8	AA2	=CHOOSE(COUNTA(Y2:Z2)+1,"N/A",IF(Y2="",Z$1,Y$1),IF(LEFT(Y2,FIND(";",Y2&";")-1)-LEFT(Z2,FIND(";",Z2&";")-1)<0,Y$1,Z$1))

I don't know what sport you are dealing with but is it possible that one team scores and then the other team also scores before another minute has passed so that both teams have the same number for their first score?

 

[honkin]

hey Pete

We meet again. Thanks so much for your reply

The sports is Football (Soccer). I amended your formula to have it not use the headers from Y & Z, just so it now puts Home or Away, rather than what was there. That part works a treat, so I am trying to see how to tweak that for a second column to identify the second scorer. Can I assume it would be changing the -1 to -2 in LEFT(YS2 & LEFT(Z2 formulas?

Mmm, I tried that but it was hit and miss, so I may not be on the right track there. Sometimes it got the answer right, sometimes it put #VALUE! and sometimes it got it wrong

The idea is trying to analyse results over a long period to see if one team scored first, if there may be a betting chance on the other team to score next. So if the columns showed Home then Away or Away then Home enough times, was there a possibility to get on board.

cheers mate

 

[honkin]

Oh and yes, I guess there will be those few occasions where one team scores and either they score again inside a minute, or the other team does. That will always be a tough one

 

[Peter_SSs]

I am trying to see how to tweak that for a second column to identify the second scorer.

I guess there will be those few occasions where one team scores and either they score again inside a minute, or the other team does. That will always be a tough one

The second issue will be tough since, using the data layout in your sample, there would be nothing to indicate which of the 2 equal times actually came first.

This is the closest I could come. If equal first values are in opposite teams, my formula awards first to the Away team as seen in row 12.
The formula allows for up to 10 goals altogether. You could increase that via the array {1,2,3,4,5,6,7,8,9,10}
If you don't get the same results as me, you may have to confirm the formula with Ctrl+Shift+Enter, not just Enter.
Note also that it relies on scores being separated by a semicolon and a space each time.

20 03 21.xlsm
	Y	Z	AA	AB
1	Home	Away	First	Second
2	23; 44; 72	12; 37	Away	Home
3	23; 44; 72		Home	Home
4			-	-
5		12; 37	Away	Away
6	12; 37	23; 44; 72	Home	Away
7	15		Home	-
8		12	Away	-
9	15	12	Away	Home
10	12; 12	20	Home	Home
11	20	12; 12	Away	Away
12	12; 20	12	Away	Home
1st & 2nd

Cell Formulas
Range	Formula
AA2:AA12	AA2	=CHOOSE(COUNTA(Y2:Z2)+1,"-",IF(Y2="","Away","Home"),IF(LEFT(Y2,FIND(";",Y2&";")-1)-LEFT(Z2,FIND(";",Z2&";")-1)<0,"Home","Away"))
AB2:AB12	AB2	=IF(ISNUMBER(FIND("|",SUBSTITUTE(TRIM(Y2&" "&Z2)," ","|"))),IF(ISNUMBER(FIND("; "&AGGREGATE(15,6,(MID(SUBSTITUTE("; "&Y2&"; "&Z2,"; ",REPT(" ",100)),{1,2,3,4,5,6,7,8,9,10}*100,100)+0),2)&"; ","; "&Y2&"; ")),"Home","Away"),"-")

 

[honkin]

With the equal times, it is not really a concern as it doesn't happen that much.

Unfortunately that formula works only some of the time. Here are some example where it didn't

Y = 43; 56 Z = blank. Should be Home Home instead it is Home and -
Y= 30;36;45 Z=06;83 Should be Away Home but shows as Away Away
Y= 84 Z= 07;90 Should be Away Home but is Away Away

There are others like that as well, so just something not quite doing it.

 

[Peter_SSs]

so just something not quite doing it.

Perhaps you missed ..

Note also that it relies on scores being separated by a semicolon and a space each time.

 

[honkin]

Ah sorry I did as well, apologies there. Did a quick Find & Replace and they all now have a space. It appears to work fine.

I turned both columns into tables now so it will just continue to add the formulas as I add entries.

Thanks so much Pete. You've save so much time

Regards

 

[Peter_SSs]

Thanks so much Pete. You've save so much time

You're welcome.