Hello again,
I've managed to deal with some things but now I'm struggling with something like this... Ill try to explain best I can, if something is not clear, just please point it to me and I’ll try explain it again.
This is example of my daily order list, locations are next to each other in one sheet and that's what I'm trying to automate and filter/sort to make it easier/faster.
EXAMPLE.xlsx |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T |
---|
1 | | | | | | | | | | | | | | | | | | | | |
---|
2 | | | | | | | | | | | | | | | | | | | | |
---|
3 | | | | | | | | | Location1 | Location2 | Location3 | Location4 | Location5 | Location6 |
---|
4 | Product1 | | | | | | | | | | 18 | 18 | 9 | 9 | 9 | 9 | 18 | 18 | | |
---|
5 | Product2 | | | | | | | | 288 | 3 x 84 + 36 | 264 | 3 x 84 + 12 | 204 | 2 x 84 + 36 | 912 | 10 x 84 + 72 | 432 | 5 x 84 + 12 | 744 | 8 x 84 + 72 |
---|
6 | Product3 | | | | | | | | | | | | 162 | 3 x 45 + 27 | 288 | 6 x 45 + 18 | | | 261 | 5 x 45 + 36 |
---|
7 | Product4 | | | | | | | | | | | | | | | | 189 | 4 x 45 + 9 | | |
---|
8 | Product5 | | | | | | | | | | | | 72 | 45 + 27 | 108 | 2 x 45 + 18 | | | 117 | 2 x 45 + 27 |
---|
9 | Product6 | | | | | | | | | | | | | | | | 135 | 3 x 45 | | |
---|
10 | Product7 | | | | | | | | 36 | 36 | 48 | 48 | 48 | 48 | 36 | 36 | 96 | 2 x 48 | 60 | 48 + 12 |
---|
11 | Product8 | | | | | | | | 6 | 6 | | | 4 | 4 | 6 | 6 | | | | |
---|
12 | Product9 | | | | | | | | 72 | 2 x 36 | 81 | 2 x 36 + 9 | 63 | 36 + 27 | 90 | 2 x 36 + 18 | | | 81 | 2 x 36 + 9 |
---|
13 | Product10 | | | | | | | | | | | | | | | | 126 | 3 x 36 + 18 | | |
---|
14 | Product11 | | | | | | | | 81 | 45 + 36 | 135 | 3 x 45 | 135 | 3 x 45 | 252 | 5 x 45 + 27 | | | 234 | 5 x 45 + 9 |
---|
15 | Product12 | | | | | | | | | | | | | | | | 207 | 4 x 45 + 27 | | |
---|
16 | Product13 | | | | | | | | 24 | 24 | 36 | 24 + 12 | 72 | 3 x 24 | 36 | 24 + 12 | 120 | 5 x 24 | 30 | 24 + 6 |
---|
17 | Product14 | | | | | | | | 12 | 12 | 12 | 12 | 12 | 12 | 48 | 48 | 24 | 24 | 24 | 24 |
---|
18 | Product15 | | | | | | | | 36 | 36 | 9 | 9 | | | 135 | 3 x 36 + 27 | 9 | 9 | 117 | 3 x 36 + 9 |
---|
19 | Product16 | | | | | | | | 63 | 36 + 27 | 9 | 9 | | | 153 | 4 x 36 + 9 | 18 | 18 | 117 | 3 x 36 + 9 |
---|
20 | Product17 | | | | | | | | 72 | 2 x 28 + 16 | 116 | 4 x 28 + 4 | 92 | 3 x 28 + 8 | 128 | 4 x 28 + 16 | | | 140 | 5 x 28 |
---|
21 | Product18 | | | | | | | | | | | | | | | | 144 | 5 x 28 + 4 | | |
---|
22 | Product19 | | | | | | | | 60 | 2 x 28 + 4 | 72 | 2 x 28 + 16 | 84 | 3 x 28 | 164 | 5 x 28 + 24 | | | 112 | 4 x 28 |
---|
23 | Product20 | | | | | | | | | | | | | | | | 124 | 4 x 28 + 12 | | |
---|
24 | Product21 | | | | | | | | 30 | 30 | 42 | 30 + 12 | 84 | 2 x 30 + 24 | 48 | 30 + 18 | 126 | 4 x 30 + 6 | 54 | 30 + 24 |
---|
25 | Product22 | | | | | | | | 96 | 2 x 48 | 156 | 3 x 48 + 12 | 120 | 2 x 48 + 24 | 396 | 8 x 48 + 12 | 204 | 4 x 48 + 12 | 348 | 7 x 48 + 12 |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
| | | | | | | | | | | | | | | | | | | | |
---|
138 | | | | | | | | | 876 | 21 plts | 998 | 26 plts | 1161 | 31 plts | 2809 | 62 plts | 1972 | 49 plts | 2439 | 54 plts |
---|
139 | | | | | | | | | | 3 mix | | 4 mix | | 3 mix | | 5 mix | | 6 mix | | 4 mix |
---|
|
---|
The list looks the same every day expect rows (that's why sum of total pallets/mixes is located at row number 138/139 (to leave space for extending the potential product changes within order (now we have total 22 products {can be more or less each day} and other rows between last product (in this example row:25) and row:138 are hidden by default.
What I'm doing every day is to separate’s each location to different sheet and make space for handwrits so I can write down products that are ready and checked by me.
It would look like this (example of locations 1;2;3)
Loc.1
EXAMPLE.xlsx |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J | K |
---|
1 | | | | | | | | | | | |
---|
2 | | | | | | | | | | | |
---|
3 | | | | | | | | | Location1 | |
---|
4 | Product1 | | | | | | | | | | |
---|
5 | Product2 | | | | | | | | 288 | 3 x 84 + 36 | |
---|
6 | Product3 | | | | | | | | | | |
---|
7 | Product4 | | | | | | | | | | |
---|
8 | Product5 | | | | | | | | | | |
---|
9 | Product6 | | | | | | | | | | |
---|
10 | Product7 | | | | | | | | 36 | 36 | |
---|
11 | Product8 | | | | | | | | 6 | 6 | |
---|
12 | Product9 | | | | | | | | 72 | 2 x 36 | |
---|
13 | Product10 | | | | | | | | | | |
---|
14 | Product11 | | | | | | | | 81 | 45 + 36 | |
---|
15 | Product12 | | | | | | | | | | |
---|
16 | Product13 | | | | | | | | 24 | 24 | |
---|
17 | Product14 | | | | | | | | 12 | 12 | |
---|
18 | Product15 | | | | | | | | 36 | 36 | |
---|
19 | Product16 | | | | | | | | 63 | 36 + 27 | |
---|
20 | Product17 | | | | | | | | 72 | 2 x 28 + 16 | |
---|
21 | Product18 | | | | | | | | | | |
---|
22 | Product19 | | | | | | | | 60 | 2 x 28 + 4 | |
---|
23 | Product20 | | | | | | | | | | |
---|
24 | Product21 | | | | | | | | 30 | 30 | |
---|
25 | Product22 | | | | | | | | 96 | 2 x 48 | |
---|
|
---|
Loc.2
EXAMPLE.xlsx |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J | K |
---|
1 | | | | | | | | | | | |
---|
2 | | | | | | | | | | | |
---|
3 | | | | | | | | | Location2 | |
---|
4 | Product1 | | | | | | | | 18 | 18 | |
---|
5 | Product2 | | | | | | | | 264 | 3 x 84 + 12 | |
---|
6 | Product3 | | | | | | | | | | |
---|
7 | Product4 | | | | | | | | | | |
---|
8 | Product5 | | | | | | | | | | |
---|
9 | Product6 | | | | | | | | | | |
---|
10 | Product7 | | | | | | | | 48 | 48 | |
---|
11 | Product8 | | | | | | | | | | |
---|
12 | Product9 | | | | | | | | 81 | 2 x 36 + 9 | |
---|
13 | Product10 | | | | | | | | | | |
---|
14 | Product11 | | | | | | | | 135 | 3 x 45 | |
---|
15 | Product12 | | | | | | | | | | |
---|
16 | Product13 | | | | | | | | 36 | 24 + 12 | |
---|
17 | Product14 | | | | | | | | 12 | 12 | |
---|
18 | Product15 | | | | | | | | 9 | 9 | |
---|
19 | Product16 | | | | | | | | 9 | 9 | |
---|
20 | Product17 | | | | | | | | 116 | 4 x 28 + 4 | |
---|
21 | Product18 | | | | | | | | | | |
---|
22 | Product19 | | | | | | | | 72 | 2 x 28 + 16 | |
---|
23 | Product20 | | | | | | | | | | |
---|
24 | Product21 | | | | | | | | 42 | 30 + 12 | |
---|
25 | Product22 | | | | | | | | 156 | 3 x 48 + 12 | |
---|
|
---|
Loc.3
EXAMPLE.xlsx |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J | K |
---|
1 | | | | | | | | | | | |
---|
2 | | | | | | | | | | | |
---|
3 | | | | | | | | | Location3 | |
---|
4 | Product1 | | | | | | | | 9 | 9 | |
---|
5 | Product2 | | | | | | | | 204 | 2 x 84 + 36 | |
---|
6 | Product3 | | | | | | | | 162 | 3 x 45 + 27 | |
---|
7 | Product4 | | | | | | | | | | |
---|
8 | Product5 | | | | | | | | 72 | 45 + 27 | |
---|
9 | Product6 | | | | | | | | | | |
---|
10 | Product7 | | | | | | | | 48 | 48 | |
---|
11 | Product8 | | | | | | | | 4 | 4 | |
---|
12 | Product9 | | | | | | | | 63 | 36 + 27 | |
---|
13 | Product10 | | | | | | | | | | |
---|
14 | Product11 | | | | | | | | 135 | 3 x 45 | |
---|
15 | Product12 | | | | | | | | | | |
---|
16 | Product13 | | | | | | | | 72 | 3 x 24 | |
---|
17 | Product14 | | | | | | | | 12 | 12 | |
---|
18 | Product15 | | | | | | | | | | |
---|
19 | Product16 | | | | | | | | | | |
---|
20 | Product17 | | | | | | | | 92 | 3 x 28 + 8 | |
---|
21 | Product18 | | | | | | | | | | |
---|
22 | Product19 | | | | | | | | 84 | 3 x 28 | |
---|
23 | Product20 | | | | | | | | | | |
---|
24 | Product21 | | | | | | | | 84 | 2 x 30 + 24 | |
---|
25 | Product22 | | | | | | | | 120 | 2 x 48 + 24 | |
---|
|
---|
As You can see each location vary with products so there are empty rows – Order from
Location 1 no products with 1;3;4;5;6;10;12;18;20
Location 2 no products with 3;4;5;6;8;10;12;18;20
Location 3 no products with 4;6;10;12;15;16;18;20
That’s why I need to make each location on its own sheet so I could use CTRL-(Minus) to delete rows without affecting other locations, which could have that product.
My whole process is something like ->>>
Split locations to different sheets -> check for empty rows and delete them-> print the list -> check products -> hand write checked product -> add into system via PC by reading from the list I just wrote -> crossdown products I just put into system (WTF???)-> check rest products with other lists, depend how order is splitted.
Ex.
Location 6 - total order pallets = 54
And that will be splitted into 33p (max per truck) and 21p
that’s gives me 2 lists:
one for 33p
second for 21p
But… customers can split orders like they want, not only as the way on example above.
Like
15p
14p
13p
12p
=54p
And that would mean I need to print 4x identical lists to check every transport separately.
So…After completing 1st list with 15p
By handwriting I have to crossdown products on 2nd list with 14p - from 1st list with (15p) I just checked then I can check the rest products…repeat same with 3rd and 4th and so on..
As You can see this whole processs is idiotic and making me re-write same data over and over again with each list.
This is happening because products from orders are checked differently each day. I have only information about how its split by amounts of pallets within the location –
In first transport (15p) I can put any product from the order, so each day will be different, because there could be a day, in which there are already made 42pallets of different products so I would be able to fill list 1,2,3 (15p,14p,13p)
and only last 12pallets of products need to be waited for.
Other day I’ll have only 9p ready so I wont be able to complete even 1st list…
etc etc
I tried to explain whole data-relation process, the best way I can and I hope it at least on ‘decent’ status of being readable…
My whole idea is to end with this stupid re-writing process and implement something different like working only on excel without printing papers at all.
My best idea so far is that, I could use Tablet instead of pen and paper. So in real-time I could change the checked product values -> then with proper formula/macro/whatever the whole data would modify.
That’s why earlier I wanted to change”
7 x 48 + 12” to 48,48,48,48,48,48,48,12 because with tablet I can just click 48,48.48.12 adding it to 1st list, then excel could automaticly generate other sheet for 2nd list but with changes making it
“4 x 48”
(I added already 3x pallets with 48 boxes + 12 boxes to the 1st list, so there are only 4x48 left of that specific product.
Like I mentioned, right now I would have 4x identical printed lists of location 6 with “7 x 48 + 12” of X product, and every changes is related with crossing down with pen making it less and less visible and readable.
I have my fingers crossed that You’ll be able to understand the way of my thinking and the methods of dealing with those sheets. Of course that tablet idea is just idea, if there are any better solutions to make it easier/faster to deal with data, please let me know.
With regards,