How to show previous 10 games played

[groceryguy306]

Hello
I have a chart of hockey teams and on a different tab i would like to show the previous 10 games played for each team and who the winner was, but having games in my data field sorted in to home columns and away columns has proved difficult. I was reading some other posts and many mentioned using the "Lambda" formula, but it is not available in my version of excel

 

[groceryguy306]

Date	Visitor	Goals V	Home	Goals H	OT	Total	OV / UD	Goals	Winner
2023-10-10	Chicago Blackhawks	4	Pittsburgh Penguins	2		6	Under	4-2	Chicago Blackhawks
2023-10-10	Nashville Predators	3	Tampa Bay Lightning	5		8	Over	3-5	Tampa Bay Lightning
2023-10-10	Seattle Kraken	1	Vegas Golden Knights	4		5	Under	1-4	Vegas Golden Knights
2023-10-11	Chicago Blackhawks	1	Boston Bruins	3		4	Under	1-3	Boston Bruins
2023-10-11	Ottawa Senators	3	Carolina Hurricanes	5		8	Over	3-5	Carolina Hurricanes
2023-10-11	Winnipeg Jets	3	Calgary Flames	5		8	Over	3-5	Calgary Flames
2023-10-11	Colorado Avalanche	5	Los Angeles Kings	2		7	Over	5-2	Colorado Avalanche
2023-10-11	Montreal Canadiens	5	Toronto Maple Leafs	6	SO	11	Over	5-6	Toronto Maple Leafs
2023-10-11	Edmonton Oilers	1	Vancouver Canucks	8		9	Over	1-8	Vancouver Canucks
2023-10-12	New York Rangers	5	Buffalo Sabres	1		6	Under	5-1	New York Rangers
2023-10-12	Philadelphia Flyers	4	Columbus Blue Jackets	2		6	Under	4-2	Philadelphia Flyers
2023-10-12	St. Louis Blues	1	Dallas Stars	2	SO	3	Under	1-2	Dallas Stars
2023-10-12	Florida Panthers	0	Minnesota Wild	2		2	Under	0-2	Minnesota Wild
2023-10-12	Detroit Red Wings	3	New Jersey Devils	4		7	Over	3-4	New Jersey Devils
2023-10-12	Seattle Kraken	0	Nashville Predators	3		3	Under	0-3	Nashville Predators
2023-10-12	Vegas Golden Knights	4	San Jose Sharks	1		5	Under	4-1	Vegas Golden Knights
2023-10-13	Arizona Coyotes	4	New Jersey Devils	3	SO	7	Over	4-3	Arizona Coyotes
2023-10-13	Pittsburgh Penguins	4	Washington Capitals	0		4	Under	4-0	Pittsburgh Penguins
2023-10-14	Nashville Predators	2	Boston Bruins	3		5	Under	2-3	Boston Bruins
2023-10-14	New York Rangers	3	Columbus Blue Jackets	5		8	Over	3-5	Columbus Blue Jackets
2023-10-14	Tampa Bay Lightning	4	Detroit Red Wings	6		10	Over	4-6	Detroit Red Wings
2023-10-14	Vancouver Canucks	4	Edmonton Oilers	3		7	Over	4-3	Vancouver Canucks
2023-10-14	Carolina Hurricanes	6	Los Angeles Kings	5	SO	11	Over	6-5	Carolina Hurricanes
2023-10-14	Chicago Blackhawks	2	Montreal Canadiens	3		5	Under	2-3	Montreal Canadiens
2023-10-14	Buffalo Sabres	2	New York Islanders	3		5	Under	2-3	New York Islanders
2023-10-14	Philadelphia Flyers	2	Ottawa Senators	5		7	Over	2-5	Ottawa Senators
2023-10-14	Calgary Flames	2	Pittsburgh Penguins	5		7	Over	2-5	Pittsburgh Penguins
2023-10-14	Colorado Avalanche	2	San Jose Sharks	1	SO	3	Under	2-1	Colorado Avalanche
2023-10-14	Seattle Kraken	1	St. Louis Blues	2	SO	3	Under	1-2	St. Louis Blues
2023-10-14	Minnesota Wild	4	Toronto Maple Leafs	7		11	Over	4-7	Toronto Maple Leafs
2023-10-14	Anaheim Ducks	1	Vegas Golden Knights	4		5	Under	1-4	Vegas Golden Knights
2023-10-14	Florida Panthers	4	Winnipeg Jets	6		10	Over	4-6	Winnipeg Jets
2023-10-15	Carolina Hurricanes	3	Anaheim Ducks	6		9	Over	3-6	Anaheim Ducks

 

[Peter_SSs]

Can you manually create what the results should look like and post that with XL2BB as well?

 

[bobaol]

try this... all done with Record Macro. only written piece of the vba is changing the reference to cell AA1 so it allows the spreadsheet to become a variable instead of a fixed range. you can open the macro editor, hit F8 to see the macro run line-by-line to see exactly what it is doing. Cheers!

''assume you have these two sheets
''sheet1 is the data data you're currently showing, including the drop-down
''sheet2 will be used to do the work in the macro.

''assume cell K2 is the result of your dropdown.
 
'' assume your "Date" is in cell B5, "Visitor" in C5, "Home" in E5, etc...
''assume your data starts in B6 and below.

Sub macro12___Run_all__()
    Application.Run " Macro14"
    Application.Run " Macro22"
    Application.Run " Macro23"
    Application.Run " Macro24"
    Application.Run " Macro26"
    Application.Run " Macro28"
    Application.Run " Macro31"
    Application.Run " Macro32"
    Application.Run " Macro33"
  
    Application.Run " Macro35"
    Application.Run " Macro36"
    Application.Run " Macro38"
End Sub

Sub Macro14()
'clear all of sheet2 for a fresh start
    Sheets("Sheet2").Select
    Application.Goto Reference:="R1C1"
    ActiveCell.Columns("A:Z").EntireColumn.Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Application.Goto Reference:="R1C1"

''copy a to z of sheet1
    Sheets("Sheet1").Select
    Application.Goto Reference:="R1C1"
    ActiveCell.Columns("A:Z").EntireColumn.Select
    Selection.Copy
''paste to sheet2 as values, formats
    Sheets("Sheet2").Select
    Application.Goto Reference:="R1C1"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Sub Macro22()

'clear all of AA first
    Sheets("Sheet2").Select
    Application.Goto Reference:="R1C1"
    Application.Goto Reference:="R1C27"
    ActiveCell.Columns("A:Z").EntireColumn.Select
    Selection.Delete Shift:=xlToLeft
    Application.Goto Reference:="R1C1"
End Sub

Sub Macro23()

'find last row in data, paste in cell AA1, to be used later
    Sheets("Sheet2").Select
    Application.Goto Reference:="R1C1"
    ActiveCell.SpecialCells(xlLastCell).Select
    ActiveCell.Offset(2, 0).Range("A1").Select
    Selection.End(xlToLeft).Select
    Selection.Clear
    Selection.FormulaR1C1 = "=ROW()"
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Cut
    Application.Goto Reference:="R1C27"
    ActiveSheet.Paste
End Sub

Sub Macro24()
'set up formulas to look for teams, dates, winners
    Sheets("Sheet2").Select
    Application.Goto Reference:="R1C28"
    ActiveCell.FormulaR1C1 = "''add"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "''row_index"
    ActiveCell.Offset(-1, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "''find if true in K2"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "'visitor"
    ActiveCell.Offset(-1, 2).Range("A1").Select
    ActiveCell.FormulaR1C1 = "''find if true in K2"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "''home"
    ActiveCell.Offset(-1, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "'''find team, regardless"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "if Visitor or Home"
    ActiveCell.Offset(-1, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "'''find"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "winner"
    ActiveCell.Offset(-1, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "''find date"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "game was played"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R6C28"
    Selection.FormulaR1C1 = "=ROW()"
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.FormulaR1C1 = "=R2C11=RC[-26]"
    ActiveCell.Offset(0, 2).Range("A1").Select
    Selection.FormulaR1C1 = "=R2C11=RC[-26]"
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.FormulaR1C1 = "=IF(RC[-3]=TRUE,RC[-29],IF(RC[-1]=TRUE,RC[-27],""""))"
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-22])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.FormulaR1C1 = "=IF(RC[-2]="""","""",RC[-32])"
    Application.Goto Reference:="R6C28"
    ActiveCell.Range("A1:G1").Select
    Selection.Copy
'''    ActiveCell.Range("A1:G20").Select
''manually change this to be referencing cell AA1
    ActiveCell.Range("A1:G" & Range("aa1")).Select
    ActiveSheet.Paste
    Calculate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Sub Macro26()

'''parse AH to general format, so to get rid of the residual from the formulas
    Sheets("Sheet2").Select
    Application.Goto Reference:="R1C34"
    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
End Sub

Sub Macro28()
''''format AH as date
    Sheets("Sheet2").Select
    Application.Goto Reference:="R1C34"
    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.NumberFormat = "[$-en-US]dd-mmm-yyyy;@"
End Sub

Sub Macro31()
'select AB to AH, sort AH newest first
    Sheets("Sheet2").Select
    Application.Goto Reference:="R6C28"
'''    ActiveCell.Range("A1:G427").Select
    ActiveCell.Range("A1:G" & Range("aa1")).Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add2 Key:=ActiveCell.Offset(0, 6).Range("A1:A" & Range("aa1")), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
'''        .SetRange ActiveCell.Range("A1:G425")
        .SetRange ActiveCell.Range("A1:G" & Range("aa1"))
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Sub Macro32()
'find the most recent games in AI
    Sheets("Sheet2").Select
    Application.Goto Reference:="R1C35"
    ActiveCell.FormulaR1C1 = "'all most "
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "recent games"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R6C35"
    Selection.FormulaR1C1 = "=COUNTIF(R1C32:RC[-3],RC[-3])"
    Selection.Copy
    ActiveCell.Range("A1:A" & Range("aa1")).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Calculate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Sub Macro33()
'color the winner green
    Application.Goto Reference:="R1C33"
    ActiveCell.Range("A1:A38").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 3407718
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
End Sub


Sub Macro35()
''''find only the ten most recent games,
''if less than ten games, keep only those games
  
    Application.Goto Reference:="R1C36"
    ActiveCell.FormulaR1C1 = "'ten most "
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "recent games"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R1C37"
    ActiveCell.FormulaR1C1 = "Keep_or_Clear"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R6C36"
    Selection.FormulaR1C1 = "=IF(RC[-1]<11,RC[-4],"""")"
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.FormulaR1C1 = "=IF(RC[-1]=R2C11,""1_Keep"",""2_Clear"")"
    Application.Goto Reference:="R6C36"
    ActiveCell.Range("A1:B1").Select
    Selection.Copy
    ActiveCell.Range("A1:B" & Range("AA1")).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Calculate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub


Sub Macro36()

'clear all that is not ten games or more
    Application.Goto Reference:="R1C37"
    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.Find(What:="2_Clear", After:=ActiveCell, LookIn:=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, -1).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(0, -8).Range("A1:J" & Range("aa1")).Select
    Application.CutCopyMode = False
    Selection.Clear
End Sub



Sub Macro38()
'shrink columns, and zoom to 75% for small laptops like mine
    Application.Goto Reference:="R1C1"
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ActiveCell.Range("A1:AZ" & Range("aa1")).Select
    Selection.ColumnWidth = 0.5
    Selection.Columns.AutoFit
    ActiveWindow.Zoom = 75
    Selection.Columns.AutoFit
End Sub