Hi All - looking for some help with a VBA script to do the following:
I have a master list with just over 5000 rows of data.
Column A is formatted as a date (ex: 5/6/17). Note that sometimes there is the same service day two or more times for the same unique identifier.
Column C is my unique identifier (there are 25 different unique identifiers, but this could change with other data sets).
Column L is a velocity level and formatted as a number (ex: 10, 0, 20, 25, 100, etc). Note, this number is always between 0 and 100.
One a second sheet, called "List," I have all 25 unique identifiers transposed across row 2 with the first one in column A and the last one in column Y. What I'm hoping to do is, starting in row 3 going down is for the script to put in the number of days until the velocity level is greater than or equal to 90.
So essentially the loop would do something like this:
On Master List tab:
For each unique identifier:
1. Count # of days until velocity level >=90 starting from row 2 working down
2. Then go to next service date where velocity level = 0
3. And count # of service days until velocity level >=90
4. Then go to next service date where velocity level = 0
Loop to step 1.
Next unique identifier
I would like to record each # of days until velocity level >=90 on the "List" tab underneath each respective unique identifier.
I've attached a link to a sample file with the first row of days until velocity level >= 90 populated on the list tab for the first unique identifier.
I think my (very manual) approach would be to filter for each unique ID in column C, then look at the max date and the min date between the first row of data for that unique identifier and the row before the velocity level >=90 and calculate the difference. So for the first data point on the list tab I have 21 days.
https://drive.google.com/open?id=0B_o_PsSgIw_-OGpoc2FSYmxTVjA
Thank you!
I have a master list with just over 5000 rows of data.
Column A is formatted as a date (ex: 5/6/17). Note that sometimes there is the same service day two or more times for the same unique identifier.
Column C is my unique identifier (there are 25 different unique identifiers, but this could change with other data sets).
Column L is a velocity level and formatted as a number (ex: 10, 0, 20, 25, 100, etc). Note, this number is always between 0 and 100.
One a second sheet, called "List," I have all 25 unique identifiers transposed across row 2 with the first one in column A and the last one in column Y. What I'm hoping to do is, starting in row 3 going down is for the script to put in the number of days until the velocity level is greater than or equal to 90.
So essentially the loop would do something like this:
On Master List tab:
For each unique identifier:
1. Count # of days until velocity level >=90 starting from row 2 working down
2. Then go to next service date where velocity level = 0
3. And count # of service days until velocity level >=90
4. Then go to next service date where velocity level = 0
Loop to step 1.
Next unique identifier
I would like to record each # of days until velocity level >=90 on the "List" tab underneath each respective unique identifier.
I've attached a link to a sample file with the first row of days until velocity level >= 90 populated on the list tab for the first unique identifier.
I think my (very manual) approach would be to filter for each unique ID in column C, then look at the max date and the min date between the first row of data for that unique identifier and the row before the velocity level >=90 and calculate the difference. So for the first data point on the list tab I have 21 days.
https://drive.google.com/open?id=0B_o_PsSgIw_-OGpoc2FSYmxTVjA
Thank you!
