Need stupid help again

[Payne]

Hey folks. I am just wondering if I can do something.

Yes, I come up with dumb things, so please bear with me.

Okay, a bit of background for the one helping me.

I run a baseball team/league, and I have been helped by you folks with ratios, and random seedings. Now, I am looking for streaks.

If it is possible, I would like to make a cell show the streak of a certain other cell. I will show example.


A B C D E F G
1 Name Wins Losses Draws Streak
2 Dan 3 0 0 3 wins
3 John 2 1 0 1 loss
3 Paul 1 2 0 2 losses
4 Jim 0 2 1 1 Draw

I hope you get the point. I would like, say, cell E2 to show the streak of Wins/Losses/Draws.

Is there a way of doing this?

In cell E2, show the last input in either cell B2, C2 or D2.

THanks for the help, even if I can't do it.

 

[Aladin Akyurek]

... I am looking for streaks.

If it is possible, I would like to make a cell show the streak of a certain other cell. I will show example.


A B C D E F G
1 Name Wins Losses Draws Streak
2 Dan 3 0 0 3 wins
3 John 2 1 0 1 loss
3 Paul 1 2 0 2 losses
4 Jim 0 2 1 1 Draw

I hope you get the point. I would like, say, cell E2 to show the streak of Wins/Losses/Draws.

Is there a way of doing this?

In cell E2, show the last input in either cell B2, C2 or D2...

Book4
	A	B	C	D	E	F
1	Name	Wins	Losses	Draws	Streak
2	Dan	3	0	0	3	Wins
3	John	2	1	0	1	Losses
4	Paul	1	2	0	2	Losses
5	Jim	0	2	1	1	Draws
6
Sheet1

Formulas...

E2, which is copied down:

=LOOKUP(9.99999999999999E+307,IF(B2:D2>0,B2:D2))

F2, whic is copied down:

=INDEX($B$1:$D$1,MATCH(9.99999999999999E+307,IF(B2:D2>0,B2:D2)))

Both formulas must be confirmed with control+shift+enter instead of just with enter.

If you wouldn't use 0, the formulas can be turned into ordinary (less expensive) formulas.

 

[Payne]

That one didn't work at all. It just put the highest number in E/F2

 

[Aladin Akyurek]

That one didn't work at all. It just put the highest number in E/F2

If this is addressing what I posted, it's just plain wrong. The formulas do not pick up the highest number. Rather, they pick out the last non-zero number in a row. If this is the wrong thing to do, then re-read your initial post for it produces exactly the desired results under streak you provided. Maybe you defined what a streak is somewhere I missed.

 

[tactps]

To change my event handler to handle more rows, you only need to change one line:

If Target.Row > 1 And Target.Row < 6 And Target.Column < 5 And Target.Column > 1 Then

change to:
If Target.Row > 1 And Target.Row < 76 And Target.Column < 5 And Target.Column > 1 Then


Also, re it happening in the row below, this happens because you probably have your cursor moving on enter.

To get rid of this, click on "Tools", then "Options", then under "Edit" tab, uncheck "Move selection after enter".

Regards

 

[tactps]

Alternatively, you can leave the "Move after selection" as it is and change the event handler to:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 1 And Target.Row < 6 And Target.Column < 5 And Target.Column > 1 Then
If ActiveCell.Offset(1 - ActiveCell.Row, 0).Text = ActiveCell.Offset(-1, 6 - ActiveCell.Column).Text Then
ActiveCell.Offset(-1, 5 - ActiveCell.Column).Formula = ActiveCell.Offset(-1, 5 - ActiveCell.Column).Value + 1
Else
ActiveCell.Offset(-1, 5 - ActiveCell.Column).Formula = 1
ActiveCell.Offset(-1, 6 - ActiveCell.Column).Formula = ActiveCell.Offset(1 - ActiveCell.Row, 0).Text
End If
End If
End Sub

 

[Payne]

tactps, SUCCESS! Thank you very much. It now works, and that was the problem. I used the second code, and it works perfectly.


Aladin, I admit I know nothing about Excel, and may have screwed up, but sarcasm is not appreciated.


Tactps and DRJ, thank you very much for your help

 

[tactps]

Aladin,

This issue is that the OP was not listeing each win, loss and draw in columns or rows, but rather was just increasing the win/loss/draw number in 3 columns.

Payne,

Glad to help and thanks for the feedback.

Regards

 

[wroet]

how about if you insert a hidden column after the 'wins' 'losses' and draws, wich contain the las entry date + time of the cell before it, and in column E you hlookup the last date/time, and then give the cell before...

isnt that the easy way or dont i understand what you want?

Thomas

 

[BradMcLeod]

Total thread necro, but for a reason.

I am Payne.

A recent fire totalled my computer, backups and everything,

I got a new computer, and was frantically looking for this macro. (Yes, 11 years later, and I am still using this.

Thank you to Mr. Excel (for keeping posts this long) and thanks again to tactps, if s/he is even here still.

 

[BradMcLeod]

2024, and this still is my go to. Thank you all