Solved

Determine number of rows and assign name to each.

Posted on 2016-08-22
11
57 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 33

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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 33

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 33

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 33

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 33

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
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 demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

808 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