+ Reply to Thread
Results 1 to 11 of 11

Condensing Bill of Materials

  1. #1
    Registered User
    Join Date
    03-23-2016
    Location
    England
    MS-Off Ver
    2007
    Posts
    6

    Condensing Bill of Materials

    Hi all,

    I occasionally receive BOMs which are not condensed correctly.

    I receive them in this format:

    Part Number Reference Part Purchased Distributor
    001 A1 2431 Dist1
    002 A2 3402 Dist1
    003 B1 1104 Dist1
    003 B2 1104 Dist1
    004 B3 F304A Dist2

    However, I would like to change it to this using a macro

    Part Number Reference Part Purchased Distributor
    001 A1 2431 Dist1
    002 A2 3402 Dist1
    003 B1 B2 1104 Dist1
    004 B3 F304A Dist2

    Is this at all possible?

    Many thanks,

    Andrew Stanton
    Last edited by AndyStanton; 03-23-2016 at 09:19 AM. Reason: Got columns and rows mixed up :)

  2. #2
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Condensing Bill of Materials

    Please Login or Register  to view this content.
    I'm interested in starting a career working with VBA, if anyone knows of any opportunities!

  3. #3
    Registered User
    Join Date
    03-23-2016
    Location
    England
    MS-Off Ver
    2007
    Posts
    6

    Re: Condensing Bill of Materials

    Whenever I attempt to run the macro, I get the error: 'Object Required'

    Sorry but i'm new to the macro game, is it possible for a step-by-step guide on how to insert it to the file?

  4. #4
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Condensing Bill of Materials

    If you're getting that error it's likely in the right spot. CAn you post your workbook, or an example workbook with sample data that gives you the same error?

  5. #5
    Registered User
    Join Date
    03-23-2016
    Location
    England
    MS-Off Ver
    2007
    Posts
    6

    Re: Condensing Bill of Materials

    Here is my test macro-enabled workbook

    https://www.dropbox.com/s/429c8hmhou...Test.xlsm?dl=1

  6. #6
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Condensing Bill of Materials

    Mixed up the loop a bit, my bad. Try:

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    09-23-2007
    Location
    Melbourne, Australia
    Posts
    173

    Re: Condensing Bill of Materials

    Hi
    I had a go at this one as well. My code is a little drawn out but it's flexible in terms of where you put the table and what you name the sheet. The macro name is 'reformat_table'.

    Probably allot more drawn out than it needed to be but at this stage of the night most things are. I was getting some funny errors so everything expanded out has prevented this.

    Let me know how it go's.

    BOMS.xlsm

  8. #8
    Registered User
    Join Date
    03-23-2016
    Location
    England
    MS-Off Ver
    2007
    Posts
    6

    Re: Condensing Bill of Materials

    Hi Danny2000,

    I have tested yours and it seems to work perfectly on a full sized BOM, except for one thing- on this one, there are lots in the 'Parts Purchased' column which have the same name. Because of this, I have to run the macro multiple times to condense everything down.

    Many thanks,

    Andrew Stanton

  9. #9
    Registered User
    Join Date
    03-23-2016
    Location
    England
    MS-Off Ver
    2007
    Posts
    6

    Re: Condensing Bill of Materials

    Also,

    apart from the issue i mentioned above, this program works perfectly! thank you!

  10. #10
    Forum Contributor
    Join Date
    09-23-2007
    Location
    Melbourne, Australia
    Posts
    173

    Re: Condensing Bill of Materials

    Quote Originally Posted by AndyStanton View Post
    Hi Danny2000,

    I have tested yours and it seems to work perfectly on a full sized BOM, except for one thing- on this one, there are lots in the 'Parts Purchased' column which have the same name. Because of this, I have to run the macro multiple times to condense everything down.

    Many thanks,

    Andrew Stanton
    No worries. Can I say that the logic I used was this.

    If I found a duplicate 'Part Number' the macro then checks if that previous same 'Part Number' has the same
    'Part Purchased' and 'Distributor'. If these did not match between duplicate part numbers then I did not identify that as a duplicate 'Part Number'.

    Having said this, I see what you mean. That complicates things a little more.

    It's easily corrected if need be.

  11. #11
    Registered User
    Join Date
    03-23-2016
    Location
    England
    MS-Off Ver
    2007
    Posts
    6

    Re: Condensing Bill of Materials

    Yes, it would be nice, but not necessary.

    Many thanks,

    Andrew Stanton

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. BOM (Bill of Materials) Explosion
    By AndySimm2000 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-18-2016, 06:06 AM
  2. Multi-level Bill of Materials
    By ahouston in forum Excel General
    Replies: 0
    Last Post: 07-02-2015, 12:11 PM
  3. Help with Designing a Bill of Materials!
    By blakewalker in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-22-2012, 11:52 AM
  4. Bill of materials form
    By JESSIER4025 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-08-2010, 08:38 PM
  5. Bill of Materials
    By tonyhindmarsh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-19-2007, 05:37 AM
  6. bill of materials
    By stevekirk in forum Excel General
    Replies: 7
    Last Post: 12-10-2006, 05:12 PM
  7. Bill of Materials / Router
    By MStim in forum Excel General
    Replies: 0
    Last Post: 09-14-2005, 10:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1