Excel - Align matching cells of two different columns

Asked By JohnDessell on 22-Oct-08 12:14 PM
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




JohnDessell replied on 22-Oct-08 02:42 PM
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.
JohnDessell replied on 22-Oct-08 03:57 PM
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).Column
'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