rank login attempts

jamesddelaney

New Member
Joined
Jun 24, 2012
Messages
4
i have a pp table of usernames and a date time stamp of each login. so if a user logged into a site 5 times, he would have 5 entries in the table.

i'd like to add a column to this table call login number.

username
login_datetime
login_number
james
1/1/12 5:00 AM
1
james
1/1/12 11:00 AM
2
james
1/2/12 6:00 AM
3
james
2/1/12 5:00 AM
4
james
3/10/12 5:00 AM
5
john
1/3/12 4:00 PM
1
john
1/3/12 8:00 PM
2
john
1/3/12 11:00 PM
3

<tbody>
</tbody>

what formula would you use to do the calculation for "login_number".
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Excel 2010
ABC
1usernamelogin_datetimelogin_number
2james1/1/2012 5:001
3james1/1/2012 11:002
4james1/2/2012 6:003
5james2/1/2012 5:004
6james3/10/2012 5:005
7john1/3/2012 16:001
8john1/3/2012 20:002
9john1/3/2012 23:003
Sheet5
Cell Formulas
RangeFormula
C2=COUNTIF($A$2:A2,A2)


Drag formula down
 
Upvote 0
i have taken this a bit further but i an now running into this error:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

I also had to change around the syntax of your formula just a bit but given the error i am guessing i didn't do it quite right.

Code:
=RANKX(Table1,FILTER(Table1,[username]=EARLIER([username])),[login_datetime],1,skip)

thanks.
 
Upvote 0
Sorry sloppy with my syntax. "skip" is the default so havent put it after the 1. The ",," before the 1 is because the "value" expression goes here (i'm not quite sure what "value" means!)

=RANKX(FILTER(Table1,[username]=EARLIER([username])),[login_datetime],,1)
 
Upvote 0

Forum statistics

Threads
1,211,840
Messages
6,104,300
Members
447,901
Latest member
boy3hc2004

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top