Sports Stats - Stacking Games based on Player (Problem with Home/Away)

[larinda4]

I'll try to explain this as best as I can and I have included screenshots.

I want to stack information based on player.

I have two sheets, one called "Score Sheets" where I enter the weekly scoresheets and another called "Player Stats".

I have my player name is cell D1 of "Player Stats" and I want it to pull from "Score Sheets" either home or away based on the player name in D1. I want it to show Player Name, Handicap, Total, Win/Loss but it doesn't need to show the opponent.

For example, if the player was home, it would take columns C:G of that row and create a new list off to the side in the "Player Stats" sheet. If they were away, it would take columns S:W of that row and add it to the created list off to the side.

I've been racking my brain trying to think of a formula for this but now I'm thinking maybe I'd need to create a code?

 

[Fluff]

How about

=VSTACK(FILTER(CHOOSECOLS('Score Sheets'!D2:G1000,1,2,-1),'Score Sheets'!B2:B1000=D1),FILTER(CHOOSECOLS('Score Sheets'!T2:W1000,1,2,-1),'Score Sheets'!R2:R1000=D1))

 

[larinda4]

How about

=VSTACK(FILTER(CHOOSECOLS('Score Sheets'!D2:G1000,1,2,-1),'Score Sheets'!B2:B1000=D1),FILTER(CHOOSECOLS('Score Sheets'!T2:W1000,1,2,-1),'Score Sheets'!R2:R1000=D1))

Thanks Fluff for the help!

It's working for the most part. I have tweaked the references in the code.

=VSTACK(FILTER(CHOOSECOLS('Score Sheets'!D:G,1,2,-1),'Score Sheets'!D:D=D1),FILTER(CHOOSECOLS('Score Sheets'!T:W,1,2,-1),'Score Sheets'!T:T=D1))

Could you help me with including the date (column B) and then maintaining the data in order of date? I'd also need the Win/Loss included (Home would be Column H and Away would be Column X). I have included screenshots of what it looks like now and what I'd need it to look like.

Thank you again!

 

[Fluff]

Your image shows the win/loss in columns G & W, not H & X

 

[larinda4]

Your image shows the win/loss in columns G & W, not H & X

Sorry about that. I have included an updated picture.

 

[larinda4]

So I got the VSTACK to work how I need except maintaining the data in order of date.

=VSTACK(FILTER(CHOOSECOLS('Score Sheets'!B:H,1,3,4,5,7),'Score Sheets'!D:D=D1),FILTER(CHOOSECOLS('Score Sheets'!B:X,1,19,20,21,23),'Score Sheets'!T:T=D1))

 

[larinda4]

I figured it out.

Here's the code:

=SORT(VSTACK(FILTER(CHOOSECOLS('Score Sheets'!B:H,1,3,4,5,7),'Score Sheets'!D:D=D1),FILTER(CHOOSECOLS('Score Sheets'!B:X,1,19,20,21,23),'Score Sheets'!T:T=D1)),,1,FALSE)

Thank you for your help, Fluff!

 

[Fluff]

Glad you sorted it & thanks for letting us know.