Solved

Autofilling a numeric time code corresponding to a date?

Posted on 2016-09-17
18
44 Views
Last Modified: 2016-09-17
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.
0
Comment
Question by:Connor Queen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
18 Comments
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41803206
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
 

Author Comment

by:Connor Queen
ID: 41803223
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
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41803225
Which one is the date column and which one is the Student column?
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41803227
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
 

Author Comment

by:Connor Queen
ID: 41803230
Ive attached a screen shot.

A is student, E is date and F is destination for code.
Screen-Shot.png
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41803236
Then try this....

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

Open in new window

and copy down.
0
 

Author Comment

by:Connor Queen
ID: 41803238
Mock up is attached of what I'm trying to get to, i.e. count is based on the date stamp.
mockup.xlsx
0
 

Author Comment

by:Connor Queen
ID: 41803241
Still getting a sequential count with your second posted formula.
0
 
LVL 31

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41803263
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
 

Author Comment

by:Connor Queen
ID: 41803279
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
 

Author Comment

by:Connor Queen
ID: 41803280
(i substituted for the appropriate fields in the real workbook)
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41803282
So what formula you are using in F2 in your actual workbook?
0
 

Author Comment

by:Connor Queen
ID: 41803284
=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
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41803288
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
 

Author Comment

by:Connor Queen
ID: 41803293
Almost there, only working for specific date.
Screen-Shot-2016-09-17-at-1.45.38-PM.png
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41803298
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
 

Author Comment

by:Connor Queen
ID: 41803299
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
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41803300
No problem. Glad the formula is working for you ultimately.
You're welcome. Glad to help.
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question