Conditional Data Accumulation for Sport Analytics

evwill616 It might take 10 -20 minutes copying, pasting and deleting to prepare the data. Not all that bad. Just make sure that the original data set is not changed so that you can always come back to it to check or start from scratch if something went wrong. You will have to create two rows of data for each game, each row identifying if it is for the Home or Away team.

Create two separate sheets with all game data. On each, add a column that will hold a game index number from 1 to 3910. Call one sheet HOME, the other AWAY. On each of them, delete the columns that contain information that do not relate to Home or Away, so that you end up with two data sets. One for the Home teams and one for the Away teams. On each sheet, insert a column marking the teams either as Home or Away. Now you can copy both data sets onto another sheet below each other and sort by game number (then by H or A). Rename the column headers to neutral names (i.e. without a reference to "H" or "A". It will then look something like this (just the first 10 columns shown).

(In Excel for Windows, you could use Power Query for this. It will be more sturdy and less prone to error. And, once set-up you can use it over and over again. So, if you can get your hands on a PC, do that instead.)

By the way, you need to clean-up your data first as the EPL data is inconsistent. From row 3042 and down, all data shifts one column because of the time information inserted there. Either insert blank space in the rows above it or delete the time information from the rows below it. All columns need to be lined up correctly to begin with.