I am trying to do the following but cannot seem to wrap my head around it.
I have a workbook with multiple sheets. For what I am doing I have to pull information from one and compare it to another. It looks something like this:
Sheet 1
<tbody>
</tbody>Sheet 2
<tbody>
</tbody>
On sheet 2, I am trying to count the number of cards for each team from sheet 1, however, I only need to count cards from the winning team or the home team in the event of a tie. So I need to compare the team id with the Home ID/Away ID and if it matches see if the team won, and if they did count if there was a card present or not.
So using team 145 - they played 2 games (game # 10 & 18). In game 10, 145 was the home team and they tied. So I would look to see if there was a card and there was not so I would return a 0 in cell D2. In game 18, 145 was the away team and lost the game. So I would also return a 0 in cell D2 even though a card was present in the game.
Team 149 played 2 games (12 & 16). Game 12, 148 won the game 3-1 but there were no cards so I would return a 0 in cell D4. Game 16, 149 won 2-1 and there was a card present so I would return a 1 in cell D4.
I have thousands of games to sort through with hundreds of teams so any help I can get would really be appreciated.
I have a workbook with multiple sheets. For what I am doing I have to pull information from one and compare it to another. It looks something like this:
Sheet 1
| A | B | C | D | E | F | |
| 1 | Game # | Cards | Home ID | Home Score | Away ID | Away Score |
| 2 | 10 | 145 | 2 | 153 | 2 | |
| 3 | 11 | 1 | 156 | 3 | 151 | 1 |
| 4 | 12 | 158 | 1 | 149 | 3 | |
| 5 | 13 | 1 | 160 | 5 | 147 | 4 |
| 6 | 14 | 162 | 4 | 160 | 1 | |
| 7 | 15 | 147 | 1 | 162 | 1 | |
| 8 | 16 | 1 | 149 | 2 | 158 | 1 |
| 9 | 17 | 151 | 0 | 156 | 5 | |
| 10 | 18 | 1 | 153 | 3 | 145 | 2 |
<tbody>
</tbody>
| A | B | C | D | |
| 1 | Team ID | Team Name | Flight | Cards |
| 2 | 145 | Red | 12b | |
| 3 | 147 | Blue | 12b | |
| 4 | 149 | Green | 12b | |
| 5 | 151 | Black | 12a | |
| 6 | 153 | White | 12a | |
| 7 | 156 | Orange | 12a | |
| 8 | 158 | Silver | 12c | |
| 9 | 160 | Purple | 12c | |
| 10 | 162 | Yellow | 12c |
<tbody>
</tbody>
On sheet 2, I am trying to count the number of cards for each team from sheet 1, however, I only need to count cards from the winning team or the home team in the event of a tie. So I need to compare the team id with the Home ID/Away ID and if it matches see if the team won, and if they did count if there was a card present or not.
So using team 145 - they played 2 games (game # 10 & 18). In game 10, 145 was the home team and they tied. So I would look to see if there was a card and there was not so I would return a 0 in cell D2. In game 18, 145 was the away team and lost the game. So I would also return a 0 in cell D2 even though a card was present in the game.
Team 149 played 2 games (12 & 16). Game 12, 148 won the game 3-1 but there were no cards so I would return a 0 in cell D4. Game 16, 149 won 2-1 and there was a card present so I would return a 1 in cell D4.
I have thousands of games to sort through with hundreds of teams so any help I can get would really be appreciated.
