Solved

Determine number of rows and assign name to each.

Posted on 2016-08-22
11
56 Views
Last Modified: 2016-08-25
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
0
Comment
Question by:spudmcc
  • 5
  • 5
11 Comments
 
LVL 18

Expert Comment

by:xtermie
ID: 41765484
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 :)
0
 

Author Comment

by:spudmcc
ID: 41765510
Here is a simple example of what I am trying to get to.  

Thanks
EXAMPLE.xlsx
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41765517
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
0
ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

 
LVL 32

Expert Comment

by:Rob Henson
ID: 41765521
I hadn't refreshed, your last comment answers my question; ignore my comment for now, I will update it.

Thanks
Rob H
0
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 41765548
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
1
 

Author Comment

by:spudmcc
ID: 41765565
Works perfectly for what I need it for.  Thank you so much for your quick and spot-on response.  

Andy
0
 

Author Closing Comment

by:spudmcc
ID: 41765568
Thank you so much for your quick and spot-on response.

Andy
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41765575
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,FLOOR(COUNTIF(A:A,A2),4)/4)>0,C1,CHAR(MIN(68,CODE(C1)+1))))

If you need any explanation for the formula, please let me know.

Thanks
Rob H
0
 

Author Comment

by:spudmcc
ID: 41770400
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
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41770412
Try this:

=IF(A2<>A1,"A",IF(MOD(B1,FLOOR(COUNTIF(A:A,A2),MIN(COUNTIF(A:A,A2),4))/MIN(COUNTIF(A:A,A2),4))>0,C1,CHAR(MIN(68,CODE(C1)+1))))

Note the MIN statement in bold, uses lower of COUNT for each route and 4
1
 

Author Comment

by:spudmcc
ID: 41770454
Thank you, thank you, thank you.  Works perfectly.  

Andy
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
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…

832 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