Excel Formula Dilemma

[ItalianPlatinum]

Please help, finding a solution is driving me NUTS. For starters my data as described.

Column A: Match Start (date started)
Column B: My Team (Always the same value)
Column C: Opposing Team (Many duplicates)
Column D: Match End (date end)
Column E: Win/Loss (value = Win or Loss depending on the result)
*The data is quite larger over 300 rows*

My end result here is I want a standings bracket (how I fair against each team (win, loss, win %). Problem I am having is the duplicates are giving me a headache, and I want it to auto pull. So not having to intervene by creating filters or auto filters. Sample below, in a smaller scale but in theory that is how my spreadsheet is made up:

Start Date	Team	Opponent	End Date	Win/Loss
9/15/2016	A	B	9/15/2016	Win
9/16/2016	A	C	9/16/2016	Win
9/17/2016	A	D	9/17/2016	Win
9/18/2019	A	E	9/19/2016	Win
9/19/2016	A	F	9/19/2016	Win
9/20/2016	A	G	9/21/2016	Win
9/21/2016	A	D	9/22/2016	Win
9/22/2016	A	E	9/23/2016	Win
9/23/2016	A	B	9/24/2016	Win
9/24/2016	A	C	9/25/2016	Win

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

 

[snjpverma]

Re: Excel Formila Dilemma.HELP ME PLEASE

Where are the duplicates in your data ? Each row has a different start date .

Could you post the formula that you are using ? along with the desired ouput for this sample data ?

 

[Eric W]

Re: Excel Formila Dilemma.HELP ME PLEASE

Welcome to the forum.

Maybe:

	A	B	C	D	E	F	G	H	I
1	Start Date	Team	Opponent	End Date	Win/Loss		Opponent	Wins	Losses
2	9/15/2016	A	B	9/15/2016	Win		B	2	0
3	9/16/2016	A	C	9/16/2016	Win		C	2	0
4	9/17/2016	A	D	9/17/2016	Win		D	2	0
5	9/18/2019	A	E	9/19/2016	Win		E	2	0
6	9/19/2016	A	F	9/19/2016	Win		F	1	0
7	9/20/2016	A	G	9/21/2016	Win		G	1	0
8	9/21/2016	A	D	9/22/2016	Win
9	9/22/2016	A	E	9/23/2016	Win
10	9/23/2016	A	B	9/24/2016	Win
11	9/24/2016	A	C	9/25/2016	Win
12
13

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet13​

Worksheet Formulas Cell Formula H2 =IF(G2="","",COUNTIFS($C$2:$C$100,G2,$E$2:$E$100,"Win")) I2 =IF(G2="","",COUNTIFS($C$2:$C$100,G2,$E$2:$E$100,"Loss")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula G2 {=IF(G1="","",IFERROR(INDEX($C$2:$C$100,SMALL(IF($C$2:$C$100<>"",IF(COUNTIF($G$1:$G1,$C$2:$C$100)=0,ROW($C$2:$C$100)-ROW($C$2)+1)),1)),""))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[ItalianPlatinum]

Re: Excel Formila Dilemma.HELP ME PLEASE

Where are the duplicates in your data ? Each row has a different start date .

Could you post the formula that you are using ? along with the desired ouput for this sample data ?

The duplications are in the opponents column

 

[ItalianPlatinum]

Okay ideally I want to create another tab called standings, so I will have the new tab reference those cells and see how that works. I will let you know how that works, thanks in advance!

 

[philaugust2004]

Try this in F2 and copy down
=COUNTIFS($E$2:E2,"Win",$C$2:C2,C2)/COUNTIF($C$2:C2,C2)*100

 

[philaugust2004]

Or, to get Wins, losses, Win %

Excel Workbook
	A	B	C	D	E	F	G	H
1	Start Date	Team	Opponent	End Date	Win/Loss	Wins	losses	win %
2	9/15/2016	A	B	9/15/2016	Win	1	0	100
3	9/16/2016	A	C	9/16/2016	Win	1	0	100
4	9/17/2016	A	D	9/17/2016	Win	1	0	100
5	9/18/2019	A	E	9/19/2016	Win	1	0	100
6	9/19/2016	A	F	9/19/2016	Win	1	0	100
7	9/20/2016	A	G	9/21/2016	Win	1	0	100
8	9/21/2016	A	D	9/22/2016	Win	2	0	100
9	9/22/2016	A	E	9/23/2016	Loss	1	1	50
10	9/23/2016	A	B	9/24/2016	Win	2	0	100
11	9/24/2016	A	C	9/25/2016	Win	2	0	100
Sheet2

 

[ItalianPlatinum]

Try this in F2 and copy down
=COUNTIFS($E$2:E2,"Win",$C$2:C2,C2)/COUNTIF($C$2:C2,C2)*100

So the array formula actually worked well. My new head scratcher is trying to find an average column that tells me on average how often do I face my opponent. I figured another array formula but I can't muster a way to get it to work.

 

[philaugust2004]

So the array formula actually worked well. My new head scratcher is trying to find an average column that tells me on average how often do I face my opponent. I figured another array formula but I can't muster a way to get it to work.

Not sure what you mean by this. Do you mean if you play Team A, Team B, Team A and then Team C you want formula to say you play Team A 50% of the time and team B and C 25%?

 

[ItalianPlatinum]

Not sure what you mean by this. Do you mean if you play Team A, Team B, Team A and then Team C you want formula to say you play Team A 50% of the time and team B and C 25%?

No more lets just say I play team A on 10/1/16, then play them again on 10/6/16. On average I play team A every 5 days. That but with more occurrences and more variables.