Hello all,
I have been tasked with an assignment to make our Bill of Material management a wee bit easier. I should also mention that I can barely operate excel, and know next to zero VBA code. My only saving grace is I have taken a coding class in the past, so I can understand structure, to an extent.
Background: We export our Bill of Materials from Autodesk Inventor, and format them into excel. It includes various columns, the only one I'm concerned about right now is the drawing number.
Engineering saves drawings (CAD format) as a 6 digit number: 123456 for example. We add a -1 or a -2 in our Bill of Materials (in excel) to differentiate from right hand and left hand sides of a machine (123456-1 or 123456-2). When (but not always) sales/purchasing people get their hands on the drawing list, they add the sheet size (up front) and revision (after the drawing number but before the -1 or -2 for RH/LH differentiation) to the drawing column in excel (D123456-A, C123456-B-2, etc). If a part doesn't need a drawing (such as buying a bolt or washer from a store), we just put in "---" as the drawing number, since it doesn't need one. When it comes time to export our drawing package to a fabrication shop, we need to strip the added information, as well as the -1 or -2, so all we have are the original drawing numbers (123456). I know, it's a lengthy process, but being the new guy at the bottom of the totem pole, I don't ask questions
Here are a few real world drawing numbers as examples:
D338225-A-1
B337212-A
A338993-A
D338225-A-2
335629-1
335712
---
<tbody>
</tbody>I have found a macro (https://www.mrexcel.com/forum/excel-questions/545660-macro-delete-all-text-cells-leave-numbers.html post #2) that does almost exactly what I would like. There are a couple more steps that I need to add in order to make our lives even easier.
The final value of the cell needs to reflect the drawing number that engineering saves as (123456) with alpha characters removed, and everything past the "-" removed. So, copying from the real world list, the final product in excel would be:
338225
337212
338993
338225
335629
335712
(---) would like to be a cell removed, then everything shifted up
I have tried adding a Left or Right statement to Ron Coderre's macro, but it ends up stripping all the information from the cell.
If there is VBA code that can accomplish this, I would be happy.
Bonus round:
Unfortunately, we have some older drawings (predating the computer era) that do not fit under this format structure (C10-84941-3 is a real world example), but are also fairly frequently included in the Bill of Material. The C10 portion is what cabinet the hand drawing was stored in, the 84941-3 was the actual drawing number. Would there be a way for the macro to look at a worksheet with a white list of drawing numbers that we can add manually? So when the filtering macro runs, it checks to see if the drawing is on the white list, then ignores that cell?
Thanks in advance for all the help. I'm sure I'll be coming to this board frequently for various tasks I get assigned.
I have been tasked with an assignment to make our Bill of Material management a wee bit easier. I should also mention that I can barely operate excel, and know next to zero VBA code. My only saving grace is I have taken a coding class in the past, so I can understand structure, to an extent.
Background: We export our Bill of Materials from Autodesk Inventor, and format them into excel. It includes various columns, the only one I'm concerned about right now is the drawing number.
Engineering saves drawings (CAD format) as a 6 digit number: 123456 for example. We add a -1 or a -2 in our Bill of Materials (in excel) to differentiate from right hand and left hand sides of a machine (123456-1 or 123456-2). When (but not always) sales/purchasing people get their hands on the drawing list, they add the sheet size (up front) and revision (after the drawing number but before the -1 or -2 for RH/LH differentiation) to the drawing column in excel (D123456-A, C123456-B-2, etc). If a part doesn't need a drawing (such as buying a bolt or washer from a store), we just put in "---" as the drawing number, since it doesn't need one. When it comes time to export our drawing package to a fabrication shop, we need to strip the added information, as well as the -1 or -2, so all we have are the original drawing numbers (123456). I know, it's a lengthy process, but being the new guy at the bottom of the totem pole, I don't ask questions
Here are a few real world drawing numbers as examples:
D338225-A-1
B337212-A
A338993-A
D338225-A-2
335629-1
335712
---
<tbody>
</tbody>
The final value of the cell needs to reflect the drawing number that engineering saves as (123456) with alpha characters removed, and everything past the "-" removed. So, copying from the real world list, the final product in excel would be:
338225
337212
338993
338225
335629
335712
(---) would like to be a cell removed, then everything shifted up
I have tried adding a Left or Right statement to Ron Coderre's macro, but it ends up stripping all the information from the cell.
If there is VBA code that can accomplish this, I would be happy.
Bonus round:
Unfortunately, we have some older drawings (predating the computer era) that do not fit under this format structure (C10-84941-3 is a real world example), but are also fairly frequently included in the Bill of Material. The C10 portion is what cabinet the hand drawing was stored in, the 84941-3 was the actual drawing number. Would there be a way for the macro to look at a worksheet with a white list of drawing numbers that we can add manually? So when the filtering macro runs, it checks to see if the drawing is on the white list, then ignores that cell?
Thanks in advance for all the help. I'm sure I'll be coming to this board frequently for various tasks I get assigned.
