I previously had this problem solved thanks to the genius of T. Valko, Rick Rothstein, and markmzz until it morphed into something bigger.
This is the final piece of the puzzle I have to solve in order to complete my spreadsheet which tracks racing results (so admittedly, this isn’t going to save the world, but I’m still quite passionate about it)
Focusing on two of the worksheets only, I have a ‘Drivers & Standings’ worksheet, and a ‘Team Standings’ worksheet.
On Drivers & Standings, Driver names are listed in B3:B37. Their associated team name is listed in D3:D37
On the Team Standings worksheet, row 1 is a header row, and then I want to do the following:
List any team names from Drivers & Standings D3:D37 in column A.
List the associated driver’s names in column B.
However, there can be between 0 and 10 drivers assigned per team, so suppose a team had 10 drivers. I would want 10 cells in column A merged with the team name displayed within. Then I would want the 10 (unmerged) adjacent cells in column B to list the 10 drivers.
On top of that, columns C through U are for the individual races, and will list the points each driver accumulated in that race, then column V will list the total team points, so again, the merge in column A will match the merge in column V. Here is an example:
Of course, on top of all that, I need it to be dynamic to account for changes in teams used, number of drivers per team, and I need it to sort by team points despite the merging. Currently the team names are pre-entered manually, and there can only be two drivers per team, and the formulas are as follows:
B2 (provided by T.Valko):
=INDEX('Drivers & Standings'!$B$3:$B$37,MATCH(A2,'Drivers & Standings'!$D$3:$D$37,0))
B3 (provided by T.Valko):
=LOOKUP(2,1/('Drivers & Standings'!$D$3:$D$37=A2),'Drivers & Standings'!$B$3:$B$37)
C2 (provided by markmzz):
=IF(ISNUMBER(MATCH(C$1,'Drivers & Standings'!$E$1:$W$1,0)),IF(ISNUMBER(MATCH($B2,'Drivers & Standings'!B$3:B$37,0)),INDEX('Drivers & Standings'!E$3:E$37,MATCH($B2,'Drivers & Standings'!B$3:B$37,0)),""),"")
It also uses the following code (provided by Rick Rothstein to unmerge, sort, and remerge:
Private Sub Worksheet_Activate()
Dim X As Long, LastRow As Long
Application.ScreenUpdating = False
' Unmerge cells
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For X = 2 To LastRow Step 2
Cells(X, "A").MergeArea.UnMerge
Cells(X + 1, "A").Value = Cells(X, "A").Value
Cells(X, "V").MergeArea.UnMerge
Cells(X + 1, "V").Value = Cells(X, "V").Value
Next
' Sort
Range("A:V").Sort Key1:=Range("V1"), Order1:=xlDescending, _
Key2:=Range("A1"), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
' Merge cells
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For X = 2 To LastRow Step 2
Application.DisplayAlerts = False
Cells(X, "A").Resize(2).Merge
Cells(X, "V").Resize(2).Merge
Application.DisplayAlerts = True
Next
Application.ScreenUpdating = True
End Sub
Of course, yeah, this is all going to change. Anyone want to give it a go?
This is the final piece of the puzzle I have to solve in order to complete my spreadsheet which tracks racing results (so admittedly, this isn’t going to save the world, but I’m still quite passionate about it)
Focusing on two of the worksheets only, I have a ‘Drivers & Standings’ worksheet, and a ‘Team Standings’ worksheet.
On Drivers & Standings, Driver names are listed in B3:B37. Their associated team name is listed in D3:D37
On the Team Standings worksheet, row 1 is a header row, and then I want to do the following:
List any team names from Drivers & Standings D3:D37 in column A.
List the associated driver’s names in column B.
However, there can be between 0 and 10 drivers assigned per team, so suppose a team had 10 drivers. I would want 10 cells in column A merged with the team name displayed within. Then I would want the 10 (unmerged) adjacent cells in column B to list the 10 drivers.
On top of that, columns C through U are for the individual races, and will list the points each driver accumulated in that race, then column V will list the total team points, so again, the merge in column A will match the merge in column V. Here is an example:
Of course, on top of all that, I need it to be dynamic to account for changes in teams used, number of drivers per team, and I need it to sort by team points despite the merging. Currently the team names are pre-entered manually, and there can only be two drivers per team, and the formulas are as follows:
B2 (provided by T.Valko):
=INDEX('Drivers & Standings'!$B$3:$B$37,MATCH(A2,'Drivers & Standings'!$D$3:$D$37,0))
B3 (provided by T.Valko):
=LOOKUP(2,1/('Drivers & Standings'!$D$3:$D$37=A2),'Drivers & Standings'!$B$3:$B$37)
C2 (provided by markmzz):
=IF(ISNUMBER(MATCH(C$1,'Drivers & Standings'!$E$1:$W$1,0)),IF(ISNUMBER(MATCH($B2,'Drivers & Standings'!B$3:B$37,0)),INDEX('Drivers & Standings'!E$3:E$37,MATCH($B2,'Drivers & Standings'!B$3:B$37,0)),""),"")
It also uses the following code (provided by Rick Rothstein to unmerge, sort, and remerge:
Private Sub Worksheet_Activate()
Dim X As Long, LastRow As Long
Application.ScreenUpdating = False
' Unmerge cells
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For X = 2 To LastRow Step 2
Cells(X, "A").MergeArea.UnMerge
Cells(X + 1, "A").Value = Cells(X, "A").Value
Cells(X, "V").MergeArea.UnMerge
Cells(X + 1, "V").Value = Cells(X, "V").Value
Next
' Sort
Range("A:V").Sort Key1:=Range("V1"), Order1:=xlDescending, _
Key2:=Range("A1"), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
' Merge cells
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For X = 2 To LastRow Step 2
Application.DisplayAlerts = False
Cells(X, "A").Resize(2).Merge
Cells(X, "V").Resize(2).Merge
Application.DisplayAlerts = True
Next
Application.ScreenUpdating = True
End Sub
Of course, yeah, this is all going to change. Anyone want to give it a go?
