+ Reply to Thread
Results 1 to 6 of 6

Creating Replacement Parts List

  1. #1
    Registered User
    Join Date
    11-05-2013
    Location
    Sauk Centre, MN
    MS-Off Ver
    Office 365
    Posts
    11

    Creating Replacement Parts List

    Hello,

    I have a list of part #'s that has the original part in column A and the replacement part in column B. I need to build this into a list that references all part #'s that are associated to the newest part in the supersede chain. Where part A is replaced by part B and that is replaced by part C, etc.. I have been able to put those numbers all in a row using Vlookup. What I am having difficulty figuring out how to accomplish is looking for other parts in the worksheet that should also be included in the row and then combine this information and eliminate duplicates. On the attached worksheet I have colored all the cells that contain part # 5214-077. I would like to take all that information and list all parts the newest part replaces in a single row on a new sheet with the newest part (Column G) shown in column A. The corresponding values would be in individual cells starting with column B in that row. It would also be ok if all the data was placed in column B separated with commas. The the process would then need to repeat, looking at each unique part # in column F and returning 1 row for each individual part # with the value in column F shown in Column A, repeated again for columns E, D, & C. Is this possible in Excel? Any help is appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Creating Replacement Parts List

    =IFERROR(INDIRECT("l!"&TEXT(AGGREGATE(15,6,(ROW(RNG)*10000+COLUMN(RNG))/(RNG<>"")/(COUNTIF($B2:B$2,RNG)=0)/(RNG<>$A2),COLUMNS($B2:B$2)-COUNTA($B2:B$2)),"R000000C0000"),),"")
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-05-2013
    Location
    Sauk Centre, MN
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Creating Replacement Parts List

    tim201110 thanks for the information. That is exactly what I need. I apologize for my lack of knowledge using complex formulas in Excel, but how do I expand this to look at the remaining rows of part #'s to generate a complete list for each of those as well? And after all the data is compiled, what would I need to do to arrange the information in each row to be numerical starting with column B? Thanks.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Creating Replacement Parts List

    As far as expanding to look at the remaining rows of part #'s I would suggest the following:
    1) Add a column to the I(nput) sheet populated using: =INDEX(A2:G2,AGGREGATE(14,6,(A2:G2<>"")*COLUMN(A:G),1))
    2) Populate A2 on the O(utput) sheet using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    3) Modify Tim's formula in cell C2 to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    4) Drag the fill handle of cell C2 across to cell AB2
    Note Be sure that you will not need to use your computer for a long time before you proceed to the next step.
    5) Select A2:AB2 and drag the fill handle down to row 12938 (cell O1 on Input sheet) Note that it took my dual processor machine @ 15 min to process even 8% of this, so you may want to start this as you are leaving for the evening.
    As to arranging the information I would suggest a pivot table.
    I have modeled part of this proposal, 10 rows worth, in the attached file.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    11-05-2013
    Location
    Sauk Centre, MN
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Creating Replacement Parts List

    Hi JeteMC,

    Thanks for the help! This worked great. I now have all the 'old parts' in a single row with the corresponding 'new part' in column A.
    The one item I can't figure out how to accomplish is sorting the 'old' part numbers in each row in numerical order.
    The only information I can find on sorting rows to select the information to be sorted in the row and use custom sort to sort left to right.
    This does work, but can only be done 1 row at a time. Is there a formula that can be used that would do this?

    Any help is appreciated.

    Thanks,

    CountryCatMark

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Creating Replacement Parts List

    Perhaps this will help.
    Columns AD:BC convert the part number to a numeric value using: =IF(C2="",FALSE,VALUE(SUBSTITUTE(C2,"-","")))
    Columns BE:CD sort the numbers in smallest to largest order using: =AGGREGATE(15,6,$AD2:$BC2,COLUMN(A:A))
    Columns CF:DE display the part numbers in the original format using: =IFERROR(TEXT(BE2,"0000-000"),"")
    Note that you may choose to hide columns C:CE.
    Let us know if you have any questions.
    Attached Files Attached Files

+ 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. Creating Automotive parts kits.
    By wtwos in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 07-03-2018, 02:02 PM
  2. Creating a Parts list
    By Thomas Morris in forum Excel General
    Replies: 2
    Last Post: 03-19-2018, 01:01 PM
  3. [SOLVED] Creating a Parts List from an Equipment Database with checkboxes for each row
    By JoeyFinnz in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-04-2016, 01:32 PM
  4. [SOLVED] using Large() and Max() without replacement from a list
    By mmccleve in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-21-2013, 04:47 PM
  5. Scan parts OUT to Sheet1, parts IN to Sheet2 and list in Sheet3
    By Nu2Java in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-12-2012, 11:40 AM
  6. creating a parts list
    By artj in forum Excel General
    Replies: 3
    Last Post: 07-29-2012, 06:42 PM
  7. Creating a used parts list?
    By danjstock in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-17-2008, 10:14 AM

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