Automating Template Workbook: Breaking Up Material Table into 3 Different Tables
Hi, I have attached a workbook that I would like to automate as much as possible but am not familiar enough with VBA to do so.
The "EquipmentList" tab is sent to us by our vendors when we request a quote and is not changed by us. As you can see it is a single list where the headers are repeated throughout as the type of equipment is changed (Pipe,Elbows,Tees,etc.) this list changes with each project so having a macro that could recognize when moving from a new type would be ideal.
In the comments section, this is a confirmation that the Manufacturer listed is one of the approved manufacturers in the "Manufacturers" tab. There are exceptions to this so one not being approved doesn't kill the project they just need approval so highlighting those that aren't approved in that list would be ideal. There are so many manufacturers that a formula gets too tedious to write (I've tried).
The three remaining tabs "Pipe", "Valves", "Fittings" are created from the "EquipmentList" tab. These take columns A and D are combined using the CONCATENATE function and a column assigning a control # (P-pipe, V-valve, F-fitting) and a second column to manually input a serial #. Pipe in the Equipment list correlates to the "Pipe" tab, Valves to the "Valve" tab and EVERYTHING ELSE to the "Fittings" tab. I have been able to accomplish a rough version of this simply using formulas but for the fittings it gets ugly. There are examples of what the final product should look like.
Again the idea would be to automate as much as possible to avoid human error which occurs regularly, currently nothing is connected and each user uses their own discretion. If anyone can help especially with the fittings portion this would be amazing! Have a nice day!
|