Search spreadsheet for quantities of part numbers occurring multiple times

  • 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 sub-parts and quantities of these sub-parts per unit. I need to sort all of the sub-parts 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?

  • 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


    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 sub-part numbers.

  • 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 (NTF1014-04, NTF1014-07, 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 PivotTable-friendly 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.:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!