# Combine Lists with different formulas?

#### bet1734

##### New Member
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
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
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)

### 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
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
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
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
Here's another way:

Book1
ABCDEFUVWXY
1DateAway TeamHome TeamAway ScoreHome ScoreCombined Home & Away
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
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
Looks like this will work! Thank you both for the help!

#### Eric W

##### MrExcel MVP
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)))

Replies
5
Views
98
Replies
3
Views
89
Replies
6
Views
50
Replies
1
Views
54
Replies
9
Views
130