# 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.

AJW

## Files

• 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!