I have a spreadsheet (summary) with exported unit quantities and attributes from AutoCAD. Each unit has a row with quantities, locations (floor level), and all subparts and quantities of these subparts per unit. I need to sort all of the subparts into quantities per location on a separate sheet (TC by floor). How do I search the entire field for the part number, add the quantities for each occurrence of the part number and multiply it by the unit count?
Search spreadsheet for quantities of part numbers occurring multiple times



Re: Search spreadsheet for quantities of part numbers occurring multiple times
Can you link a copy of the spreadsheet you're exporting from AutoCAD? Would help give more specific results. There's a post here http://www.ozgrid.com/forum/showthread.php?t=25239 that shows part of what I think the solution could be... since you'll have items that you will be looking up by number that will have multiple variables after the item #. Also this shows some solutions that are through vb and that are not...

Re: Search spreadsheet for quantities of part numbers occurring multiple times
In Excel: looking for multiple instances of a value= filtering.

Re: Search spreadsheet for quantities of part numbers occurring multiple times
I wanted to upload the file, but I'm having some difficulty. The webpage gives me an error every I want to upload the file. Is there a trick to this?

Re: Search spreadsheet for quantities of part numbers occurring multiple times
Ahh, sorry, got it now. The file was too large, even though it was only 190KB.
Here is a cut down version of the same table.
The thread Rowddog linked to comes close, but I can't any solution to add the multiple occurring subpart numbers.

Re: Search spreadsheet for quantities of part numbers occurring multiple times
Excel 2010, Table, PivotTable
With DeNormalize.
Needs further processing for undefined "SubPart".
http://www.mediafire.com/downl…6hd3qjcb71/08_08_13a.xlsx 
Re: Search spreadsheet for quantities of part numbers occurring multiple times
Thanks Herbs. This is really close, but the only problem I still have is, I need to multiply the parts per unit with the unit count in column A of the 'summary' sheet. Unfortunately the portion I showed in the example only had units with 1 unit, but I have units with varying quantities as well.
Also, TC01, TC02, etc. aren't part numbers in themselves, but attribute titles from AutoCAD to show the part number (NTF101404, NTF101407, etc...). So, a placeholder of sorts.

Re: Search spreadsheet for quantities of part numbers occurring multiple times
Included Units, removed Type.
Same link. 
Re: Search spreadsheet for quantities of part numbers occurring multiple times
Awesome Herbds! This is exactly what I needed. Can you give me a brief breakdown of how you got this?
Thanks!

Re: Search spreadsheet for quantities of part numbers occurring multiple times
Way too complicated to treat in a brief summary.
The proper way is to build a PivotTablefriendly table (like Table3)
in AutoCAD and export it to Excel.
What version of AutoCAD to you have?
Upload *.dwg 
Re: Search spreadsheet for quantities of part numbers occurring multiple times
Unfortunately I don't have access to the DWG file this was exported from, and the guy that did it originally is on vacation, which is why I'm trying to figure this out on my own now:(
I was able to compress the entire takeoff that I need and attach it. Maybe you could work your magic on this one? I'd be super grateful.:) 
Re: Search spreadsheet for quantities of part numbers occurring multiple times

Re: Search spreadsheet for quantities of part numbers occurring multiple times
Thanks for all the great help!
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!