spudmcc

asked on

# Determine number of rows and assign name to each.

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

ASKER

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

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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

Andy

Andy

ASKER

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

Andy

Andy

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

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

ASKER

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

Try this:

=IF(A2<>A1,"A",IF(MOD(B1,FLOOR(COUNTIF(A:A,A2),)+1))))

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

=IF(A2<>A1,"A",IF(MOD(B1,F

**MIN(COUNTIF(A:A,A2),4)**)/

**MIN(COUNTIF(A:A,A2),4)**)>0,C1,CHAR(MIN(68,CODE(C1

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

ASKER

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

Andy

Andy

A countif or sumif or something like that could work if we get the logic :)