|
This project used Excel, Visual Basic, Excel Macro’s, and Creating Button’s within the Excel Spreadsheet making it as simple as possible to build the data to be used. This was a fun project. The goal was to enter into a database many parts and keep a consistent description among common parts. First was to establish what an acceptable description would be, then make sure every entry was typed in that way.
Previously they had no consistent way and looking them up in the database could never guarantee that common parts were together, so my goal was to change that and make it user friendly. The description came from the part number of the vendor. Every few digits defined either what category it was, what size it was, what thread it had, how many connector pins it had, what configuration those pins were in, what angle the connector was, 90 degrees or straight, and what color finish it had.To come up with a description, you had to go from page to page to find a chart. On that chart you used a cross reference to get a small part of the description. Then go to another page, (not necessarily near that page) and find another chart, go through its cross reference to find another piece of the puzzle, which was just another small piece of the part description…you can see how time consuming this could be. And having many parts to enter into the database, it would be a very lengthy project, not to mention opportunities for typo’s or duplication.
On top of that, the company didn’t want to have their parts description the same as the vendor’s description, they wanted their own. So that added more difficulty to the project to not only get a description, but rearrange the description per project. Couldn’t there be some automated way to just type in what existed, (the vendor’s part number) and have it spit out the description the way the client wanted it? Yes, but that took a little programming, and Excel had the perfect combination of built in tools to make it happen. And most everybody has Excel, so there was no software to purchase, only the time to sync all that data into a user friendly format. The clickable image above shows the complex requirements and how much data it looks through to obtain just a description of the part. Much of the data it uses is not shown, but I found it handy to have some of it for ‘Quick Reference’ when the user was making entries to act as a ‘Cross Reference’ to verify they were on the right track, or help troubleshoot any issues if the results were not what they were expecting.
You can see from all that ‘Double Checking Reference’ features, that its second nature to me in creating this type of work. Breaking things down and analyzing a process, to make it a smooth system.
Some parts of the description would automatically update the description as you type, other parts required a button pressed to update it due to the complexity of the data it had to look up. The end results can be seen in the middle of the screen in the Yellow background to the right of the orange cell with the word Description in it. Most of the page is color coded to help be a cross reference to watch if the end result updated correctly while entering the customer part number.
Some parts of the part number was actually a combination function that had to be in that order to have an intelligence to making the whole part number work together, so I listed all those combination on the page too so as the user was entering the part number, they could double check themselves that they were entering things correctly. Entering in the wrong combination would cause garbage to populate the yellow area, and having a quick reference area to trouble shoot that was very beneficial.
The whole process only took as long as it took the user to enter in the part number, and click the ‘RUN’ button, it was very fast. The next step was for the user to copy the final description in the yellow, and paste it as ‘Values Only’ to the right of it in the next yellow area. The reason for this is that the first description was generated by several ‘Concatenations’ within Excel, all brought together from above it in individual cells that were populated from the part number. That area was the original result from the working program, the yellow area just brought it all together, but a format was needed that was in raw ansii format that could be copied and pasted into another piece of software that was used by the database, so thats the need for the copy ‘Values Only’ within Excel.
I just love it when a plan comes together, as you heard Colonel ‘Hannibal’ Smith of the A-Team say. Sure the result of having a correct database is nice, but I enjoyed creating this project and all its requirements as much as a user would have running the program. It’s just nice knowing you can make someone’s job easier, which is my motto.
If something like this interests you and how it can save your group time and cost, then we have something in common. You have a need, and I have a solution. Please contact me and lets talk.
|
|
