| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find an exact number in a different sheet and use the cell containing the number to output information
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| an exact number if a number is less than | Excel Worksheet Functions | |||
| Search A Cell for Certain Letters Then Output a Number | Excel Discussion (Misc queries) | |||
| Code using A sheet to find a number | Excel Discussion (Misc queries) | |||
| look for the exact number | Excel Discussion (Misc queries) | |||
| Find all text instances in a sheet and add one number from each row | Excel Discussion (Misc queries) | |||
Hybrid Mode
