|
Matching data in two columns
Hello,
I need help with finding the last game that two teams played each other, either home or away. This is to decide who will have the home town advantage in the semi-finals. What formula would be able to find the last time two teams played each other in a season (either home (Team A vs Team B) or away (Team B v Team A)), and extract the score for the last game for both teams? Thank you and best regards Max |
Matching data in two columns
We would need to know how your data is set out.
best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Max" wrote in message ... Hello, I need help with finding the last game that two teams played each other, either home or away. This is to decide who will have the home town advantage in the semi-finals. What formula would be able to find the last time two teams played each other in a season (either home (Team A vs Team B) or away (Team B v Team A)), and extract the score for the last game for both teams? Thank you and best regards Max |
Matching data in two columns
Hi Max ,
Pl show the sample of your data base so that soln can be given. H S Shastri ++++++++++++++++++++++++++++++++++++++++++++++++++ + "Max" wrote: Hello, I need help with finding the last game that two teams played each other, either home or away. This is to decide who will have the home town advantage in the semi-finals. What formula would be able to find the last time two teams played each other in a season (either home (Team A vs Team B) or away (Team B v Team A)), and extract the score for the last game for both teams? Thank you and best regards Max |
Matching data in two columns
Hello All,
In column A is the list of the Home Teams in Column B is the Home team score in column C is the Away Team score and Column d is the Away Teams Eg: HOME HT Score AT Score AWAY Highlanders 31 33 Brumbies Force 19 25 Blues Lions 34 28 Cheetahs Crusaders 19 13 Chiefs Hurricanes 22 26 Waratahs Stormers 15 20 Sharks Bulls 33 20 Reds Hurricanes 22 17 Highlanders Waratahs 11 7 Chiefs Force 16 10 Cheetahs Stormers 27 24 Reds Brumbies 18 16 Crusaders Bulls 59 26 Blues Sharks 25 10 Lions The lists will be for the whole seasons games, so the teams are not sorted. Thank you Max "Bernard Liengme" wrote: We would need to know how your data is set out. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Max" wrote in message ... Hello, I need help with finding the last game that two teams played each other, either home or away. This is to decide who will have the home town advantage in the semi-finals. What formula would be able to find the last time two teams played each other in a season (either home (Team A vs Team B) or away (Team B v Team A)), and extract the score for the last game for both teams? Thank you and best regards Max |
Matching data in two columns
Hi Max,
Assuming your data is as per follows, A B c TEAM A TEAM B 1/1/09 TEAM A TEAM C 5/1/09 TEAM A TEAM C 10/2/09 TEAM B TEAM C 2/6/09 Put following array formula in any relevant cell {=MAX(IF(A1:A3="TEAM A",IF(B1:B3="TEAM B",C1:C3,0),0))} H S SHASTRI +++++++++++++++++++++++++++++++++++++++++++++++++ "Max" wrote: Hello, I need help with finding the last game that two teams played each other, either home or away. This is to decide who will have the home town advantage in the semi-finals. What formula would be able to find the last time two teams played each other in a season (either home (Team A vs Team B) or away (Team B v Team A)), and extract the score for the last game for both teams? Thank you and best regards Max |
Matching data in two columns
Hello Harshawardhan,
Thank you for your help. What your example does not take into account is that all the teams will also be playing games as an Away team ie: they will also appear in both columns. These teams play each other only once during a season. I did try and post the actual spreadsheet but it appears that is has not shown up on the forum page. I could e-mail it to you directly if this is possible. Thanks again for your help. Max "HARSHAWARDHAN. S .SHASTRI" wrote: Hi Max, Assuming your data is as per follows, A B c TEAM A TEAM B 1/1/09 TEAM A TEAM C 5/1/09 TEAM A TEAM C 10/2/09 TEAM B TEAM C 2/6/09 Put following array formula in any relevant cell {=MAX(IF(A1:A3="TEAM A",IF(B1:B3="TEAM B",C1:C3,0),0))} H S SHASTRI +++++++++++++++++++++++++++++++++++++++++++++++++ "Max" wrote: Hello, I need help with finding the last game that two teams played each other, either home or away. This is to decide who will have the home town advantage in the semi-finals. What formula would be able to find the last time two teams played each other in a season (either home (Team A vs Team B) or away (Team B v Team A)), and extract the score for the last game for both teams? Thank you and best regards Max |
Matching data in two columns
Hi Max,
Seen your data. I have tried in following manner and getting desired results. Insert column before HOME column ie new insered column will be A. put the name of desired teams in cell H1 and I1 and then put following formula in cell A1 and drag it =IF(OR(AND(B1=$H$1,E1=$I$1),AND(B1=$I$1,E1=$H$1)), ROW(),"") in cell J1 put following formula =VLOOKUP(MAX(A1:A1000),A1:C1000,3,FALSE) and in cell K1 cell put following formula =VLOOKUP(MAX(A1:A1000),A1:D1000,4,FALSE) You will get desired results. H S Shastri ++++++++++++++++++++++++++++++++++++++++++++++++++ ++ "Max" wrote: Hello Harshawardhan, Thank you for your help. What your example does not take into account is that all the teams will also be playing games as an Away team ie: they will also appear in both columns. These teams play each other only once during a season. I did try and post the actual spreadsheet but it appears that is has not shown up on the forum page. I could e-mail it to you directly if this is possible. Thanks again for your help. Max "HARSHAWARDHAN. S .SHASTRI" wrote: Hi Max, Assuming your data is as per follows, A B c TEAM A TEAM B 1/1/09 TEAM A TEAM C 5/1/09 TEAM A TEAM C 10/2/09 TEAM B TEAM C 2/6/09 Put following array formula in any relevant cell {=MAX(IF(A1:A3="TEAM A",IF(B1:B3="TEAM B",C1:C3,0),0))} H S SHASTRI +++++++++++++++++++++++++++++++++++++++++++++++++ "Max" wrote: Hello, I need help with finding the last game that two teams played each other, either home or away. This is to decide who will have the home town advantage in the semi-finals. What formula would be able to find the last time two teams played each other in a season (either home (Team A vs Team B) or away (Team B v Team A)), and extract the score for the last game for both teams? Thank you and best regards Max |
Matching data in two columns
Hello Harshawardhan,
Thank you it worked well. I made a small change, swopped the vlookup to us an INDEX. Your idea worked well. Thnak you and best regards Max "HARSHAWARDHAN. S .SHASTRI" wrote: Hi Max, Seen your data. I have tried in following manner and getting desired results. Insert column before HOME column ie new insered column will be A. put the name of desired teams in cell H1 and I1 and then put following formula in cell A1 and drag it =IF(OR(AND(B1=$H$1,E1=$I$1),AND(B1=$I$1,E1=$H$1)), ROW(),"") in cell J1 put following formula =VLOOKUP(MAX(A1:A1000),A1:C1000,3,FALSE) and in cell K1 cell put following formula =VLOOKUP(MAX(A1:A1000),A1:D1000,4,FALSE) You will get desired results. H S Shastri ++++++++++++++++++++++++++++++++++++++++++++++++++ ++ "Max" wrote: Hello Harshawardhan, Thank you for your help. What your example does not take into account is that all the teams will also be playing games as an Away team ie: they will also appear in both columns. These teams play each other only once during a season. I did try and post the actual spreadsheet but it appears that is has not shown up on the forum page. I could e-mail it to you directly if this is possible. Thanks again for your help. Max "HARSHAWARDHAN. S .SHASTRI" wrote: Hi Max, Assuming your data is as per follows, A B c TEAM A TEAM B 1/1/09 TEAM A TEAM C 5/1/09 TEAM A TEAM C 10/2/09 TEAM B TEAM C 2/6/09 Put following array formula in any relevant cell {=MAX(IF(A1:A3="TEAM A",IF(B1:B3="TEAM B",C1:C3,0),0))} H S SHASTRI +++++++++++++++++++++++++++++++++++++++++++++++++ "Max" wrote: Hello, I need help with finding the last game that two teams played each other, either home or away. This is to decide who will have the home town advantage in the semi-finals. What formula would be able to find the last time two teams played each other in a season (either home (Team A vs Team B) or away (Team B v Team A)), and extract the score for the last game for both teams? Thank you and best regards Max |
Matching data in two columns
Hi,
Suppose your raw data start in A1:D100 with titles on the first row. Enter one teams name in F1 and one in G1, (Bulls, Blues) The formula for the two scores of the final game regardless or who is the home team a =INDEX(B2:B100,MAX((A2:A100=F1)*(D2:D100=G1)*ROW(A 2:A100))) =INDEX(C2:C100,MAX((A2:A100=F1)*(D2:D100=G1)*ROW(A 2:A100))) These are array formulas so you need to enter them by pressing Shift+Ctrl+Enter not by pressing Enter. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Max" wrote: Hello, I need help with finding the last game that two teams played each other, either home or away. This is to decide who will have the home town advantage in the semi-finals. What formula would be able to find the last time two teams played each other in a season (either home (Team A vs Team B) or away (Team B v Team A)), and extract the score for the last game for both teams? Thank you and best regards Max |
Matching data in two columns
Do you have dates associated with the games?
Will the last game be the game towards the bottom of the list? -- Biff Microsoft Excel MVP "Max" wrote in message ... Hello All, In column A is the list of the Home Teams in Column B is the Home team score in column C is the Away Team score and Column d is the Away Teams Eg: HOME HT Score AT Score AWAY Highlanders 31 33 Brumbies Force 19 25 Blues Lions 34 28 Cheetahs Crusaders 19 13 Chiefs Hurricanes 22 26 Waratahs Stormers 15 20 Sharks Bulls 33 20 Reds Hurricanes 22 17 Highlanders Waratahs 11 7 Chiefs Force 16 10 Cheetahs Stormers 27 24 Reds Brumbies 18 16 Crusaders Bulls 59 26 Blues Sharks 25 10 Lions The lists will be for the whole seasons games, so the teams are not sorted. Thank you Max "Bernard Liengme" wrote: We would need to know how your data is set out. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Max" wrote in message ... Hello, I need help with finding the last game that two teams played each other, either home or away. This is to decide who will have the home town advantage in the semi-finals. What formula would be able to find the last time two teams played each other in a season (either home (Team A vs Team B) or away (Team B v Team A)), and extract the score for the last game for both teams? Thank you and best regards Max |
Matching data in two columns
Try this:
Assuming your data start in A1:A15 header in row 1 Criteria: F1, and G1 (Home and Away) In F2: =LOOKUP(2,1/(($A$2:$A$15=$F$1)*($D$2:$D$15=$G$1)),B2:B15) Normally ENTER, copy across to G2 "Max" wrote: Hello, I need help with finding the last game that two teams played each other, either home or away. This is to decide who will have the home town advantage in the semi-finals. What formula would be able to find the last time two teams played each other in a season (either home (Team A vs Team B) or away (Team B v Team A)), and extract the score for the last game for both teams? Thank you and best regards Max |
Matching data in two columns
Hello Toothless Mama,
Your formula works well. I would like to make an alteration if possible. The data in A1:A15 (actually A4:A101) gets calculated into a league table. Positions 1, 2, 3, 4 in the league table, go through to the semi-finals. 1 plays 4 and 2 plays 3. Lets look at teams placed in 1 and 4. These would be the teams that would be displayed in F1 and G1 in your formula. The adjustment I would like to make is to link directly F1 and G1 to the table. This would mean that as the table changes when games are played, the teams that appear at the 2 places in the table would change. The problem is that they could appear as Bulls(1) and Sharks(4), but in the fixtures list the Bulls only play away to the Sharks. This means they would have to be switched around for your formula. Is it possible to adjust your formula to make provision for these possibilities? "Teethless mama" wrote: Try this: Assuming your data start in A1:A15 header in row 1 Criteria: F1, and G1 (Home and Away) In F2: =LOOKUP(2,1/(($A$2:$A$15=$F$1)*($D$2:$D$15=$G$1)),B2:B15) Normally ENTER, copy across to G2 "Max" wrote: Hello, I need help with finding the last game that two teams played each other, either home or away. This is to decide who will have the home town advantage in the semi-finals. What formula would be able to find the last time two teams played each other in a season (either home (Team A vs Team B) or away (Team B v Team A)), and extract the score for the last game for both teams? Thank you and best regards Max |
Matching data in two columns
Hello Biff,
I apologise i missed your note. Yes they do have dates that range over a 4 months period with games being palyed Fridays and Saturdays, with different times and in different countries. Yes the last game will be the last on the list. I wonder if you could look at the answer I received from "Teethless Mama". His formula worked the way I liked best. Could you look at the answer I gave and perhaps help with a formula there. I will copy the answer I gave to "Teethless Mama" below. (Hello Toothless Mama, Your formula works well. I would like to make an alteration if possible. The data in A1:A15 (actually A4:A101) gets calculated into a league table. Positions 1, 2, 3, 4 in the league table, go through to the semi-finals. 1 plays 4 and 2 plays 3. Lets look at teams placed in 1 and 4. These would be the teams that would be displayed in F1 and G1 in your formula. The adjustment I would like to make is to link directly F1 and G1 to the table. This would mean that as the table changes when games are played, the teams that appear at the 2 places in the table would change. The problem is that they could appear as Bulls(1) and Sharks(4), but in the fixtures list the Bulls only play away to the Sharks. This means they would have to be switched around for your formula. Is it possible to adjust your formula to make provision for these possibilities? ) My mistake is I assumed everybody knows how a league table would work. Thank you an best regards Max "T. Valko" wrote: Do you have dates associated with the games? Will the last game be the game towards the bottom of the list? -- Biff Microsoft Excel MVP "Max" wrote in message ... Hello All, In column A is the list of the Home Teams in Column B is the Home team score in column C is the Away Team score and Column d is the Away Teams Eg: HOME HT Score AT Score AWAY Highlanders 31 33 Brumbies Force 19 25 Blues Lions 34 28 Cheetahs Crusaders 19 13 Chiefs Hurricanes 22 26 Waratahs Stormers 15 20 Sharks Bulls 33 20 Reds Hurricanes 22 17 Highlanders Waratahs 11 7 Chiefs Force 16 10 Cheetahs Stormers 27 24 Reds Brumbies 18 16 Crusaders Bulls 59 26 Blues Sharks 25 10 Lions The lists will be for the whole seasons games, so the teams are not sorted. Thank you Max "Bernard Liengme" wrote: We would need to know how your data is set out. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Max" wrote in message ... Hello, I need help with finding the last game that two teams played each other, either home or away. This is to decide who will have the home town advantage in the semi-finals. What formula would be able to find the last time two teams played each other in a season (either home (Team A vs Team B) or away (Team B v Team A)), and extract the score for the last game for both teams? Thank you and best regards Max |
Matching data in two columns
I must be missing something.
Here's my understanding of your data: Team1.....10.....20.....Team4 Team2.....12.....10.....Team3 Team4.....17.....15.....Team1 Team3.....10.....22.....Team2 You want to lookup the *last* entries for Team1 and Team4 in *any order* (home-away or away-home) I wonder if you could look at the answer I received from "Teethless Mama". His formula worked the way I liked best. Using TM's formula, if F1 = Team1 and G1 = Team4 the results returned are 10 and 20. Shouldn't the results be 17 and 15? -- Biff Microsoft Excel MVP "Max" wrote in message ... Hello Biff, I apologise i missed your note. Yes they do have dates that range over a 4 months period with games being palyed Fridays and Saturdays, with different times and in different countries. Yes the last game will be the last on the list. I wonder if you could look at the answer I received from "Teethless Mama". His formula worked the way I liked best. Could you look at the answer I gave and perhaps help with a formula there. I will copy the answer I gave to "Teethless Mama" below. (Hello Toothless Mama, Your formula works well. I would like to make an alteration if possible. The data in A1:A15 (actually A4:A101) gets calculated into a league table. Positions 1, 2, 3, 4 in the league table, go through to the semi-finals. 1 plays 4 and 2 plays 3. Lets look at teams placed in 1 and 4. These would be the teams that would be displayed in F1 and G1 in your formula. The adjustment I would like to make is to link directly F1 and G1 to the table. This would mean that as the table changes when games are played, the teams that appear at the 2 places in the table would change. The problem is that they could appear as Bulls(1) and Sharks(4), but in the fixtures list the Bulls only play away to the Sharks. This means they would have to be switched around for your formula. Is it possible to adjust your formula to make provision for these possibilities? ) My mistake is I assumed everybody knows how a league table would work. Thank you an best regards Max "T. Valko" wrote: Do you have dates associated with the games? Will the last game be the game towards the bottom of the list? -- Biff Microsoft Excel MVP "Max" wrote in message ... Hello All, In column A is the list of the Home Teams in Column B is the Home team score in column C is the Away Team score and Column d is the Away Teams Eg: HOME HT Score AT Score AWAY Highlanders 31 33 Brumbies Force 19 25 Blues Lions 34 28 Cheetahs Crusaders 19 13 Chiefs Hurricanes 22 26 Waratahs Stormers 15 20 Sharks Bulls 33 20 Reds Hurricanes 22 17 Highlanders Waratahs 11 7 Chiefs Force 16 10 Cheetahs Stormers 27 24 Reds Brumbies 18 16 Crusaders Bulls 59 26 Blues Sharks 25 10 Lions The lists will be for the whole seasons games, so the teams are not sorted. Thank you Max "Bernard Liengme" wrote: We would need to know how your data is set out. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Max" wrote in message ... Hello, I need help with finding the last game that two teams played each other, either home or away. This is to decide who will have the home town advantage in the semi-finals. What formula would be able to find the last time two teams played each other in a season (either home (Team A vs Team B) or away (Team B v Team A)), and extract the score for the last game for both teams? Thank you and best regards Max |
Matching data in two columns
Yes please Biff, in any order. That would be perfect.
This is because the teams only play each other once and not the normal home and away basis. Thank you for you response and help. Max "T. Valko" wrote: I must be missing something. Here's my understanding of your data: Team1.....10.....20.....Team4 Team2.....12.....10.....Team3 Team4.....17.....15.....Team1 Team3.....10.....22.....Team2 You want to lookup the *last* entries for Team1 and Team4 in *any order* (home-away or away-home) I wonder if you could look at the answer I received from "Teethless Mama". His formula worked the way I liked best. Using TM's formula, if F1 = Team1 and G1 = Team4 the results returned are 10 and 20. Shouldn't the results be 17 and 15? -- Biff Microsoft Excel MVP "Max" wrote in message ... Hello Biff, I apologise i missed your note. Yes they do have dates that range over a 4 months period with games being palyed Fridays and Saturdays, with different times and in different countries. Yes the last game will be the last on the list. I wonder if you could look at the answer I received from "Teethless Mama". His formula worked the way I liked best. Could you look at the answer I gave and perhaps help with a formula there. I will copy the answer I gave to "Teethless Mama" below. (Hello Toothless Mama, Your formula works well. I would like to make an alteration if possible. The data in A1:A15 (actually A4:A101) gets calculated into a league table. Positions 1, 2, 3, 4 in the league table, go through to the semi-finals. 1 plays 4 and 2 plays 3. Lets look at teams placed in 1 and 4. These would be the teams that would be displayed in F1 and G1 in your formula. The adjustment I would like to make is to link directly F1 and G1 to the table. This would mean that as the table changes when games are played, the teams that appear at the 2 places in the table would change. The problem is that they could appear as Bulls(1) and Sharks(4), but in the fixtures list the Bulls only play away to the Sharks. This means they would have to be switched around for your formula. Is it possible to adjust your formula to make provision for these possibilities? ) My mistake is I assumed everybody knows how a league table would work. Thank you an best regards Max "T. Valko" wrote: Do you have dates associated with the games? Will the last game be the game towards the bottom of the list? -- Biff Microsoft Excel MVP "Max" wrote in message ... Hello All, In column A is the list of the Home Teams in Column B is the Home team score in column C is the Away Team score and Column d is the Away Teams Eg: HOME HT Score AT Score AWAY Highlanders 31 33 Brumbies Force 19 25 Blues Lions 34 28 Cheetahs Crusaders 19 13 Chiefs Hurricanes 22 26 Waratahs Stormers 15 20 Sharks Bulls 33 20 Reds Hurricanes 22 17 Highlanders Waratahs 11 7 Chiefs Force 16 10 Cheetahs Stormers 27 24 Reds Brumbies 18 16 Crusaders Bulls 59 26 Blues Sharks 25 10 Lions The lists will be for the whole seasons games, so the teams are not sorted. Thank you Max "Bernard Liengme" wrote: We would need to know how your data is set out. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Max" wrote in message ... Hello, I need help with finding the last game that two teams played each other, either home or away. This is to decide who will have the home town advantage in the semi-finals. What formula would be able to find the last time two teams played each other in a season (either home (Team A vs Team B) or away (Team B v Team A)), and extract the score for the last game for both teams? Thank you and best regards Max |
Matching data in two columns
Hello Biff,
Sorry I did not read the last part of your message. "Using TM's formula, if F1 = Team1 and G1 = Team 4 the results returned are 10 and 20. Shouldn't the results be 17 and 15?" No it's the team names that must be either Team 1 in postion 1 and Team 4 in position 1 or Team 4 in postion 1 and Team 1 in postion 4. The scores who be as the teams are show with there relevant scores. I apologise for missing this important part. Thank you again. Max "T. Valko" wrote: I must be missing something. Here's my understanding of your data: Team1.....10.....20.....Team4 Team2.....12.....10.....Team3 Team4.....17.....15.....Team1 Team3.....10.....22.....Team2 You want to lookup the *last* entries for Team1 and Team4 in *any order* (home-away or away-home) I wonder if you could look at the answer I received from "Teethless Mama". His formula worked the way I liked best. Using TM's formula, if F1 = Team1 and G1 = Team4 the results returned are 10 and 20. Shouldn't the results be 17 and 15? -- Biff Microsoft Excel MVP "Max" wrote in message ... Hello Biff, I apologise i missed your note. Yes they do have dates that range over a 4 months period with games being palyed Fridays and Saturdays, with different times and in different countries. Yes the last game will be the last on the list. I wonder if you could look at the answer I received from "Teethless Mama". His formula worked the way I liked best. Could you look at the answer I gave and perhaps help with a formula there. I will copy the answer I gave to "Teethless Mama" below. (Hello Toothless Mama, Your formula works well. I would like to make an alteration if possible. The data in A1:A15 (actually A4:A101) gets calculated into a league table. Positions 1, 2, 3, 4 in the league table, go through to the semi-finals. 1 plays 4 and 2 plays 3. Lets look at teams placed in 1 and 4. These would be the teams that would be displayed in F1 and G1 in your formula. The adjustment I would like to make is to link directly F1 and G1 to the table. This would mean that as the table changes when games are played, the teams that appear at the 2 places in the table would change. The problem is that they could appear as Bulls(1) and Sharks(4), but in the fixtures list the Bulls only play away to the Sharks. This means they would have to be switched around for your formula. Is it possible to adjust your formula to make provision for these possibilities? ) My mistake is I assumed everybody knows how a league table would work. Thank you an best regards Max "T. Valko" wrote: Do you have dates associated with the games? Will the last game be the game towards the bottom of the list? -- Biff Microsoft Excel MVP "Max" wrote in message ... Hello All, In column A is the list of the Home Teams in Column B is the Home team score in column C is the Away Team score and Column d is the Away Teams Eg: HOME HT Score AT Score AWAY Highlanders 31 33 Brumbies Force 19 25 Blues Lions 34 28 Cheetahs Crusaders 19 13 Chiefs Hurricanes 22 26 Waratahs Stormers 15 20 Sharks Bulls 33 20 Reds Hurricanes 22 17 Highlanders Waratahs 11 7 Chiefs Force 16 10 Cheetahs Stormers 27 24 Reds Brumbies 18 16 Crusaders Bulls 59 26 Blues Sharks 25 10 Lions The lists will be for the whole seasons games, so the teams are not sorted. Thank you Max "Bernard Liengme" wrote: We would need to know how your data is set out. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Max" wrote in message ... Hello, I need help with finding the last game that two teams played each other, either home or away. This is to decide who will have the home town advantage in the semi-finals. What formula would be able to find the last time two teams played each other in a season (either home (Team A vs Team B) or away (Team B v Team A)), and extract the score for the last game for both teams? Thank you and best regards Max |
Matching data in two columns
Ok, try this...
Assume: A2:A15 = home team name B2:B15 = home team score C2:C15 = away team score D2:D15 = away team name F2 = a team name G2 = a team name These can be in any order: Blues.....Bulls Bulls......Blues In the formulas** I've used named ranges: HT (home team) refers to A2:A15 HS (home score) refers to B2:B15 AS (away score) refers to C2:C15 AT (away team) refers to D2:D15 Enter this array formula** in F3: =SUM((HT=F2)*(AT=G2)*HS)+SUM((AT=F2)*(HT=G2)*AS) Enter this array formula** in G3: =SUM((HT=G2)*(AT=F2)*HS)+SUM((AT=G2)*(HT=F2)*AS) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Max" wrote in message ... Hello Biff, Sorry I did not read the last part of your message. "Using TM's formula, if F1 = Team1 and G1 = Team 4 the results returned are 10 and 20. Shouldn't the results be 17 and 15?" No it's the team names that must be either Team 1 in postion 1 and Team 4 in position 1 or Team 4 in postion 1 and Team 1 in postion 4. The scores who be as the teams are show with there relevant scores. I apologise for missing this important part. Thank you again. Max "T. Valko" wrote: I must be missing something. Here's my understanding of your data: Team1.....10.....20.....Team4 Team2.....12.....10.....Team3 Team4.....17.....15.....Team1 Team3.....10.....22.....Team2 You want to lookup the *last* entries for Team1 and Team4 in *any order* (home-away or away-home) I wonder if you could look at the answer I received from "Teethless Mama". His formula worked the way I liked best. Using TM's formula, if F1 = Team1 and G1 = Team4 the results returned are 10 and 20. Shouldn't the results be 17 and 15? -- Biff Microsoft Excel MVP "Max" wrote in message ... Hello Biff, I apologise i missed your note. Yes they do have dates that range over a 4 months period with games being palyed Fridays and Saturdays, with different times and in different countries. Yes the last game will be the last on the list. I wonder if you could look at the answer I received from "Teethless Mama". His formula worked the way I liked best. Could you look at the answer I gave and perhaps help with a formula there. I will copy the answer I gave to "Teethless Mama" below. (Hello Toothless Mama, Your formula works well. I would like to make an alteration if possible. The data in A1:A15 (actually A4:A101) gets calculated into a league table. Positions 1, 2, 3, 4 in the league table, go through to the semi-finals. 1 plays 4 and 2 plays 3. Lets look at teams placed in 1 and 4. These would be the teams that would be displayed in F1 and G1 in your formula. The adjustment I would like to make is to link directly F1 and G1 to the table. This would mean that as the table changes when games are played, the teams that appear at the 2 places in the table would change. The problem is that they could appear as Bulls(1) and Sharks(4), but in the fixtures list the Bulls only play away to the Sharks. This means they would have to be switched around for your formula. Is it possible to adjust your formula to make provision for these possibilities? ) My mistake is I assumed everybody knows how a league table would work. Thank you an best regards Max "T. Valko" wrote: Do you have dates associated with the games? Will the last game be the game towards the bottom of the list? -- Biff Microsoft Excel MVP "Max" wrote in message ... Hello All, In column A is the list of the Home Teams in Column B is the Home team score in column C is the Away Team score and Column d is the Away Teams Eg: HOME HT Score AT Score AWAY Highlanders 31 33 Brumbies Force 19 25 Blues Lions 34 28 Cheetahs Crusaders 19 13 Chiefs Hurricanes 22 26 Waratahs Stormers 15 20 Sharks Bulls 33 20 Reds Hurricanes 22 17 Highlanders Waratahs 11 7 Chiefs Force 16 10 Cheetahs Stormers 27 24 Reds Brumbies 18 16 Crusaders Bulls 59 26 Blues Sharks 25 10 Lions The lists will be for the whole seasons games, so the teams are not sorted. Thank you Max "Bernard Liengme" wrote: We would need to know how your data is set out. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Max" wrote in message ... Hello, I need help with finding the last game that two teams played each other, either home or away. This is to decide who will have the home town advantage in the semi-finals. What formula would be able to find the last time two teams played each other in a season (either home (Team A vs Team B) or away (Team B v Team A)), and extract the score for the last game for both teams? Thank you and best regards Max |
Matching data in two columns
Hello Biff,
That is excellent, the formulas work perfectly. Thank you very much and much appreciated. Max "T. Valko" wrote: Ok, try this... Assume: A2:A15 = home team name B2:B15 = home team score C2:C15 = away team score D2:D15 = away team name F2 = a team name G2 = a team name These can be in any order: Blues.....Bulls Bulls......Blues In the formulas** I've used named ranges: HT (home team) refers to A2:A15 HS (home score) refers to B2:B15 AS (away score) refers to C2:C15 AT (away team) refers to D2:D15 Enter this array formula** in F3: =SUM((HT=F2)*(AT=G2)*HS)+SUM((AT=F2)*(HT=G2)*AS) Enter this array formula** in G3: =SUM((HT=G2)*(AT=F2)*HS)+SUM((AT=G2)*(HT=F2)*AS) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Max" wrote in message ... Hello Biff, Sorry I did not read the last part of your message. "Using TM's formula, if F1 = Team1 and G1 = Team 4 the results returned are 10 and 20. Shouldn't the results be 17 and 15?" No it's the team names that must be either Team 1 in postion 1 and Team 4 in position 1 or Team 4 in postion 1 and Team 1 in postion 4. The scores who be as the teams are show with there relevant scores. I apologise for missing this important part. Thank you again. Max "T. Valko" wrote: I must be missing something. Here's my understanding of your data: Team1.....10.....20.....Team4 Team2.....12.....10.....Team3 Team4.....17.....15.....Team1 Team3.....10.....22.....Team2 You want to lookup the *last* entries for Team1 and Team4 in *any order* (home-away or away-home) I wonder if you could look at the answer I received from "Teethless Mama". His formula worked the way I liked best. Using TM's formula, if F1 = Team1 and G1 = Team4 the results returned are 10 and 20. Shouldn't the results be 17 and 15? -- Biff Microsoft Excel MVP "Max" wrote in message ... Hello Biff, I apologise i missed your note. Yes they do have dates that range over a 4 months period with games being palyed Fridays and Saturdays, with different times and in different countries. Yes the last game will be the last on the list. I wonder if you could look at the answer I received from "Teethless Mama". His formula worked the way I liked best. Could you look at the answer I gave and perhaps help with a formula there. I will copy the answer I gave to "Teethless Mama" below. (Hello Toothless Mama, Your formula works well. I would like to make an alteration if possible. The data in A1:A15 (actually A4:A101) gets calculated into a league table. Positions 1, 2, 3, 4 in the league table, go through to the semi-finals. 1 plays 4 and 2 plays 3. Lets look at teams placed in 1 and 4. These would be the teams that would be displayed in F1 and G1 in your formula. The adjustment I would like to make is to link directly F1 and G1 to the table. This would mean that as the table changes when games are played, the teams that appear at the 2 places in the table would change. The problem is that they could appear as Bulls(1) and Sharks(4), but in the fixtures list the Bulls only play away to the Sharks. This means they would have to be switched around for your formula. Is it possible to adjust your formula to make provision for these possibilities? ) My mistake is I assumed everybody knows how a league table would work. Thank you an best regards Max "T. Valko" wrote: Do you have dates associated with the games? Will the last game be the game towards the bottom of the list? -- Biff Microsoft Excel MVP "Max" wrote in message ... Hello All, In column A is the list of the Home Teams in Column B is the Home team score in column C is the Away Team score and Column d is the Away Teams Eg: HOME HT Score AT Score AWAY Highlanders 31 33 Brumbies Force 19 25 Blues Lions 34 28 Cheetahs Crusaders 19 13 Chiefs Hurricanes 22 26 Waratahs Stormers 15 20 Sharks Bulls 33 20 Reds Hurricanes 22 17 Highlanders Waratahs 11 7 Chiefs Force 16 10 Cheetahs Stormers 27 24 Reds Brumbies 18 16 Crusaders Bulls 59 26 Blues Sharks 25 10 Lions The lists will be for the whole seasons games, so the teams are not sorted. Thank you Max "Bernard Liengme" wrote: We would need to know how your data is set out. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Max" wrote in message ... Hello, I need help with finding the last game that two teams played each other, either home or away. This is to decide who will have the home town advantage in the semi-finals. What formula would be able to find the last time two teams played each other in a season (either home (Team A vs Team B) or away (Team B v Team A)), and extract the score for the last game for both teams? Thank you and best regards Max |
Matching data in two columns
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Max" wrote in message ... Hello Biff, That is excellent, the formulas work perfectly. Thank you very much and much appreciated. Max "T. Valko" wrote: Ok, try this... Assume: A2:A15 = home team name B2:B15 = home team score C2:C15 = away team score D2:D15 = away team name F2 = a team name G2 = a team name These can be in any order: Blues.....Bulls Bulls......Blues In the formulas** I've used named ranges: HT (home team) refers to A2:A15 HS (home score) refers to B2:B15 AS (away score) refers to C2:C15 AT (away team) refers to D2:D15 Enter this array formula** in F3: =SUM((HT=F2)*(AT=G2)*HS)+SUM((AT=F2)*(HT=G2)*AS) Enter this array formula** in G3: =SUM((HT=G2)*(AT=F2)*HS)+SUM((AT=G2)*(HT=F2)*AS) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Max" wrote in message ... Hello Biff, Sorry I did not read the last part of your message. "Using TM's formula, if F1 = Team1 and G1 = Team 4 the results returned are 10 and 20. Shouldn't the results be 17 and 15?" No it's the team names that must be either Team 1 in postion 1 and Team 4 in position 1 or Team 4 in postion 1 and Team 1 in postion 4. The scores who be as the teams are show with there relevant scores. I apologise for missing this important part. Thank you again. Max "T. Valko" wrote: I must be missing something. Here's my understanding of your data: Team1.....10.....20.....Team4 Team2.....12.....10.....Team3 Team4.....17.....15.....Team1 Team3.....10.....22.....Team2 You want to lookup the *last* entries for Team1 and Team4 in *any order* (home-away or away-home) I wonder if you could look at the answer I received from "Teethless Mama". His formula worked the way I liked best. Using TM's formula, if F1 = Team1 and G1 = Team4 the results returned are 10 and 20. Shouldn't the results be 17 and 15? -- Biff Microsoft Excel MVP "Max" wrote in message ... Hello Biff, I apologise i missed your note. Yes they do have dates that range over a 4 months period with games being palyed Fridays and Saturdays, with different times and in different countries. Yes the last game will be the last on the list. I wonder if you could look at the answer I received from "Teethless Mama". His formula worked the way I liked best. Could you look at the answer I gave and perhaps help with a formula there. I will copy the answer I gave to "Teethless Mama" below. (Hello Toothless Mama, Your formula works well. I would like to make an alteration if possible. The data in A1:A15 (actually A4:A101) gets calculated into a league table. Positions 1, 2, 3, 4 in the league table, go through to the semi-finals. 1 plays 4 and 2 plays 3. Lets look at teams placed in 1 and 4. These would be the teams that would be displayed in F1 and G1 in your formula. The adjustment I would like to make is to link directly F1 and G1 to the table. This would mean that as the table changes when games are played, the teams that appear at the 2 places in the table would change. The problem is that they could appear as Bulls(1) and Sharks(4), but in the fixtures list the Bulls only play away to the Sharks. This means they would have to be switched around for your formula. Is it possible to adjust your formula to make provision for these possibilities? ) My mistake is I assumed everybody knows how a league table would work. Thank you an best regards Max "T. Valko" wrote: Do you have dates associated with the games? Will the last game be the game towards the bottom of the list? -- Biff Microsoft Excel MVP "Max" wrote in message ... Hello All, In column A is the list of the Home Teams in Column B is the Home team score in column C is the Away Team score and Column d is the Away Teams Eg: HOME HT Score AT Score AWAY Highlanders 31 33 Brumbies Force 19 25 Blues Lions 34 28 Cheetahs Crusaders 19 13 Chiefs Hurricanes 22 26 Waratahs Stormers 15 20 Sharks Bulls 33 20 Reds Hurricanes 22 17 Highlanders Waratahs 11 7 Chiefs Force 16 10 Cheetahs Stormers 27 24 Reds Brumbies 18 16 Crusaders Bulls 59 26 Blues Sharks 25 10 Lions The lists will be for the whole seasons games, so the teams are not sorted. Thank you Max "Bernard Liengme" wrote: We would need to know how your data is set out. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Max" wrote in message ... Hello, I need help with finding the last game that two teams played each other, either home or away. This is to decide who will have the home town advantage in the semi-finals. What formula would be able to find the last time two teams played each other in a season (either home (Team A vs Team B) or away (Team B v Team A)), and extract the score for the last game for both teams? Thank you and best regards Max |
| All times are GMT +1. The time now is 10:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com