Autofilling a numeric time code corresponding to a date?

Relative excel novice here, just learning proper vba script.

I have a work sheet with student scores across various competencies over time. I'm sorting the data
preparing ultimately to run a factor analyses / create a MTMM.

My sheet has 80,000 rows from all testing so the manual sort isn't going to work. I have the date at which each
student was tested but need to assign a number code (time 1, 2, 3 etc) for each student based on when their
first testing session was to their last. The issue is that when I autofill it does a series fill the whole way down or
just a straight copy. I need it to start back at 1 for each new student as it fills down the row i.e.:

Student    time
1                   1
1                   2
1                   3
2                   1
2                   2
2                   3
2                   4
n                  n

Problem being not all students were testing same number of times, there is lots of variance.

Any help is greatly appreciate, thank you very much.
Connor QueenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Assuming your data starts from A2 while A1 being the header, try the following formula in B2
=COUNTIF(A$2:A2,A2)

Open in new window

and copy down.
0
Connor QueenAuthor Commented:
I should have specified @Subbodh there are multiple fields for the same date, so the sequential count isn't working as it is assigning a new number for the same field.

updated view of what is needed.

Student    time
1                   1
1                   1
1                   1
1                   2
1                   2
1                   3
1                   3
n                  n

Thanks.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Which one is the date column and which one is the Student column?
0
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
It's always easy to visualize your requirement when you upload a sample workbook. If possible, please upload one with the desired output mocked up manually.
0
Connor QueenAuthor Commented:
Ive attached a screen shot.

A is student, E is date and F is destination for code.
Screen-Shot.png
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Then try this....

In F2
=COUNTIFS(A$2:A2,A2,E$2:E2,E2)

Open in new window

and copy down.
0
Connor QueenAuthor Commented:
Mock up is attached of what I'm trying to get to, i.e. count is based on the date stamp.
mockup.xlsx
0
Connor QueenAuthor Commented:
Still getting a sequential count with your second posted formula.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.
In C2
=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)>1,C1,MAX(IF(A$1:A1=A2,C$1:C1))+1)

Open in new window

mockup.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Connor QueenAuthor Commented:
Returning a date in absolute days instead of a coded 1,2,3....n
Screen-Shot-2016-09-17-at-1.34.56-PM.png
0
Connor QueenAuthor Commented:
(i substituted for the appropriate fields in the real workbook)
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
So what formula you are using in F2 in your actual workbook?
0
Connor QueenAuthor Commented:
=IF(COUNTIFS(A$2:A2,A2,E$2:E2,E2)>1,E1,MAX(IF(A$1:A1=A2,E$1:E1))+1)

then hitting the confirmation key combo.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Should be this...
=IF(COUNTIFS(A$2:A2,A2,E$2:E2,E2)>1,F1,MAX(IF(A$1:A1=A2,F$1:F1))+1)

Open in new window

and confirm with Ctrl+Shift+Enter.
0
Connor QueenAuthor Commented:
Almost there, only working for specific date.
Screen-Shot-2016-09-17-at-1.45.38-PM.png
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Screenshots don't help, we cannot work with them.
BTW did you confirm the formula with Ctrl+Shift+Enter?
Click any formula cell and in the formula bar, your formula should be surrounded by curly braces { }, that means you have correctly entered the Array formula.
If you don't see the curly braces in the formula bar, select the cell F2 and press the function key F2 and then hold down the Ctrl+Shift and then hit Enter. And then simply copy down the formula.
0
Connor QueenAuthor Commented:
My apologies on screenshot, first time user.

I hit command accidentally (on a mac), hit ctrl and is working now.

Thank you very much for your help.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
No problem. Glad the formula is working for you ultimately.
You're welcome. Glad to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.