I have two columns that contain computer names. One Column C is 330 rows and
Column D is 452. What I would like to happen is to align the names in C with
their matches in D. I'm guessing I need the HLOOKUP but am not sure how to
use it.
I've been able to figure out a formula to tell me if the two cells match or
not: =IF(C2=D2,"GOOD","BAD")

Here is a sample of my data:

Matches MachineName Device
GOOD 10VHQB1 10vhqb1
BAD 12WFB21 12yt2d1
BAD 12YT2D1 14yh051
GOOD 15YT2D1 15yt2d1
GOOD 180W2D1 180w2d1

Maybe a better example of data would be:
Matches MachineName Device
GOOD 10VHQB1 10vhqb1
BAD 12WFB21 12yt2d1
BAD 12YT2D1 14yh051


I want the Device column to scoot down one row so 12YT2D1 ends up on the
same row.

"John Desselle" wrote:

I have two columns that contain computer names. One Column C is 330 rows and
Column D is 452. What I would like to happen is to align the names in C with
their matches in D. I'm guessing I need the HLOOKUP but am not sure how to
use it.
I've been able to figure out a formula to tell me if the two cells match or
not: =IF(C2=D2,"GOOD","BAD")

Here is a sample of my data:

Matches MachineName Device
GOOD 10VHQB1 10vhqb1
BAD 12WFB21 12yt2d1
BAD 12YT2D1 14yh051
GOOD 15YT2D1 15yt2d1
GOOD 180W2D1 180w2d1

I answered my own question. Guess I should have changed the question for
here.....I needed to copy/paste data based off a matching cell. Here is the
code:


I have an Excel Workbook with two work sheets. It is a bunch of information
about some of our computers. The first sheet has about 348 rows and the
second one has about 480 rows. Each sheet has a little bit different
information. One may have the user name and the other has the phone number.

I wanted a way to make Excel (since I'm so lazy) compare the first column of
each sheet. This column has the unique computer name. I wanted it to look
at these two columns and when it finds a match copy/paste that row next to
it's match on the other sheet.



Sub FindRowAndCopy()
' Use the first column of the CurrentRegion for lookup values
' Find matches on another sheet and copy the row back
' to the matching row on the active sheet
Dim ActiveSht As Worksheet
Dim FindMatchOnSht As Worksheet
Dim myCell As Range
Dim PasteCol As Integer
Dim LastCol As Integer
Dim r As Long

Application.ScreenUpdating = False

'use Sheet2 for finding matches
Set FindMatchOnSht = ActiveWorkbook.Worksheets("Sheet2")

With ActiveSheet
'Use the first column of the range with the cellpointer for lookup values
'Change the columns(1) value to use a different column
Set rng = Intersect(ActiveCell.CurrentRegion, .Columns(1))
'Find the last column with data and add 1 (one way to do it)
'assumes column 256 is blank
PasteCol = Cells(ActiveCell.Row, 256).End(xlToLeft).Column + 1
End With

For Each cell In rng
'search the first column on the sheet for the first match
'change the Columns(1) to search a different column
Set myCell = FindMatchOnSht.Columns(1).Find(What:=cell.Value, _
LookIn:=xlValues, _
LookAt:=xlWhole)

If Not myCell Is Nothing Then
'If a match is found
r = myCell.Row
LastCol = FindMatchOnSht.Cells.SpecialCells(xlLastCell).Colu mn
'Copy and paste the row
FindMatchOnSht.Range(FindMatchOnSht.Cells(r, 1), _
FindMatchOnSht.Cells(r, LastCol)).Copy _
ActiveSheet.Cells(cell.Row, PasteCol)
End If
Next cell
Application.ScreenUpdating = True

End Sub


"John Desselle" wrote:

Maybe a better example of data would be:
Matches MachineName Device
GOOD 10VHQB1 10vhqb1
BAD 12WFB21 12yt2d1
BAD 12YT2D1 14yh051


I want the Device column to scoot down one row so 12YT2D1 ends up on the
same row.

"John Desselle" wrote:

I have two columns that contain computer names. One Column C is 330 rows and
Column D is 452. What I would like to happen is to align the names in C with
their matches in D. I'm guessing I need the HLOOKUP but am not sure how to
use it.
I've been able to figure out a formula to tell me if the two cells match or
not: =IF(C2=D2,"GOOD","BAD")

Here is a sample of my data:

Matches MachineName Device
GOOD 10VHQB1 10vhqb1
BAD 12WFB21 12yt2d1
BAD 12YT2D1 14yh051
GOOD 15YT2D1 15yt2d1
GOOD 180W2D1 180w2d1