Google Sheets formula - Sorting a row of numerical data

Question

I'm trying to find out how often the lead of a football match changes based on the time goals are scored.

In A1, the user inputs the times of the home team's goals, separated by commas. B1 has the same but for the away team.

I use the following code (in A2) to split those scores into separate cells and put them in order from least to greatest:

=IF(A1="","", TRANSPOSE(SORT(TRANSPOSE(SPLIT(A1,",")),1,TRUE)))

I've reserved A2:J2 for these goals.

In K2, I have the same idea, but for the away team:

=IF(B1="","", TRANSPOSE(SORT(TRANSPOSE(SPLIT(B1,",")),1,TRUE)))

I have K2:T2 reserved for these results.

I would now like to get all of the results from A2:T2 to be converted from the inputed minute data to 1H, 2H, 1A or 2A (in order of the times the goals were scored). 1H means the goal was a goal for the home team in the 1st half (<=45). 2H = home 2nd half. 1A = away 1st half. 2A = away 2nd half.

The output I'd like to have is shown in U2:AK2.

Any help is appreciated.

player0 · Accepted Answer · 2021-07-16 17:03:47Z

2

try:

=TRANSPOSE(INDEX(SORT(SPLIT(FLATTEN({
 IF(SPLIT(A1, ",")<=45, 1, 2)&"H×"&SPLIT(A1, ","), 
 IF(SPLIT(B1, ",")<=45, 1, 2)&"A×"&SPLIT(B1, ",")}), "×"), 2, 1),, 1))

edited Jul 16, 2021 at 17:03

answered Jul 16, 2021 at 16:05

player0

131k14 gold badges92 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Brian Zollinhofer Over a year ago

This is great, thanks so much. I did have another issue arise when a team doesn't score any goals. I get the #VALUE! error: Function SPLIT parameter 1 value should be non-empty. I feel like an ISBLANK() should help, but I don't understand how the curly brackets work in your formula.

player0 Over a year ago

@BrianZollinhofer you can add IFERROR like: i.sstatic.net/9tbq5.png

Brian Zollinhofer Over a year ago

Genius. I haven't been in google sheets or programming long enough to be able to see these connections so quickly. Thanks so much!

Collectives™ on Stack Overflow

Google Sheets formula - Sorting a row of numerical data

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related