Look Ups

[DGB]

I have a spreadsheet with 3 columns. The first column has a list of numbers, the second column has a list of numbers, and the third column has a code, one of which is “IP”

I want to look up IP, when I find it I need to get the number in the column to it’s left, on the same row. Then I need to look up that number in the first column (which will be on a separate row). I then need to change the word in the third column of that row to “Kit”.

Example:

Col. A has numbers
Col. B has numbers, some of which are the same as in Col. A, but not in the same row
Col. C has a code, one of which is ‘IP’

I want to look for ‘IP’ in column C. When I find it, I want to get the number corresponding number from Col. B (same row), then look up that number in Col. A. (remember, Col. A and Col. B have some of the same numbers, they are just not in the same row).

When I find that number in Col. A, I want to change the code that is in Col. C, from that same row, to read “Kit”

Example: I find “IP” in Col. C. The coinciding number in Col. B is “4630.0”. I find that number in Col. A (one row down) and need to change the “CP” in Col. C to “Kit”.

Col. A Col. B Col. C
12345.0 46730.0 IP
46730.0 CP – (this needs to be changed to “Kit”)



Hopefully this makes sence. The list I have is 10,000 plus rows long, and the rows where the part numbers match are usually not close to each other. One is closer to the bottom of the list, while the other is closer to the top of the list.

Thanks

 

[Special-K99]

How do you go about "finding" IP? Are you entering one code at a time?
Are there any duplicate values in column A or B?
Are either column A or B sorted?

 

[DGB]

Hi Special KK

Thanks for looking at this. In answer to your questions:

Will you ever be searching for something other than “IP”? Answer: No
How do you go about "finding" IP? Answer: I was using the Search box, but would like to use a formula
Are there any duplicate values in column A or B? Answer: No
Are either column A or B sorted? Answer: Col B is sorted ASC

Thanks again for your help.

 

[ShaunD30]

I'll give this one a try..keep in mind I am a VBA noob, and my code is certainly not as elegant as others..but I think this may work...

Sub FindIP()
Finalrow = Cells(Rows.Count, 1).End(xlUp).Row
Range("C1").Select
Do Until ActiveCell.Row = Finalrow
    ActiveCell.Offset(1, 0).Select
        If ActiveCell.Value = "IP" Then
            LastAddress = ActiveCell.Address
            ActiveCell.Offset(0, -1).Select
    
            CurrentNum = ActiveCell.Value
    
        Range("A1").Select
   
For i = 1 To Finalrow
    If Cells(i, 1).Value = CurrentNum Then
        Cells(i, 3).Value = "Kit"
    
    End If
    Next i
    
    Range(LastAddress).Select
    
    End If
    
    Loop
Range("A1").Select
End Sub

Hope this helps. Just save your work before you try

Shaun

 

[DGB]

Hi Shaun,

I created a test sheet witht e following bits of data:

ColA ColB ColC
12345 46730 IP
46730 12345 CP

And pasted your code into a macro. When i run it, nothing other than the cursor moving to Cell A1 happened.

Did I do something whong?

 

[ShaunD30]

The code i wrote assumed that there were column headers. Create column headers for those 3 columns and then try.

Shaun

 

[DGB]

that did it thank you.

How would the code change if there were no headers?

 

[ShaunD30]

DGB,

Honestly I am not sure. I tried to get it to work with no headers but got frustrated because my code would always skip over row 1. I know it was skipping because of the offset function but I tried to put the offset function in other places within the code and nothing seemed to work the way you needed it to..so I just created the code based on the assumption that there were headers.

I am sure someone else can tweak my code to have it look at row 1 also.

Shaun

 

[DGB]

well, given that my columns have headers, thee are no worries.

Thanks again for taking the time and sharing your knowledge.

 

[ShaunD30]

DGB- I tried a few more times and this code works if there are no headers

Sub FindIP()
Finalrow = Cells(Rows.Count, 1).End(xlUp).Row
Range("C1").Select
Do Until ActiveCell.Row = Finalrow
    
        If ActiveCell.Value = "IP" Then
            LastAddress = ActiveCell.Address
            ActiveCell.Offset(0, -1).Select
    
            CurrentNum = ActiveCell.Value
    
        Range("A1").Select
   
For i = 1 To Finalrow
    If Cells(i, 1).Value = CurrentNum Then
        Cells(i, 3).Value = "Kit"
    
    End If
    Next i
    
    Range(LastAddress).Select
    ActiveCell.Offset(1, 0).Select
    Else
    ActiveCell.Offset(1, 0).Select
    End If
   
       
    Loop
Range("A1").Select
End Sub

Again..not the best looking code in the world..or the most efficient..but it works

This is usually about the time an MVP speaks up and says "Why don't you just use this simple, easy to use one line formula =(insert basic formula)." haha

:D