CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaspreadsheet
Brian Zollinhofer
Brian Zollinhofer

Reputation: 25

Google Sheets formula - Sorting a row of numerical data

Link to example spreadsheet

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.

Upvotes: 1

Views: 91

Answers (1)

player0
player0

Reputation: 1

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))

enter image description here

enter image description here

Upvotes: 2

Related Questions