find an exact number in a different sheet and use the cell containing the number to output informati

[SirMichaelGriffin]

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?

 

[Don Guillett]

Do you want the first team 6 stats or the total of team 6 stats?

 

[Tom Ogilvy]

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 B11 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.

 

[SirMichaelGriffin]

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.

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 B11 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

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?

 

[Tom Ogilvy]

=Average(If(Sheet2!$A$1:$A$2000=$A1,Sheet2!B$1:B$2000))

Entered with Ctrl+Shift+enter rather than just enter since this is an array
formula.

then drag fill down

--
Regards,
Tom Ogilvy

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.

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 B11 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

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?