Hi, first post here, hope i have done the process correctly....
I have been trying to figure out the best way to do this for a few days but keep getting stumped.
I have a data set from a sports competition whereby each row specifies all data from one match (date, day, time, venue, home team, away team etc). The end result i want to get to is to be able to call/filter for how many days rest a team has had between games. To get to this i have decided to split each game to two rows - row one for home team with all info and row two for away team with all info. This way i am able to create a formula to count days between dates since team last appeared in the "team" column.
The problem i am having now is trying to create a macro which enables me to loop through the data set and print the information that pertains to both teams onto consecutive rows. For example - the date value in "2022 data" cell A2 should print on "2022 format" cells A2 (home) and A3 (away). Then the date value in A3 should print on new sheet in cells A4 and A5 etc etc. I have managed to get it to work but only for one iteration - it then overwrites itself as i dont know how to make it print from the next empty row. The order in which everything happens is obviously not important, just that the information ends up in the right spot.
Hope someone can help! Thanks a bunch - pics and code below
dataset.jpg
Sub formatdata() 'Define Start and End Lines for our For Loop start_line = 2 end_line = Sheets("2022 data").Cells(Rows.Count, "A").End(xlUp).Row 'clear contents Sheets("2022 format").Range("A2:M203").ClearContents 'define line to start printing on format sheet new_line = 2 'For loop to run through 2022 data For start_line = 2 To 3 'define variables on 2022 data sheet home_date = Seets("2022 data").Cells(start_line, 1).Value away_date = Sheets("2022 data").Cells(start_line, 1).Value home_day = Sheets("2022 data").Cells(start_line, 2).Value away_day = Sheets("2022 data").Cells(start_line, 2).Value home_time = Sheets("2022 data").Cells(start_line, 3).Value away_time = Sheets("2022 data").Cells(start_line, 3).Value home_team = Sheets("2022 data").Cells(start_line, 4).Value away_team = Sheets("2022 data").Cells(start_line, 5).Value venue = Sheets("2022 data").Cells(start_line, 6).Value home_score = Sheets("2022 data").Cells(start_line, 7).Value away_score = Sheets("2022 data").Cells(start_line, 8).Value Sheets("2022 format").Cells(start_line, 1).Value = home_date Sheets("2022 format").Cells(start_line, 2).Value = home_day Sheets("2022 format").Cells(start_line, 3).Value = home_time Sheets("2022 format").Cells(start_line, 5).Value = home_team Sheets("2022 format").Cells(start_line, 6).Value = venue Sheets("2022 format").Cells(start_line + 1, 1).Value = away_date Sheets("2022 format").Cells(start_line + 1, 2).Value = away_day Sheets("2022 format").Cells(start_line + 1, 3).Value = away_time Sheets("2022 format").Cells(start_line + 1, 5).Value = away_team Sheets("2022 format").Cells(start_line + 1, 6).Value = venue Next start_line End Sub