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:

[TABLE="width: 411"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]Team[/TD]
[TD]Opponent[/TD]
[TD]End Date[/TD]
[TD]Win/Loss[/TD]
[/TR]
[TR]
[TD]9/15/2016[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]9/15/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/16/2016[/TD]
[TD]A[/TD]
[TD]C[/TD]
[TD]9/16/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/17/2016[/TD]
[TD]A[/TD]
[TD]D[/TD]
[TD]9/17/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/18/2019[/TD]
[TD]A[/TD]
[TD]E[/TD]
[TD]9/19/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/19/2016[/TD]
[TD]A[/TD]
[TD]F[/TD]
[TD]9/19/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/20/2016[/TD]
[TD]A[/TD]
[TD]G[/TD]
[TD]9/21/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/21/2016[/TD]
[TD]A[/TD]
[TD]D[/TD]
[TD]9/22/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/22/2016[/TD]
[TD]A[/TD]
[TD]E[/TD]
[TD]9/23/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/23/2016[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]9/24/2016[/TD]
[TD]Win[/TD]
[/TR]
[TR]
[TD]9/24/2016[/TD]
[TD]A[/TD]
[TD]C[/TD]
[TD]9/25/2016[/TD]
[TD]Win[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]

 

[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
B
C
D
E
F
G

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Start Date[/TD]
[TD="bgcolor: #FAFAFA"]Team[/TD]
[TD="bgcolor: #FAFAFA"]Opponent[/TD]
[TD="bgcolor: #FAFAFA"]End Date[/TD]
[TD="bgcolor: #FAFAFA"]Win/Loss[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Opponent[/TD]
[TD="bgcolor: #FAFAFA"]Wins[/TD]
[TD="bgcolor: #FAFAFA"]Losses[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/15/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]B[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/15/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/16/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]C[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/16/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/17/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]D[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/17/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/18/2019[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]E[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/19/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/19/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]F[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/19/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/20/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]G[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/21/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/21/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]D[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/22/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/22/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]E[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/23/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/23/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]B[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/24/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/24/2016[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]C[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9/25/2016[/TD]
[TD="bgcolor: #FAFAFA"]Win[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet13​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=IF(G2="","",COUNTIFS($C$2:$C$100,G2,$E$2:$E$100,"Win"))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I2[/TH]
[TD="align: left"]=IF(G2="","",COUNTIFS($C$2:$C$100,G2,$E$2:$E$100,"Loss"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]{=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)),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

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