match up formula

Maybe this in C2 copied down: =INDEX($A$2:$A$308,MATCH(D2,$B$2:$B$308,0))

Dom

It must be psychic Monday

Dom

Can you post an example of what you want this to finish up looking like, not sure I follow.

Dom

How about this?

It loops through the entries in column D and where a match is found in column B puts the value in column C and deletes the entry from D. You would therefore only be left in D with items that could not be found.

Sub test()

Dim rngSearchRange As Range
Dim rngFindRange As Range
Dim lngLoopRow As Long

Set rngSearchRange = Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)

For lngLoopRow = Cells(Rows.Count, 4).End(xlUp).Row To 2 Step -1

    With Range("D" & lngLoopRow)

        Set rngFindRange = rngSearchRange.Find(.Value, LookIn:=xlValues, lookat:=xlWhole)
        
        If Not rngFindRange Is Nothing Then
        
            rngFindRange.Offset(0, 1) = .Value
            .Delete xlShiftUp
            
        End If

    End With
    
Next lngLoopRow

End Sub

The code needs to be placed in a regular code module and will work on the active worksheet.

Dom

Seems to work for me if I've understood you. See attached example.

Dom

Never heard of Lynda.com until you mentioned it. The problem with most courses I've come across is that they tend to focus on using Excel functions without really explaining how the application itself really works. I guess they are okay but you'd do far worse than getting a decent Excel book and asking questions on places like this. You'll learn far more than a general course will give you if you're prepared to put in some work yourself.

For the macro button you just draw it off the Forms toolbar and Excel will ask you if you want to attach a macro to it.

Dom

Hi Dom,

Over a year ago you were a great help and made up a formula to me that has been a fantastic help for me and my work (here is the tread for that http://www.excelforum.com/excel-gene...-new-post.html ) however i have now come to a situation where i need to extend that formula further, where before i needed to match up the data from one column with another i now need the same formula but i also need the 3 following columns to follow as well, im enclosing a sheet with the initial formula you done for me and what else i would like, i hope im not being chicky and asking for help again and i really hope you can help me again.

The idea is to match column D with the data from column B and move matching results to column C however this updated formula needs also to move all the data from column E F G H I J along with column D but its the D data that needs to match the data from column B, columns E F G H I J need to follow the Data from D. i hope you understand what i mean.

many thanks once again.

Monteiro1969,

Don't post a question in an older thread -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.