Find PartNumbers

F

flds

Board Regular
Joined
Jun 19, 2008
Messages
73
Hello friends,

I am looking to create a VBA code to do the following. Is it possible?
I have a data range with many columns (ie. Description, Vendor, Part number……).
Column ‘A’ "Description" has string of data which includes part numbers, vendor and other info. (2500+ rows)
Column ‘H’ , 'I' & 'J' are blank
Column ‘M’ has Alphanumeric part numbers.
Column ‘N’ has code numbers.
Columns ‘M’ and ‘N’ go together
I need to find the part numbers from column M by searching in Column ‘A’, (should be able to use wildcard search)
If a match is found, I need to copy the code number in column ‘N’ and place it in column ‘H’ on the same row the match is found in Column ‘A’. and also the cell address of column 'N' to be placed in column 'I'. (for verification purpose).
If no Match is found place a "NO" in Column 'J'.
I hope I have explained this correctly.

Question
How long will it take to search all the part numbers from column 'M', it need to search each cell and find a match?

Thanks
FLDS
 
Sort by date Sort by votes
Hi hiker95,

Excellent, works perfect.
Thank you so much for taking the time to help me. What would one do without MrExcel and helpful people like you.

Is it possible to explain this macro.

I have a few questions.
- What if I need to insert a column before Column 'A', Do I need to modify the macro?
- What if I have 2 or more part numbers in column 'M', which could be in column 'A' on the same row.

This is what I came across today when working with the actual file. The file is extracted from another system.

Thanks once again
FLDS
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
flds,

Thanks for the feedback.

You are very welcome. Glad I could help.

Is it possible to explain this macro.
Click to expand...

I will put some comments in the code for you - probably tomorrow.


I have a few questions.
- What if I need to insert a column before Column 'A', Do I need to modify the macro?
- What if I have 2 or more part numbers in column 'M', which could be in column 'A' on the same row.
Click to expand...

I would have to see the actual raw data workbook, with before and after worksheets. Please post another workbook on BOX.
 
Upvote 0
flds,

The macro is working correctly right now.

The macro cycles thru M3 to M18.

After filling in the correct information for 888-Test, we get this:


Excel 2007
ABCHIJMNO
1Raw Data
2DescriptionVendorEquip.CodeCell AdrsPart No.CodeY/N
3kdfjlki kjdfk d14df1245 kjdfkjkljMcMasterJ901234N14124S245A12345
4mmfd klkdlnm ;14sw264 fdflkl ;l;fdDanielD45678N74587925B23456
5PARTS SPARE 888-test PARTSDD1234N8
6jkfhjjdks kjldfflk hhk1297c12 k;ldfkksdkBrites123GF4523C34567No
7mmfd klkdl 14sw264 fdflkl ;l;fdDanielD45678N714SW264D45678
8TUBE 444-test ZIRC-2 JJ56789N15888-TestDD1234
9kjlkdfjkklf kljf fdsjkj 124S245 kljdkfjksdRandsA12345N3236A1458E56789
10kdfj 124sd957 kjlkdsfj jklfdklRollsF67890N10124SD957F67890
11RETAINING RING 444-test P/N# 888-testDD1234N8Test-444FFXXXXNo
12jkjk jjkldflk j kkdkfklj 236a1458d kldfjjRustonE56789N9648D123G78901No
13jkfhjjdks kjldfflk hhk1297c12 k;ldfkksdkBrites569F145H89012No
14jkljfkjkfd dkjlkfd jkklfkkl ;4587925 ffd;lfk 'DramelB23456N414DF1245J901234
15NUT CS SA 563 GR A 1/2"-13UNC 888-testDD1234N8444-TestJJ56789
16jkjk jjkldflk j kkdkfklj ;=236a1458d kldfjjRustonE56789N9678D145K012345No
17kdfj sjh-;124sd957 kjlkdsfj jklfdklRollsF67890N10
18DUCT COVER 888-test RPANDUIT G2X2LG6 444-testDD1234N8
19
Sheet1


Then when we get to 444-Test, the new information replaces the old information, like this:


Excel 2007
ABCHIJMNO
1Raw Data
2DescriptionVendorEquip.CodeCell AdrsPart No.CodeY/N
3kdfjlki kjdfk d14df1245 kjdfkjkljMcMasterJ901234N14124S245A12345
4mmfd klkdlnm ;14sw264 fdflkl ;l;fdDanielD45678N74587925B23456
5PARTS SPARE 888-test PARTSDD1234N8
6jkfhjjdks kjldfflk hhk1297c12 k;ldfkksdkBrites123GF4523C34567No
7mmfd klkdl 14sw264 fdflkl ;l;fdDanielD45678N714SW264D45678
8TUBE 444-test ZIRC-2 JJ56789N15888-TestDD1234
9kjlkdfjkklf kljf fdsjkj 124S245 kljdkfjksdRandsA12345N3236A1458E56789
10kdfj 124sd957 kjlkdsfj jklfdklRollsF67890N10124SD957F67890
11RETAINING RING 444-test P/N# 888-testJJ56789N15Test-444FFXXXXNo
12jkjk jjkldflk j kkdkfklj 236a1458d kldfjjRustonE56789N9648D123G78901No
13jkfhjjdks kjldfflk hhk1297c12 k;ldfkksdkBrites569F145H89012No
14jkljfkjkfd dkjlkfd jkklfkkl ;4587925 ffd;lfk 'DramelB23456N414DF1245J901234
15NUT CS SA 563 GR A 1/2"-13UNC 888-testDD1234N8444-TestJJ56789
16jkjk jjkldflk j kkdkfklj ;=236a1458d kldfjjRustonE56789N9678D145K012345No
17kdfj sjh-;124sd957 kjlkdsfj jklfdklRollsF67890N10
18DUCT COVER 888-test RPANDUIT G2X2LG6 444-testJJ56789N15
19
Sheet1


Choice #1: We could do this if the cells in column A contained two Part No.'s:


Excel 2007
ABCHIJMNO
1Raw Data
2DescriptionVendorEquip.CodeCell AdrsPart No.CodeY/N
3kdfjlki kjdfk d14df1245 kjdfkjkljMcMasterJ901234N14124S245A12345
4mmfd klkdlnm ;14sw264 fdflkl ;l;fdDanielD45678N74587925B23456
5PARTS SPARE 888-test PARTSDD1234N8
6jkfhjjdks kjldfflk hhk1297c12 k;ldfkksdkBrites123GF4523C34567No
7mmfd klkdl 14sw264 fdflkl ;l;fdDanielD45678N714SW264D45678
8TUBE 444-test ZIRC-2 JJ56789N15888-TestDD1234
9kjlkdfjkklf kljf fdsjkj 124S245 kljdkfjksdRandsA12345N3236A1458E56789
10kdfj 124sd957 kjlkdsfj jklfdklRollsF67890N10124SD957F67890
11RETAINING RING 444-test P/N# 888-testDD1234 : JJ56789N8 : N15Test-444FFXXXXNo
12jkjk jjkldflk j kkdkfklj 236a1458d kldfjjRustonE56789N9648D123G78901No
13jkfhjjdks kjldfflk hhk1297c12 k;ldfkksdkBrites569F145H89012No
14jkljfkjkfd dkjlkfd jkklfkkl ;4587925 ffd;lfk 'DramelB23456N414DF1245J901234
15NUT CS SA 563 GR A 1/2"-13UNC 888-testDD1234N8444-TestJJ56789
16jkjk jjkldflk j kkdkfklj ;=236a1458d kldfjjRustonE56789N9678D145K012345No
17kdfj sjh-;124sd957 kjlkdsfj jklfdklRollsF67890N10
18DUCT COVER 888-test RPANDUIT G2X2LG6 444-testDD1234 : JJ56789N8 : N15
19
Sheet1


Choice #2: Or, we could do this:


Excel 2007
ABCHIJMNOPQ
1Raw Data
2DescriptionVendorEquip.CodeCell AdrsPart No.CodeY/N
3kdfjlki kjdfk d14df1245 kjdfkjkljMcMasterJ901234N14124S245A12345
4mmfd klkdlnm ;14sw264 fdflkl ;l;fdDanielD45678N74587925B23456
5PARTS SPARE 888-test PARTSDD1234N8
6jkfhjjdks kjldfflk hhk1297c12 k;ldfkksdkBrites123GF4523C34567No
7mmfd klkdl 14sw264 fdflkl ;l;fdDanielD45678N714SW264D45678
8TUBE 444-test ZIRC-2 JJ56789N15888-TestDD1234
9kjlkdfjkklf kljf fdsjkj 124S245 kljdkfjksdRandsA12345N3236A1458E56789
10kdfj 124sd957 kjlkdsfj jklfdklRollsF67890N10124SD957F67890
11RETAINING RING 444-test P/N# 888-testDD1234N8Test-444FFXXXXNoJJ56789N15
12jkjk jjkldflk j kkdkfklj 236a1458d kldfjjRustonE56789N9648D123G78901No
13jkfhjjdks kjldfflk hhk1297c12 k;ldfkksdkBrites569F145H89012No
14jkljfkjkfd dkjlkfd jkklfkkl ;4587925 ffd;lfk 'DramelB23456N414DF1245J901234
15NUT CS SA 563 GR A 1/2"-13UNC 888-testDD1234N8444-TestJJ56789
16jkjk jjkldflk j kkdkfklj ;=236a1458d kldfjjRustonE56789N9678D145K012345No
17kdfj sjh-;124sd957 kjlkdsfj jklfdklRollsF67890N10
18DUCT COVER 888-test RPANDUIT G2X2LG6 444-testDD1234N8JJ56789N15
19
Sheet1



What is your pleasure?
Choice #1
or
Choice #2
 
Upvote 0
flds,

What would be the max number of part numbers that would be in a cell in column A?
 
Upvote 0
Hi hiker95,

Wonderful, I would prefer Choice # 2.

I am not sure the Max number of Part No. in a Cell in column A (would expect 2)

Thank you so much.
FLDS
 
Upvote 0
Hi hiker95,

See Post #16
"I am not sure the Max number of Part No. in a Cell in column A (would expect 2)." Is it possible to have more, If need be I could "comment Block" the line.

Thank you so much.
FLDS
 
Upvote 0
Hi Hiker95,
I am anxiously waiting for the code for Choice #2.
What are the chances? Are you able to create this code?
I am sure you must be busy helping others.
Hopefully waiting to hear from you.
FLDS
 
Upvote 0
flds,

There were some problems when there were duplicate Part No.'s in column M.

Sample raw data:


Excel 2007
AHIMNOPQR
1Raw Data
2DescriptionCodeCell AdrsPart No.CodeY/N
3kdfjlki kjdfk d14df1245 kjdfkjklj124S245A12345
4mmfd klkdlnm ;14sw264 fdflkl ;l;fd4587925B23456
5PARTS SPARE 888-test PARTS
6jkfhjjdks kjldfflk hhk1297c12 k;ldfkksdk123GF4523C34567
7mmfd klkdl 14sw264 fdflkl ;l;fd14SW264D45678
8TUBE 444-test ZIRC-2 888-TestDD1234
9kjlkdfjkklf kljf fdsjkj 124S245 kljdkfjksd236A1458E56789
10kdfj 124sd957 kjlkdsfj jklfdkl124SD957F67890
11RETAINING RING 444-test P/N# 888-test 444-TestFFXXXX
12jkjk jjkldflk j kkdkfklj 236a1458d kldfjj648D123G78901
13jkfhjjdks kjldfflk hhk1297c12 k;ldfkksdk569F145H89012
143 Part Numbers 444-test P/N# 888-test 333-Test
15jkljfkjkfd dkjlkfd jkklfkkl ;4587925 ffd;lfk '14DF1245J901234
16NUT CS SA 563 GR A 1/2"-13UNC 888-test444-TestJJ56789
17jkjk jjkldflk j kkdkfklj ;=236a1458d kldfjj678D145K012345
18kdfj sjh-;124sd957 kjlkdsfj jklfdkl
19DUCT COVER 888-test RPANDUIT G2X2LG6 444-test333-TestZ333333
20
Sheet1


At the beginning of the macro the code will remove all duplicats except the first, like this:


Excel 2007
AHIMNOPQR
1Raw Data
2DescriptionCodeCell AdrsPart No.CodeY/N
3kdfjlki kjdfk d14df1245 kjdfkjklj124S245A12345
4mmfd klkdlnm ;14sw264 fdflkl ;l;fd4587925B23456
5PARTS SPARE 888-test PARTS
6jkfhjjdks kjldfflk hhk1297c12 k;ldfkksdk123GF4523C34567
7mmfd klkdl 14sw264 fdflkl ;l;fd14SW264D45678
8TUBE 444-test ZIRC-2 888-TestDD1234
9kjlkdfjkklf kljf fdsjkj 124S245 kljdkfjksd236A1458E56789
10kdfj 124sd957 kjlkdsfj jklfdkl124SD957F67890
11RETAINING RING 444-test P/N# 888-test 444-TestFFXXXX
12jkjk jjkldflk j kkdkfklj 236a1458d kldfjj648D123G78901
13jkfhjjdks kjldfflk hhk1297c12 k;ldfkksdk569F145H89012
143 Part Numbers 444-test P/N# 888-test 333-Test
15jkljfkjkfd dkjlkfd jkklfkkl ;4587925 ffd;lfk '14DF1245J901234
16NUT CS SA 563 GR A 1/2"-13UNC 888-test
17jkjk jjkldflk j kkdkfklj ;=236a1458d kldfjj678D145K012345
18kdfj sjh-;124sd957 kjlkdsfj jklfdkl
19DUCT COVER 888-test RPANDUIT G2X2LG6 444-test333-TestZ333333
20
Sheet1


After the macro has finished we get this:


Excel 2007
AHIMNOPQR
1Raw Data
2DescriptionCodeCell AdrsPart No.CodeY/N
3kdfjlki kjdfk d14df1245 kjdfkjkljJ901234N15124S245A12345
4mmfd klkdlnm ;14sw264 fdflkl ;l;fdD45678N74587925B23456
5PARTS SPARE 888-test PARTSDD1234N8
6jkfhjjdks kjldfflk hhk1297c12 k;ldfkksdk123GF4523C34567No
7mmfd klkdl 14sw264 fdflkl ;l;fdD45678N714SW264D45678
8TUBE 444-test ZIRC-2 FFXXXXN11888-TestDD1234
9kjlkdfjkklf kljf fdsjkj 124S245 kljdkfjksdA12345N3236A1458E56789
10kdfj 124sd957 kjlkdsfj jklfdklF67890N10124SD957F67890
11RETAINING RING 444-test P/N# 888-test DD1234N8444-TestFFXXXXFFXXXXN11
12jkjk jjkldflk j kkdkfklj 236a1458d kldfjjE56789N9648D123G78901No
13jkfhjjdks kjldfflk hhk1297c12 k;ldfkksdk569F145H89012No
143 Part Numbers 444-test P/N# 888-test 333-TestDD1234N8FFXXXXN11Part No's > 2
15jkljfkjkfd dkjlkfd jkklfkkl ;4587925 ffd;lfk 'B23456N414DF1245J901234
16NUT CS SA 563 GR A 1/2"-13UNC 888-testDD1234N8
17jkjk jjkldflk j kkdkfklj ;=236a1458d kldfjjE56789N9678D145K012345No
18kdfj sjh-;124sd957 kjlkdsfj jklfdklF67890N10
19DUCT COVER 888-test RPANDUIT G2X2LG6 444-testDD1234N8333-TestZ333333FFXXXXN11
20
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code: 
Option Explicit
Sub FindCodeCellAdrsV3()
' hiker95, 12/19/2013
' http://www.mrexcel.com/forum/excel-questions/745111-find-partnumbers.html
Dim a As Variant, i As Long, ii As Long, n As Long, lr As Long, c As Range, rng As Range
lr = Cells(Rows.Count, 1).End(xlUp).Row
With Range("H3:I" & lr)
  .ClearContents
  .HorizontalAlignment = xlCenter
End With
With Range("O3:R" & lr)
  .ClearContents
  .HorizontalAlignment = xlCenter
End With
Set rng = Range("M3:M" & lr)
For Each c In rng
  If c <> "" Then
    If WorksheetFunction.CountIf(Range(rng.Cells(1, 1), rng.Cells(c.Row, 1)), c) > 1 Then
      c.Resize(, 2) = ""
    End If
  End If
Next c
a = Range("A1:R" & lr)
For i = 3 To UBound(a, 1)
  If a(i, 13) <> "" Then
    n = 0
    For ii = 3 To UBound(a, 1)
      If InStr(LCase(a(ii, 1)), LCase(a(i, 13))) Then
        n = n + 1
        If a(i, 8) = "" Then
          a(ii, 8) = a(i, 14)
          a(ii, 9) = "N" & i
        ElseIf a(i, 8) <> "" And a(ii, 16) = "" Then
          a(ii, 16) = a(i, 14)
          a(ii, 17) = "N" & i
        ElseIf a(i, 8) <> "" And a(ii, 16) <> "" And a(ii, 18) = "" Then
          a(ii, 18) = "Part No's > 2"
        End If
      End If
    Next ii
    If n = 0 Then a(i, 15) = "No"
  End If
Next i
For i = 3 To UBound(a, 1)
  If a(i, 8) = "" And a(i, 16) <> "" Then
    a(i, 8) = a(i, 16)
    a(i, 9) = a(i, 17)
    a(i, 16) = ""
    a(i, 17) = ""
  End If
Next i
Range("A1").Resize(UBound(a, 1), UBound(a, 2)) = a
Columns("A:R").AutoFit
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindCodeCellAdrsV3 macro.
 
Last edited:
Upvote 0
Hi Hiker95,
Thank you so much, I have been pushing you, sorry about that.
I am in the office working on the file, “Part No's > 2” in column ‘R’ is great, fantastic, I can now filter this.
I will be using both your macro’s.
I have one more request. The first macro that worked (post #10).
Is it possible to search part numbers without wildcard search. Search Absolute Part No (is this the right word I used)
Do we have to change this line on the macro.
“lr = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row”
If this is not too much of work, I would appreciate this.
Thanks a million
FLDS
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Copy and paste multiple matching rows to a new sheet
Replies
7
Views
129
SamKhem
S
H
  • Solved
Amend the VBA code to Filter Columns Instead of Rows
Replies
9
Views
188
Akuini
Akuini
S
  • Solved
Lookup formula
Replies
4
Views
194
Joe4
Joe4
A
  • Question
Dynamically add new rows via VBA when using IFERROR TEXTJOIN and FILTER formulas from Table?
Replies
0
Views
129
AlfredDB
A
J
  • Question
Return invoice value if invoice number appears in a list
Replies
4
Views
136
etaf
etaf

Forum statistics

Threads
1,215,388
Messages
6,124,652
Members
449,177
Latest member
Sousanna Aristiadou

Share this page

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