# Look up and Auto Populate

September 14, 2010 at 07:54:41
Specs: Windows XP
 ExcelColumn A contains Equip Nos.Column B Equip Descriptions (part of the Description may contain the Parent Equip No)I want to search Column B to see if ANY of the Equip listed anywhere Column A is contained in the Description.e.g.Column A --- Column B RE503 --- REACTOR ON CE501CE501 --- CENTREFUGEPU302 --- PUMP ON RE503Results I would like to see.Column C to reflect CE501 for line1.Column C Blank for line 2.Column C would show RE503 for line 3.

See More: Look up and Auto Populate

#1
September 14, 2010 at 08:12:56
 Thanks for letting us know what you want.If you have any questions, feel free to ask.

Report •

#2
September 14, 2010 at 15:06:14
 Hi DerbyDad03, do you have any suggestions how I could solve this problem?Any help greatly appreciated, its part of some data scrubbing so I can migrate data from maximo-4 to maximo-6.Edysku.

Report •

#3
September 14, 2010 at 19:10:21
 Try this code.I'm assuming the length of Column A and B is the same.```Sub DataScrub() Dim lastRow, partNum As Integer Dim c As Range 'Find last Row with data lastRow = Range("A" & Rows.Count).End(xlUp).Row 'Loop through Column A For partNum = 1 To lastRow 'Look for each part in Column B With Worksheets(1).Range("B1:B" & lastRow) Set c = .Find(Worksheets(1).Range("A" & partNum), _ LookIn:=xlValues, lookat:=xlPart) 'If part is found, place it in Column C and keep looking 'until all instances of that part is found If Not c Is Nothing Then firstAddress = c.Address Do Worksheets(1).Range("C" & c.Row) = _ Worksheets(1).Range("A" & partNum) Set c = .FindNext(c) Loop While Not c Is Nothing And _ c.Address <> firstAddress End If End With Next End Sub ```

Report •

Related Solutions

#4
September 15, 2010 at 04:25:48
 DerbyDad03 - excellent - worked 1st time, thanks for the help.Edysku.

Report •

#5
September 15, 2010 at 05:07:47
 Glad I could help.BTW the point of my first response was that we are all volunteers here, helping others because it's fun and challenging.I assume that you wouldn't walk into a co-worker's office and simply blurt out what you want, expecting them to give it to you. I assume you would ask them for help.It should be no different in an on-line forum. A "please and thank you" - upfront - goes a long away towards having people want to help you.

Report •