Connor Queen

asked on

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

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.

ASKER

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.

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.

Which one is the date column and which one is the Student column?

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.

ASKER

Then try this....

In F2

In F2

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

and copy down.
ASKER

Mock up is attached of what I'm trying to get to, i.e. count is based on the date stamp.

mockup.xlsx

mockup.xlsx

ASKER

Still getting a sequential count with your second posted formula.

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

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

Screen-Shot-2016-09-17-at-1.34.56-PM.png

ASKER

(i substituted for the appropriate fields in the real workbook)

So what formula you are using in F2 in your actual workbook?

ASKER

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

then hitting the confirmation key combo.

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)`

and confirm with Ctrl+Shift+Enter.
ASKER

Almost there, only working for specific date.

Screen-Shot-2016-09-17-at-1.45.38-PM.png

Screen-Shot-2016-09-17-at-1.45.38-PM.png

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.

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.

ASKER

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.

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

Thank you very much for your help.

No problem. Glad the formula is working for you ultimately.

You're welcome. Glad to help.

You're welcome. Glad to help.

Open in new window

and copy down.