Index/Match (2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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(SubParts,SMALL(IF($B$1=ParentParts,ROW(ParentParts),””),A1)))}
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

Reply | Quote

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.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

My solution does that.

Reply | Quote

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.

Reply | Quote

This really looks like Pivot Tables would be the best tool. Unfortunately I suck at Pivot Tables , but now you have posted a sample maybe someone good with them can lock and load.

Reply | Quote

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

Reply | Quote

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.

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

Reply | Quote

Good Afternoon Legare,
After trying all of the suggestions, I have found, for my needs, your example works best (as if there was a doubt). The other examples ran into a problem with duplicate subparts and your solution worked perfectly.
Thank you very much for your help (AGAIN!!).
Stats

Reply | Quote

Are you sure it’s the only one?

Reply | Quote

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(“*”,$D2:D2,-1)<$A2,INDEX(PTable,MATCH($B2,INDEX(PTable,0,1),0)+MATCH("*",$D2:D2,-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("*",$D2:D2,-1))<$A2,INDEX(PTable,MATCH($B2,INDEX(PTable,0,1),0)+GETV(),2),"")

Aladin

Reply | Quote

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

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote