Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Index/Match (2000)

    Using Microsoft Query I have retrieved a list from our SQL database that gives all of the Parent Parts Number in col. A and in col. B it lists all of the Minor Part Numbers that go into making up the Parent Part. I tried using the Index/Match command to retrieve a all of the minor parts but that command only returns the first value it finds then stops. What I want to do is in, say, Sheet 2 I want to type in a Parent Part Number and have Excel return all of the Minor Part Numbers. Does anyone have any suggestions on how I can do this?
    There could be as few as 3 Minor Parts and as many as 10.
    Thanks in advance for any help that you can provide.
    Stats

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index/Match (2000)

    Are all of the minor part numbers in one cell in column B (that is what your description sounds like)? If so, it sound like all you need is a VLOOKUP function that returns column B when it gets a hit on the part number in column A.
    Legare Coleman

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Index/Match (2000)

    Name your ranges ParentParts for col A and col B SubParts
    Enter the numbers 1-10 in A1 - A10 (you said that the most subparts is 10, change as needed
    In B1 is the cell to add the ParentPart
    In C1 enter in the Array formula (do not enter {}s, enter with ctrl-shift-enter)
    {=IF(A1>COUNTIF(ParentParts,$B$1),"",INDEX(SubPart s,SMALL(IF($B$1=ParentParts,ROW(ParentParts),""),A 1)))}
    Copy this into c2:c10
    When B1 is changed, the subparts are displayed. If a part is not listed in table, no subparts are displayed

    Other options:
    Try the data - filter - autofilter to get a list. You can copy this list. You can also use SUBTOTAL to get info on the items that are shown in the filter (count, avg, min, max, etc)

    You could also use the advanced filter to extract it out or write a macro to do all the extraction.
    Steve

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Index/Match (2000)

    Hi Stats,

    Take a look at the attached workbook. I think it'll do what you want.

    Sheet1 holds all of the Part & SubPart Nos. in Columns A & B, which are named Part & SubPart, respectively. Sheet2 extracts the SubPart list for a given Part No. (you type the required Part No. into cell A2). Column A in Sheet2 interrogates the Part list on Sheet1 to get a count of the No. of times the Part No. is repeated. This then populates the required No. of rows in Sheet2 with the Part No. Column B in Sheet2 uses the results in ColumnA for an index/match routine, offsetting the starting point each time to get the next SubPart No.

    Cheers

    PS: If you don't want to see the repeated Part Nos. on Sheet2, you could format them to the same colour as the background.
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index/Match (2000)

    Good Morning Legare,
    No, all of the minor parts appear in their own cell. MS Query returns them roughly as follows:
    A B
    1 FGABC1.0 BTTOR1.0
    2 FGABC1.0 CPWHFC
    3 FGABC1.0 BXABC1.0
    4 FGBBO500 BTTOR500
    5 FGBBO500 CPWHFC
    6 FGBBO500 TRGEN500

    There are over 500 different parent parts that appear in col A.
    Thanks
    Stats

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index/Match (2000)

    I would use the VLOOKUP function. In the attached workbook, Sheet1 is a sample of what your table might look like. Sheet2 shows how to use VLOOKUP to get the sub-partnumbers.
    Attached Files Attached Files
    Legare Coleman

  7. #7
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index/Match (2000)

    Thank you very much Macropod, it worked great. FYI because our parts are alpha numeric instead of numeric as in your example, I used COUNTA instead of COUNT in col A of Sheet 2.
    If I may throw a small curve ball at you, how would I get the sub parts to display across the columns instead of down the rows.
    So col A would have the part, col B would have the first sub part, c would have the second sub part etc.
    Thanks again for your help.
    Stats

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index/Match (2000)

    My solution does that.
    Legare Coleman

  9. #9
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index/Match (2000)

    Good Day Legare,
    I'm sorry I should have been more clear, attached is a partial list of exactly what MS Query outputs. I have included three main Part Numbers in col A but there are over 500. What I'm trying to accomplish is to use this as a "database" and on another workbook have the user enter the part number they want and have Excel return all of the sub parts that go into making the main part.
    I guess I should have put in the attachement in the first place.
    I appreciate your help.
    Attached Files Attached Files

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Index/Match (2000)

    This really looks like Pivot Tables would be the best tool. Unfortunately I suck at Pivot Tables <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>, but now you have posted a sample maybe someone good with them can lock and load.
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Index/Match (2000)

    Here is an attachment using the technique I mentioned on the 20th with Arrays.
    I also added the list across the columns (though this is harder to read)
    I also have a pulldown to select the partnumbers. (I added some columns to your table to suck out the unique entries and added some range names. If you add more items you will have to extend the range names (or just move the current last row to the end to automatically enlarge them)

    Hope this helps,
    Steve
    Attached Files Attached Files

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index/Match (2000)

    OK, then the VBA routine below will convert the list like you showed on Sheet1 into an new list on Sheet2 that could be used with the technique I showed in the other reply. This list will be much easier to work with.

    <pre>Public Sub BuildTable()
    Dim I As Long, J As Long, K As Long, lKMax As Long
    Dim lLastRow As Long
    Dim oSrc As Range, oDest As Range
    lLastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row - 1
    Worksheets("Sheet2").Cells.Clear
    Set oSrc = Worksheets("Sheet1").Range("A1")
    Set oDest = Worksheets("Sheet2").Range("A1")
    oSrc.EntireRow.Copy Destination:=oDest
    J = 0
    For I = 1 To lLastRow
    If oSrc.Offset(I, 0).Value <> oSrc.Offset(I - 1, 0).Value Then
    J = J + 1
    K = 1
    oDest.Offset(J, 0).Value = oSrc.Offset(I, 0).Value
    End If
    oDest.Offset(J, K).Value = oSrc.Offset(I, 1).Value
    K = K + 1
    If K > lKMax Then lKMax = K
    Next I
    oDest.Range(Columns(1), Columns(lKMax)).AutoFit
    End Sub
    </pre>

    Legare Coleman

  13. #13
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index/Match (2000)

    Here another approach.

    ( 1.) Insert an additional worksheet named Admin.
    ( 2.) Activate Admin.
    ( 3.) In A1:A4 enter:

    {"Data";"# of rows in use";"# of data recs";"# of columns in use"}

    I assume in what follows the part data to be in a worksheet named Data.

    ( 4.) In B2 enter:

    =MATCH(REPT("z",90),Data!A:A)

    ( 5.) In B3 enter:

    =B2-(CELL("Row",Data!A2)-1)

    ( 6.) In B4 enter: 2 (the hardcoded number of columns in use)
    ( 7.) Activate Insert|Name|Define.
    ( 8.) Enter PTable (from parts table) in the box for "Names in Workbook".
    ( 9.) Enter the following formula in the box for "Refers to":

    =OFFSET(Data!$A$2,0,0,Admin!$B$3,Admin!$B$4)

    This dynamic formula allows PTable to always include the changes to the data area.

    (10.) Click OK.
    (11.) Activate the worksheet (here referred to as Main) where the user can enter or select from a dropdown list a part in order to get a list of its subparts.
    (12.) In A2 enter:

    =COUNTIF(INDEX(PTable,0,1),B2)

    (13.) In B2 enter/select a part code, say, FG3656X4LTSPR.
    (14.) In D2 enter:

    =IF(A2,INDEX(PTable,MATCH($B2,INDEX(PTable,0,1),0) ,2),"")

    (15.) In E2 enter and copy across to 10 to 15 columns:

    =IF(MATCH("*",$D22,-1)<$A2,INDEX(PTable,MATCH($B2,INDEX(PTable,0,1),0) +MATCH("*",$D22,-1),2),"")

    If you would want to install the morefunc add-in which is downloadable from: http://longre.free.fr/english/index.html, you could also use in E2:

    =IF(SETV(MATCH("*",$D22,-1))<$A2,INDEX(PTable,MATCH($B2,INDEX(PTable,0,1),0 )+GETV(),2),"")

    Aladin
    Attached Files Attached Files
    Microsoft MVP - Excel

  14. #14
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Index/Match (2000)

    Seem you have a surfeit of responses and possible solutions. Appropos your last reply to mine, see if the attached does what you want.
    Cheers
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  15. #15
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index/Match (2000)

    Thank you very much to every body who took the time to help. You have given me a fair a mount of homework to see which works best. I'll try all of the ideas out with the full version of my spread sheet to see which works best.
    Thanks again for all of the valuable input.
    Stats

Page 1 of 2 12 LastLast

Posting Permissions

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