Combine Lists with different formulas?

bet1734

New Member
Joined
Mar 6, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
I have been working on a spreadsheet for tracking the MLB season scores. I manually enter in the date of the game (A), Away Team (B), Home Team in (C), Final Away Score (D), and Home Score in (E).

For simplicity's sake I've only used one team from here, San Francisco Giants SFG.
I have managed to use the Match function to have the sheet pull the row for each time a team plays whether they are away (H) or home (I).

From there, I have the Index function pull the date of the game (K), the teams score when they are away (L), their opponents score (M), and then calculate if they won or lost (N).
Separate table created to calculate the same thing when the team is home (P-S)

My question now, is there a way to combine these different calculations / tables into one list that will continue the calculations as new games are added?

San Francisco has played 5 games so far, 4 away and 1 at home.
I would like to combine these two lists (K-N & P-S) so it continues to pull the row, date, and scores but so I can have it in one table (U-X) so I can view everything by date.

If there's an easier way to do this let me know!

Baseball 2020.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1DateAway TeamHome TeamAway ScoreHome ScoreGiants Game RowsGiants AwayGiants HomeCombined Home & Away
223-JulSFGLAD18SFGDateSFGOpW/LDateSFGOpW/LDateSFGOpW/L
323-JulNYYWSN4112677/23/202018L7/28/202035L
424-JulBALBOS213210#N/A7/24/202019L#N/A#N/A#N/A#N/A
524-JulMINCHW105324#N/A7/25/202054W#N/A#N/A#N/A#N/A
624-JulMILCHC03439#N/A7/26/202031W#N/A#N/A#N/A#N/A
724-JulDETCIN175#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
824-JulKCRCLE026#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
924-JulSEAHOU287#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
1024-JulSFGLAD198#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
Scores
Cell Formulas
RangeFormula
H3H3=MATCH($H$2,OFFSET($B$1,F2,0,200,1),0)+F2
I3I3=MATCH($H$2,OFFSET($C$1,G2,0,200,1),0)+G2
H4:H10H4=MATCH($H$2,OFFSET($B$1,H3,0,200,1),0)+H3
I4:I10I4=MATCH($H$2,OFFSET($C$1,I3,0,200,1),0)+I3
K3:K10K3=INDEX(MLB[Date],H3-1)
L3:L10L3=INDEX(MLB[Away Score],H3-1)
M3:M10M3=INDEX(MLB[Home Score],H3-1)
S3:S10,N3:N10N3=IF(L3>M3,"W","L")
P3:P10P3=INDEX(MLB[Date],I3-1)
Q3:Q10Q3=INDEX(MLB[Home Score],I3-1)
R3:R10R3=INDEX(MLB[Away Score],I3-1)
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,003
Office Version
  1. 2019
Platform
  1. Windows
Is this what you're looking for?
I doubt that it will ever happen, but the method I've used will give incorrect results if a team plays twice on the same date.
Book1
ABCDEFGHIJ
1DateAway TeamHome TeamAway ScoreHome ScoreCombined Home & Away
27/23/20SFGLAD18DateSFGOpW/L
37/23/20NYYWSN417/23/2018L
47/24/20BALBOS2137/24/2019L
57/24/20MINCHW105    
67/24/20MILCHC03    
77/24/20DETCIN17    
87/24/20KCRCLE02
97/24/20SEAHOU28
107/24/20SFGLAD19
Sheet7
Cell Formulas
RangeFormula
G3:G7G3=IFERROR(AGGREGATE(15,6,$A$2:$A$10/($B$2:$C$10=$H$2),ROWS(G$3:G3)),"")
H3:H7H3=IF(G3="","",SUMIFS($D$2:$D$10,$A$2:$A$10,G3,$B$2:$B$10,$H$2)+SUMIFS($E$2:$E$10,$A$2:$A$10,G3,$C$2:$C$10,$H$2))
I3:I7I3=IF(G3="","",SUMIFS($E$2:$E$10,$A$2:$A$10,G3,$B$2:$B$10,$H$2)+SUMIFS($D$2:$D$10,$A$2:$A$10,G3,$C$2:$C$10,$H$2))
J3:J7J3=IF(I3="","",IF(H3>I3,"W","L"))
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,336
Here's another way:

Book1
ABCDEFUVWXY
1DateAway TeamHome TeamAway ScoreHome ScoreCombined Home & Away
27/23/2020SFGLAD18DateSFGOpW/LRow
37/23/2020NYYWSN417/23/202018L1
47/24/2020BALBOS2137/24/202019L9
57/24/2020MINCHW1057/25/202053W10
67/24/2020MILCHC037/25/202074W11
77/24/2020DETCIN17     
87/24/2020KCRCLE02     
97/24/2020SEAHOU28     
107/24/2020SFGLAD19
117/25/2020ABCSFG35
127/25/2020ABCSFG47
Sheet5
Cell Formulas
RangeFormula
U3:U9U3=IF(Y3="","",INDEX(MLB[Date],Y3))
V3:V9V3=IF(Y3="","",IF(INDEX(MLB[Away Team],Y3)=$V$2,INDEX(MLB[Away Score],1),INDEX(MLB[Home Score],Y3)))
W3:W9W3=IF(Y3="","",SUM(INDEX(MLB[[Away Score]:[Home Score]],Y3,0))-V3)
X3:X9X3=IF(Y3="","",IF(V3>W3,"W","L"))
Y3:Y9Y3=IFERROR(AGGREGATE(15,6,(ROW(MLB[Date])-ROW(INDEX(MLB[Date],1))+1)/((MLB[Away Team]=$V$2)+(MLB[Home Team]=$V$2)),ROWS($Y$2:$Y2)),"")


I added the Row column, which you can hide. The advantage of this version is that it handles double-headers.
 

bet1734

New Member
Joined
Mar 6, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Looks like this will work! Thank you both for the help!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,336
Oops, looks like I had a typo in the V formula, it should be:

=IF(Y3="","",IF(INDEX(MLB[Away Team],Y3)=$V$2,INDEX(MLB[Away Score],Y3),INDEX(MLB[Home Score],Y3)))

Anyway, glad we could help! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,098
Messages
5,545,951
Members
410,713
Latest member
TaremyLunsil
Top