Solved

Determine number of rows and assign name to each.

Posted on 2016-08-22

Microsoft Excel

1 solution

Medium Priority

60 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

Comment

Question by:spudmcc

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

11 Comments

LVL 18

Expert Comment

by:xtermie

ID: 417654842016-08-22

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

Author Comment

by:spudmcc

ID: 417655102016-08-22

Here is a simple example of what I am trying to get to.

Thanks
EXAMPLE.xlsx

LVL 33

Expert Comment

by:Rob Henson

ID: 417655172016-08-22

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

LVL 33

Expert Comment

by:Rob Henson

ID: 417655212016-08-22

I hadn't refreshed, your last comment answers my question; ignore my comment for now, I will update it.

Thanks
Rob H

LVL 33

Accepted Solution

by:Rob Henson

Rob Henson earned 2000 total points

ID: 417655482016-08-22

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

Author Comment

by:spudmcc

ID: 417655652016-08-22

Works perfectly for what I need it for. Thank you so much for your quick and spot-on response.

Andy

Author Closing Comment

by:spudmcc

ID: 417655682016-08-22

Thank you so much for your quick and spot-on response.

Andy

LVL 33

Expert Comment

by:Rob Henson

ID: 417655752016-08-22

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

Author Comment

by:spudmcc

ID: 417704002016-08-25

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

LVL 33

Expert Comment

by:Rob Henson

ID: 417704122016-08-25

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

Author Comment

by:spudmcc

ID: 417704542016-08-25

Thank you, thank you, thank you. Works perfectly.

Andy

Featured Post

Question has a verified solution.

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

Finding the Office Templates Folder

Article by: Helen

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are …