|
find an exact number in a different sheet and use the cell containing the number to output information
i have two sheets. on one sheet i have a column of about 100 team
numbers ranging in varying intervals from 1 to 1676. the other sheet looks like this: (cell columns separated by - ) [team number 6] - [team 6 stat] - [team 6 stat] - [team 6 stat] ... [team number 1] - [team 1 stat] - [team 1 stat] - [team 1 stat] ... [team number 4] - [team 4 stat] - [team 4 stat] - [team 4 stat] ... [team number 6] - [team 6 stat] - [team 6 stat] - [team 6 stat] ... [team number 2] - [team 2 stat] - [team 2 stat] - [team 2 stat] ... Note that team 6 appears in two locations. i need to take each team number from my first sheet and find it in the other sheet with all the stats so that i can export averages or totals to the sheet with only the team numbers. my problem is that i have a list of team numbers, but i don't know what order they will appear in the stat sheet, because the matchups are random. How can i find the team in the stat list and export the stats to the team list? |
find an exact number in a different sheet and use the cell containing the number to output information
|
find an exact number in a different sheet and use the cell containing the number to output information
Assume the sheet with the statistics is Sheet2
on the sheet with 100 teams in cell B1 =sumproduct(--(Sheet2!$A$1:$A$2000=$A1),Sheet2!B$1:B$2000) now drag fill to column D select B1:D1 and drag fill down to 100 cells. (this assumes no header row. If there is a header row, make obvious adjustments) this is the basic formula. Modifications would depend on what the statistics are and what you want. -- Regards, Tom Ogilvy wrote in message oups.com... i have two sheets. on one sheet i have a column of about 100 team numbers ranging in varying intervals from 1 to 1676. the other sheet looks like this: (cell columns separated by - ) [team number 6] - [team 6 stat] - [team 6 stat] - [team 6 stat] ... [team number 1] - [team 1 stat] - [team 1 stat] - [team 1 stat] ... [team number 4] - [team 4 stat] - [team 4 stat] - [team 4 stat] ... [team number 6] - [team 6 stat] - [team 6 stat] - [team 6 stat] ... [team number 2] - [team 2 stat] - [team 2 stat] - [team 2 stat] ... Note that team 6 appears in two locations. i need to take each team number from my first sheet and find it in the other sheet with all the stats so that i can export averages or totals to the sheet with only the team numbers. my problem is that i have a list of team numbers, but i don't know what order they will appear in the stat sheet, because the matchups are random. How can i find the team in the stat list and export the stats to the team list? |
find an exact number in a different sheet and use the cell containing the number to output information
Thanks Tom, it worked.
I understand the code, and it works if I want the sum of the stats to appear. Now how would I do the same thing but get an average of the stats to appear? Thanks again. On Feb 18, 9:29 am, "Tom Ogilvy" wrote: Assume the sheet with the statistics is Sheet2 on the sheet with 100 teams in cell B1 =sumproduct(--(Sheet2!$A$1:$A$2000=$A1),Sheet2!B$1:B$2000) now drag fill to column D select B1:D1 and drag fill down to 100 cells. (this assumes no header row. If there is a header row, make obvious adjustments) this is the basic formula. Modifications would depend on what the statistics are and what you want. -- Regards, Tom Ogilvy wrote in message oups.com... i have two sheets. on one sheet i have a column of about 100 team numbers ranging in varying intervals from 1 to 1676. the other sheet looks like this: (cell columns separated by - ) [team number 6] - [team 6 stat] - [team 6 stat] - [team 6 stat] ... [team number 1] - [team 1 stat] - [team 1 stat] - [team 1 stat] ... [team number 4] - [team 4 stat] - [team 4 stat] - [team 4 stat] ... [team number 6] - [team 6 stat] - [team 6 stat] - [team 6 stat] ... [team number 2] - [team 2 stat] - [team 2 stat] - [team 2 stat] ... Note that team 6 appears in two locations. i need to take each team number from my first sheet and find it in the other sheet with all the stats so that i can export averages or totals to the sheet with only the team numbers. my problem is that i have a list of team numbers, but i don't know what order they will appear in the stat sheet, because the matchups are random. How can i find the team in the stat list and export the stats to the team list? |
find an exact number in a different sheet and use the cell containing the number to output information
=Average(If(Sheet2!$A$1:$A$2000=$A1,Sheet2!B$1:B$2 000))
Entered with Ctrl+Shift+enter rather than just enter since this is an array formula. then drag fill down -- Regards, Tom Ogilvy wrote in message oups.com... Thanks Tom, it worked. I understand the code, and it works if I want the sum of the stats to appear. Now how would I do the same thing but get an average of the stats to appear? Thanks again. On Feb 18, 9:29 am, "Tom Ogilvy" wrote: Assume the sheet with the statistics is Sheet2 on the sheet with 100 teams in cell B1 =sumproduct(--(Sheet2!$A$1:$A$2000=$A1),Sheet2!B$1:B$2000) now drag fill to column D select B1:D1 and drag fill down to 100 cells. (this assumes no header row. If there is a header row, make obvious adjustments) this is the basic formula. Modifications would depend on what the statistics are and what you want. -- Regards, Tom Ogilvy wrote in message oups.com... i have two sheets. on one sheet i have a column of about 100 team numbers ranging in varying intervals from 1 to 1676. the other sheet looks like this: (cell columns separated by - ) [team number 6] - [team 6 stat] - [team 6 stat] - [team 6 stat] ... [team number 1] - [team 1 stat] - [team 1 stat] - [team 1 stat] ... [team number 4] - [team 4 stat] - [team 4 stat] - [team 4 stat] ... [team number 6] - [team 6 stat] - [team 6 stat] - [team 6 stat] ... [team number 2] - [team 2 stat] - [team 2 stat] - [team 2 stat] ... Note that team 6 appears in two locations. i need to take each team number from my first sheet and find it in the other sheet with all the stats so that i can export averages or totals to the sheet with only the team numbers. my problem is that i have a list of team numbers, but i don't know what order they will appear in the stat sheet, because the matchups are random. How can i find the team in the stat list and export the stats to the team list? |
| All times are GMT +1. The time now is 07:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com