I work for a Distribution company that stock goods in 4 different warehouses.
Every pallet in these warehouses have a different location No.
For example:-
Warehouse 1 pallet locations might start 10001A, 10001B, 10001C, 10002A,
10002B, 10002C and so forth.
Now every loaction also have a Check Didgit ranging from 01 - 79.
If a location increases by 1 number (10001A - 10002A) the check didgit
decreases by 7 numbres.
If a location decreases by 1 number (10002A - 10001A) the check didgit
increases by 7 numbers.
If the location increase by 1 isle (10001A - 10001B) the check didgit
increases by 23 numbers and in reverse decreaes by 23 number.
I need a formula that can calulate the check didgits against the location
numbers once I have entered the first value.
The data will be inputed into 3 colums.
Column 1 = Location Number
Column 2 = Isle Letter
Column 3 = Check Didgit

The formula has to able to calculate the following terms.
1. The check didgit can't go any higher then 79 and no lower the 01.
2. Has to able to calculate the change in isle letters.
3. Has to able to calculate a jump in Location numbers as not all numbers
run in sequence.

Here is brife example of what the spreadsheet would look like with actual
values.

Column 1 Column 2 Colume 3
Location Isle Check Didgit
10001 A 33
10002 A 26
10003 A 19
10001 B 56
10002 B 49
10003 B 42

I want to be able to put this information into numerical order (10001A,
10001B, 10001C ect.) and have a formula that can calculate the change and
keep the correct information.

I know this may be a challenging task even to the most experianced excel
users, but if any one can come up with a working formula and let me know what
it is, I would be very greatful to you as you could save me months of
inputting data.

Thank you for taking the time to read through this question/challenge.
I would also like to thank anyone in advance who may take some of there time
to look into the challenge.

Good luck and thanks again.

The following formuala will produce yout able based on the following
assumptions:

1.The "base" (Starting) location, ailse and check digits are cells A1,B1 and
C1 i.e. 10001, A, 33 in your table

2. Skip in location and.or aisle is a serial sequence i.e. jump form
location 10001 to 10004 will decrease by 21 [ (10004 -10001)*7]

3. Not sure what you want to do when you reach digit limits but set to 1 or
79 when reached.


Base calculation is:

=$C$1+($A$1-A2)*7+(CODE(B2)-CODE($B$1))*23

With conditional test:

==IF($C$1+($A$1-A2)*7+(CODE(B2)-CODE($B$1))*2379,79,IF($C$1+($A$1-A2)*7+(CODE(B2)-CODE($B$1))*23<1,1,$C$1+($A$1-A2)*7+(CODE(B2)-CODE($B$1))*23))

HTH

"barry24uk" wrote:

I work for a Distribution company that stock goods in 4 different warehouses.
Every pallet in these warehouses have a different location No.
For example:-
Warehouse 1 pallet locations might start 10001A, 10001B, 10001C, 10002A,
10002B, 10002C and so forth.
Now every loaction also have a Check Didgit ranging from 01 - 79.
If a location increases by 1 number (10001A - 10002A) the check didgit
decreases by 7 numbres.
If a location decreases by 1 number (10002A - 10001A) the check didgit
increases by 7 numbers.
If the location increase by 1 isle (10001A - 10001B) the check didgit
increases by 23 numbers and in reverse decreaes by 23 number.
I need a formula that can calulate the check didgits against the location
numbers once I have entered the first value.
The data will be inputed into 3 colums.
Column 1 = Location Number
Column 2 = Isle Letter
Column 3 = Check Didgit

The formula has to able to calculate the following terms.
1. The check didgit can't go any higher then 79 and no lower the 01.
2. Has to able to calculate the change in isle letters.
3. Has to able to calculate a jump in Location numbers as not all numbers
run in sequence.

Here is brife example of what the spreadsheet would look like with actual
values.

Column 1 Column 2 Colume 3
Location Isle Check Didgit
10001 A 33
10002 A 26
10003 A 19
10001 B 56
10002 B 49
10003 B 42

I want to be able to put this information into numerical order (10001A,
10001B, 10001C ect.) and have a formula that can calculate the change and
keep the correct information.

I know this may be a challenging task even to the most experianced excel
users, but if any one can come up with a working formula and let me know what
it is, I would be very greatful to you as you could save me months of
inputting data.

Thank you for taking the time to read through this question/challenge.
I would also like to thank anyone in advance who may take some of there time
to look into the challenge.

Good luck and thanks again.