Find..Match..Count???

  • Hi all,


    I've got one that sounds simple enough but has me stumped.


    I'm after a Function solution in preference to VBA if possible.


    The Problem!!


    I have a data matrix that contains part numbers applicable to equipment models, ie.

    Part > Wheel Rim Nut Bolt
    ModelA W12 A26 N23 D47
    ModelB A13 R33 P22 B67
    ModelC Z16 A26 Q27 K57
    ModelD W22 R14 N23 B67
    ModelE A13 R26 P22 D47

    On another worksheet I have a list of part numbers and descriptions.

    Part Description
    A13 Wheel
    A26 Rim
    B67 Bolt
    D47 Bolt
    K57 Bolt
    N23 Nut
    P22 Nut
    Q27 Nut
    R14 Rim
    R26 Rim
    R33 Rim
    W12 Wheel
    W22 Wheel
    Z16 Wheel

    1st. I'd like to count how many times each part number is found in the parts matrix, ie. N23 is found twice

    Part Description Qty in Matrix
    A13 Wheel
    A26 Rim
    B67 Bolt
    D47 Bolt
    K57 Bolt
    N23 Nut
    P22 Nut
    Q27 Nut
    R14 Rim
    R26 Rim
    R33 Rim
    W12 Wheel
    W22 Wheel
    Z16 Wheel


    2nd. I'd like to know the models that the part number suits.

    Part Description Model 1 Model 2 Model 3
    A13 Wheel
    A26 Rim
    B67 Bolt
    D47 Bolt
    K57 Bolt
    N23 Nut
    P22 Nut
    Q27 Nut
    R14 Rim
    R26 Rim
    R33 Rim
    W12 Wheel
    W22 Wheel
    Z16 Wheel


    As I said earlier if possible using Worksheet Functions but VBA if not.


    Thanks in advance.


    AJW

  • Assumptions:


    Sheet 1
    --------


    Contains your data matrix, with headers in the first row, and data starting in the second row


    Sheet 2
    --------


    Contains your headers in the first row (Parts and Description) and your data starting in the second row


    For the Quantity in Matrix...


    C2, copied down:


    =COUNTIF(Sheet1!$B$2:$E$6,Sheet2!A2)


    For the Model...


    D2, copied down:


    =INDEX(Sheet1!$A$2:$A$6,MATCH(Sheet2!A2,INDEX(Sheet1!$B$2:$E$6,0,MATCH(Sheet2!B2,Sheet1!$B$1:$E$1,0)),0))


    Hope this helps!

Participate now!

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