Issue with finding results, not sure what formula to use

Tucker92

Board Regular
Joined
Jun 7, 2018
Messages
53
Hi Everyone

I am currently trying to use a data base to find out which parts go onto which machines

I currently have a list of all the parts that i am using and next to each part i have each machine number that uses that part.

Description
34562Brown Link wire53456453664534645346546773456324

<tbody>
</tbody>

What i need it to do is if a number on another sheet matches one of the numbers then for it to return the description of the part.
But there can be more than one part per machine

Thank you
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
34562Brown Link wire123456
12345red wire78591011
98765blue wire121325914
####
Brown Link wireBrown Link wire
red wirered wire
blue wireblue wireblue wire
#####
the cell with brown link has the formula
=IF(COUNTIF(myrange,D1)>1,$B1,"")
myrange is C1 to H3

<colgroup><col><col><col span="8"></colgroup><tbody>
</tbody>
 
Upvote 0
Its works but not as i wanted it to.

My database has over 1000 parts in it and each machine number is typed into a cell to to the right of it and there are hundreds of different machines

What i was hoping is that it would find all the parts that match up to the specific machine number. only to show the parts that have my machine number in its row in on of the columns

I may have just used your formula wrong
 
Upvote 0
so do you have in one row all parts that match up to that machine - some will be blank presumably if the machine number is over to the right

last but one paragraph is unclear - surely you already know the parts that match to a specific machine ?
 
Upvote 0
partsENTER MACHINE NUMBERM7
P1M1M7M12M15M21P1#####
P2M2M3M6M9M10
P3M4M5M7M8M20P3
P4M6M11M13M15M21
P5M1M7M12M15M21P5
P6M2M3M6M9M10
P7M4M5M17M8M20
P8M6M11M13M15M21
P9M1M7M12M15M21P9
P10M2M3M6M9M10
P11M4M5M7M8M20P11
#####
=IF(ISERROR(MATCH($K$1,B2:F2,0)),"",A2)

<colgroup><col span="15"></colgroup><tbody>
</tbody>
 
Upvote 0
At the moment my database looks like this


Part NumberPart NameMachine number 1 Machine number 2 ETC
12345 Wire 7899564 35875734
12321Hose7899564 4657564

<tbody>
</tbody>




The i have another sheet which copys in a list of machines for that week and i need it to find the parts that match with the machine numbers. This is roughly what it will look like


Machine NumberMachine NameParts
4254562George12345
12321
3567846Steve12321
34562

<tbody>
</tbody>
 
Last edited:
Upvote 0
Where the parts are it needs to find each instance that the machine number appears throughout my database and fill in the rows
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top