Random Thoughts....

IceBone

Blue Wheel Hipster
Joined
Jan 14, 2007
Messages
27,232
Location
Slovenistan
Car(s)
Audi A5 Quattro
Several hours of product photography tomorrow. Hope my batteries last. If this repeats often enough, I should invest in an AC adapter. :think:

- - - Updated - - -

Just looked it up, holy hell it's expensive...
 

Nabster

Has Slutty Mustangs
Joined
Oct 15, 2007
Messages
4,581
Location
DFW
Car(s)
1969 Mustang Coupe, 2019 Mustang GT, 2011 F150
I now know for sure I couldn't do an office type number crunching job or one which otherwise involves a lot of Excel work.

I've been working on a special project at work this week, instead of my normal watch repair work I've been given the exciting task of building new kit boxes for my brand. There are two types of boxes I'm building, one for watch cases and one for individual movements. The boxes contain all the individual parts and components needed for each specific model of watch and each different movement. The case kits have between 40 something and 70 something individual components, which all need to be assigned compartments in the box (the boxes have interchangeable inner compartments of 3 different sizes) which are appropriate for size of the part and also arranged so all the necessary parts fit in as few boxes as possible. This part is basically Tetris, so no real problems there as far as I'm concerned. It was more of a bear than it should have been to get the templates the correct physical dimensions because of course Excel doesn't have a working feature to input specific real world dimensions for cells- what it does have for that takes the exact dimension you input and converts it to the nearest unit of whatever dimension it uses, so the sizes all come out slightly different once printed.

The main problem comes in on the end of getting the lists of components and organizing them into templates/keys which are fixed on the box to show which parts are in which compartment. There are also two versions of each template; one for the watchmakers in the shop on the outside of the kit box which contains the part numbers (PGMX600256 for example) and a description of the part (Caseback Gasket), and one for the materials department which has the part number and a QR code instead of the description. When the box is empty of a part, materials will have either a book of the templates for the kits or a second template on the inside of the box with the QR codes, they scan the appropriate QR code for the out of stock part and it loads up the part number and reorder quantity in the computer.

To make these templates I have to manually go in and place each piece of data. Where the data comes from varies, and that's one of the major problems. There's no single uniform source. A lot of it comes from preexisting Excel files, but most of them are all formatted and organized differently, so I can't automate a process to migrate data over into the blank templates I've made. I have to open up each "master" list of parts and organize and format all the pieces of data. Some of the part numbers have extra prefixes which aren't needed, so I have to run functions to strip those. The descriptions are all over the place, some of them are in all caps and have about 6 pieces of extra information like "SCREW FOR BARREL BRIDGE GENEVE DECORATION DIMENSION: 1.35MM MATERIAL: STEEL COMPATIBLE: CALIBRE 500P 504P" some of them are obtuse or nonsensical Swiss-French to English translations like "intermediate intermediary wheel" and some have 8 different parts all simply labelled "screw" so I have to track down a second source of the parts information and cross reference the part number to find a more specific description for each screw. In any case I have to manually edit 80%+ of the descriptions for each part. In addition I also have to manually assign each part a reorder quantity that makes sense for both the usage rate of the part and the package quantity the parts ship in (some come packed individually, some come in quantities of 2 or 5) so I avoid situations of having a reorder quantity of 5 for a part which only comes in packages of 2.

Once I've got all the information properly arranged and formatted I can copy it to my templates, but of course the visual format of the cells messes up with copying and pasting from one sheet to another because by default Excel keeps the source formatting instead of the destination formatting. I have to manually go in (with each individual paste command, mind you) and do an extra two or three clicks to open the "special paste" options to paste and use destination formatting, but if I do that I can't also transpose the copied cells from vertical order to horizontal order, it's either one or the other and Excel lacks the options for setting up default paste styles.

To get the QR codes we have a little automated program I have to copy and paste the part numbers and reorder quantities into for it to generate the codes. I can at least run these as a batch of all 40-70 parts for each template, but then I have to manually go and place each QR code in the right spot on the template anyway.

So if you're still following that's a part number, a description, a reorder quantity, and a QR code- so 4 pieces of data for each component. The average is probably right around 60 components for the case kits and 70 for the movement kits. So far I've finished all the case kits, 21 of them. I've also done I think 14 movement kits, and another 7 or so to go I think. That's about 8,960 pieces of information I've had to individually move around and edit in Excel over the last week, with about 2,000 left to finish. I really don't like Excel at all for this, it's terrible for working with variable formatted information. Up next I get to physically put together 2 boxes of each kit so we can have a sort of hot swap setup when one kit runs out we swap it for a full one and materials orders and refills the other one.

TL;DR: Manually placed almost 9000 individual cells of information in Excel this week, and not quite done.

The good news is, because I'm a watchmaker, the templates and information are all going to be formatted logically and usefully for us watchmakers and simple for materials to refill correctly. We've had the materials department working with the factories for other brands to do this sort of work for many of the other brands in our facility, but it's been very slow (months long) and the end result isn't great for us in the shop, there's lot of complaints about it from the other watchmakers using them. The brand I work hasn't provided us with these new layouts, so they're having me make them with hopes that they'll turn out a bit better than the ones for the other brands.

So... Should we do another Texasmeet?

I'd be game I suppose, just not the 19th as I already have plans.
 

Adunaphel

KLAUWD
STAFF MEMBER
Joined
Jan 18, 2006
Messages
11,574
Location
Fermany
Car(s)
'18 Clio; '21 Cube Aim SL
I think the real issue here is that you're trying to make Excel do stuff it was never designed to do in the first place. Right tool for the job, etc.
 

Cowboy

My name is Sheridan
Joined
Mar 31, 2009
Messages
9,852
Right tool for the job, etc.


31pbQOZGL9L._SX425_.jpg
 

eizbaer

Forum Addict
DONOR
Joined
Nov 24, 2008
Messages
6,033
Location
Cologne, Germany
Car(s)
Tesla Model 3
Does it come with another 5 smartphone pockets?
At the airport thursday and friday I encountered multiple people in the security check that had a laptop plus multiple tablets with them... so there seems to be a market at least, even if I don't understand why :dunno:
 

Dr_Grip

Made from concentrate
Joined
Jul 8, 2008
Messages
14,553
Location
Germany
Car(s)
1979 Opel Kadett | 1972 Ford Country Sedan
Does it come with another 5 smartphone pockets?
At the airport thursday and friday I encountered multiple people in the security check that had a laptop plus multiple tablets with them... so there seems to be a market at least, even if I don't understand why :dunno:
Obvious explanation: Work laptop, work tablet (for presentations and stuff), personal tablet/e-book reader.
 

MWF

Now needs wood
Joined
May 29, 2008
Messages
28,284
Location
MWF HQ, Ukadia
Car(s)
MX-5 1.8i Indiana SE, update pending
Slacker.
 

GRtak

Forum Addict
Joined
Sep 6, 2008
Messages
21,467
Location
Michigan USA

Heathrow

Yes, as in the airport.
Joined
Aug 2, 2008
Messages
6,391
Location
London, UK
Car(s)
1995 BMW 325i SE
I think the real issue here is that you're trying to make Excel do stuff it was never designed to do in the first place. Right tool for the job, etc.

Exactly what I thought, too.

Nabster, not sure what you need but it would be something like a manufacturing/inventory PC application of some type, specialising in you market of high precision engineering. :?
 

Nabster

Has Slutty Mustangs
Joined
Oct 15, 2007
Messages
4,581
Location
DFW
Car(s)
1969 Mustang Coupe, 2019 Mustang GT, 2011 F150
I think the real issue here is that you're trying to make Excel do stuff it was never designed to do in the first place. Right tool for the job, etc.

Maybe, but I figure you should at least be able to copy and paste cells between sheets and choose how it handles formatting without having to do 3 extra clicks for each paste. That's the main problem. And it seems like a major oversight, from the half an hour of searching online I did there are tons of other people annoyed that that version of excel lacks those features because they existed in previous versions. :dunno:

I guess maybe I could do extra formatting conversions on the source data before copying it over, but it's just more work that could be avoided if other options were available. There's no winning I think, but that's mostly due to the source material I'm stuck with. They weren't thinking ahead when they originally made all the files.
 

NecroJoe

Stool Chef
Joined
Apr 12, 2005
Messages
20,239
Location
San Francisco area, CA, USA
Car(s)
2015 Mazda 3 S GT, 2015 VW e-Golf
Maybe I'm misunderstanding, but isn't there a right-click way to paste the copied content into the new sheet's formatting?
 

Nabster

Has Slutty Mustangs
Joined
Oct 15, 2007
Messages
4,581
Location
DFW
Car(s)
1969 Mustang Coupe, 2019 Mustang GT, 2011 F150
Yes, if you right click and open the special paste menu and then expand it down it has the option for keeping destination formatting. In my case I was typically pasting multiple cells at once, and usually going from vertical orientation to horizontal, so I had to use the special paste option to transpose the values for that instead. You can't do both, only one special option per paste. Also you have to do that for each paste, it's not a saved option or one that sticks beyond the single paste. If you want to do both transpose and keep formatting you have to go deeper and go to the advanced paste option dialog box where you can select a few different options, but again, this is more clicks and it doesn't save that option for subsequent pastes either.
 

Adamar

Viswijf
Joined
Nov 29, 2009
Messages
1,909
Location
Belgium
Car(s)
Mercedes Benz A Limousine (2019, BMW F700GS (2014)
Yesterday i was like tl;dr. But after reading some replies i kinda got intrigued and just read all of it.

And now i still don't know what you want to do.

But excel is fucking awesome!
 

Nabster

Has Slutty Mustangs
Joined
Oct 15, 2007
Messages
4,581
Location
DFW
Car(s)
1969 Mustang Coupe, 2019 Mustang GT, 2011 F150
Yeah. I'm probably not explaining it as simply as I could be, because it's really not that complicated of a final result. It's just cells with mixed content next to each other- text, numbers, and QR codes. It would be much less problematic if I simply typed everything in by hand instead of copy/pasting, but then I'd be spending two to three times as much time putting it all together.

The good news is having this weekend away from it has given me a little time to idly think about things, and I think I've got a few changes I can make to the flow to speed things up some. Ranting sometimes helps in that it gives you a chance to state everything and look at it from the third person perspective.

Looking forward to resuming normal work in any case.
 
Top