Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
Solved
Determine number of rows and assign name to each.
Posted on 2016-08-22
Hi Experts
Need some assistance with a task. I have several delivery routes that have various number of stops on each. What I need to do is to determine the number of stops per route, divide it by 4 and then assign an alpha letter to each segment. All of our delivery routes have a different number of stops so that is not static. Below is a very simple idea of what I am trying to do. One column is the stop number and the next is the segment letter (sorry the alpha didn't line-up under the word "segment".
Any help would be greatly appreciated.
VALUE SEGMENT
1 A
2 A
3 A
4 B
5 B
6 B
7 C
8 C
9 C
10 D
11 D
12 D
Need some assistance with a task. I have several delivery routes that have various number of stops on each. What I need to do is to determine the number of stops per route, divide it by 4 and then assign an alpha letter to each segment. All of our delivery routes have a different number of stops so that is not static. Below is a very simple idea of what I am trying to do. One column is the stop number and the next is the segment letter (sorry the alpha didn't line-up under the word "segment".
Any help would be greatly appreciated.
VALUE SEGMENT
1 A
2 A
3 A
4 B
5 B
6 B
7 C
8 C
9 C
10 D
11 D
12 D
5-
5
11 Comments
Active 5 days ago
Can you provide with an example of stops and routes and expected result?
A countif or sumif or something like that could work if we get the logic :)
A countif or sumif or something like that could work if we get the logic :)
Active today
See attached.
Will this be done separately for each delivery route or will there also be a route identifier in the list?
Thanks
Rob H
Stops.xlsx
Will this be done separately for each delivery route or will there also be a route identifier in the list?
Thanks
Rob H
Stops.xlsx
Active today
I hadn't refreshed, your last comment answers my question; ignore my comment for now, I will update it.
Thanks
Rob H
Thanks
Rob H
Active today
See updated version attached. All but one are the same as your manually entered values.
Route 3 only has 9 stops which does not divide wholly by 4. My version creates a fifth segment for this because each other segment has two entries.
Thanks
Rob H
Stops.xlsx
Route 3 only has 9 stops which does not divide wholly by 4. My version creates a fifth segment for this because each other segment has two entries.
Thanks
Rob H
Stops.xlsx
Active today
Glad to be of assistance.
For cases like Route 3 where it doesn't divide wholly by 4; if you want the last stop to be no more than D then adjust the formula to:
=IF(A2<>A1,"A",IF(MOD(B1,F
If you need any explanation for the formula, please let me know.
Thanks
Rob H
For cases like Route 3 where it doesn't divide wholly by 4; if you want the last stop to be no more than D then adjust the formula to:
=IF(A2<>A1,"A",IF(MOD(B1,F
If you need any explanation for the formula, please let me know.
Thanks
Rob H
Hi Rob
Something came up that I didn't think about before...what if the route has fewer than 4 stops. We have a handful of routes that have 2 or 3 stops only. With your formula it is giving us a #div/0 error.
Any ideas??
Andy
Something came up that I didn't think about before...what if the route has fewer than 4 stops. We have a handful of routes that have 2 or 3 stops only. With your formula it is giving us a #div/0 error.
Any ideas??
Andy
Featured Post
Be seen. Boost your question’s priority for more expert views and faster solutions
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Suggested Courses
Course of the Month5 days, 6 hours left to enroll
569 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.