Comparing then Replacing
 

Hi Everyone,

I have an excel file, with 2 sheets. I need to take the original, and
row by row compare cols a through n, if those all match exactly, then
copy what is in col o from the original to the new sheet col o, on the
line that matches.

There are lines inserted in the new sheet that are not in the original,
and need to stay untouched, therefore the order of the two sheets is
not the same.

These files are 2400+ rows long.

If anyone could lend a hand, that would be great!

Thanks!

Jeff Basham

Comparing then Replacing
 

copy the workbook you're using to a test workbook and see what happens.

select all of your data and give it the name RES_CODE_LOOKUP.

put the name of your test file where i have my file name, and change the
sheet3 reference to whatever sheet you want column O posted to.

you can see what happens and post back.

--


Gary


"Jeff Basham" wrote in message
oups.com...
Hi Gary,
Thanks for the reply. I can't figure out how to adapt it .. I am
totally a novice at this.

Thanks

Comparing then Replacing
 

i did something like that yesterday for myself. here's the code i adapted
from someone here, maybe bob p. see if you can adapt it


Sub LookupNames()
Dim lngLastRow As Long
Dim x As Long, i As Long
Dim sResCode As Variant
Dim rngRescodeLookup As Range
Application.ScreenUpdating = False
Set rngRescodeLookup _
= Workbooks("1113plu.xls") _
.Names("RES_CODE_LOOKUP").RefersToRange
'determine last row
lngLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .row

With ActiveSheet
lngLastRow = .Cells(.Rows.Count, 6).End(xlUp).row
End With

i = 4
'loop through each row and lookup name in lookup table
For x = 1 To lngLastRow
sResCode _
= Application.VLookup(Cells(x, 1).Value, rngRescodeLookup, 1,
False)
If IsError(sResCode) Then

sResCode = ""
Else
Worksheets("sheet3").Cells(x, 15).Value = Cells(x, 15).Value
i = i + 1
End If
Next x
Application.ScreenUpdating = True
End Sub
--


Gary


wrote in message
oups.com...
Hi Everyone,

I have an excel file, with 2 sheets. I need to take the original, and
row by row compare cols a through n, if those all match exactly, then
copy what is in col o from the original to the new sheet col o, on the
line that matches.

There are lines inserted in the new sheet that are not in the original,
and need to stay untouched, therefore the order of the two sheets is
not the same.

These files are 2400+ rows long.

If anyone could lend a hand, that would be great!

Thanks!

Jeff Basham

Comparing then Replacing
 

OK, I found a program that will identify the rows from the original and
tell me which row in the new file matches which row in the old file.
The program is Excel Compare. Anyone know how to make it then take
those two and copy col o from the original to col o on the new file?

Comparing then Replacing
 

Hi Gary,
Thanks for the reply. I can't figure out how to adapt it .. I am
totally a novice at this.

Thanks

Comparing then Replacing
 

I get a runtine error 1004 application-defined or object-defined error.

Thanks.

Comparing then Replacing
 

When I debug It highlights this line:

For x = 1 To lngLastRowsResCode = Application.VLookup(Cells(x,
1).Value, rngRescodeLookup, 1, False)

Where should the line breaks be?

Comparing then Replacing
 

OK .. I got the Syntax error corrected, but it just copies column o to
the new column o .. it changes column o on the rows that are not in the
original .. that need to stay the same, untouched.

Comparing then Replacing
 

You really want to compare column A to column A, B to B, ..., N to N?

If yes, I created two worksheets (with headers in Row 1).

I used column A to find the last used row of each sheet.

Then this seemed to work ok (but save before trying!):

Option Explicit
Sub testme()

Dim OrigWks As Worksheet
Dim NewWks As Worksheet
Dim wks As Worksheet
Dim myCell As Range
Dim myStr As String
Dim iCtr As Long
Dim wCtr As Long
Dim LastRow As Long
Dim Res As Variant

Set OrigWks = Worksheets("orig")
Set NewWks = Worksheets("New")

For Each wks In Worksheets(Array(OrigWks.Name, NewWks.Name))
With wks
.Range("P:Q").ClearContents
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For Each myCell In .Range("Q2:Q" & LastRow).Cells
myStr = ""
For iCtr = 1 To .Range("N1").Column
myStr = myStr & Chr(1) & .Cells(myCell.Row, iCtr).Value
Next iCtr
myCell.Value = myStr
Next myCell
End With
Next wks

With NewWks
.Range("O1").Resize(1, 3).Value _
= Array("Value", "Old Value/Msg", "workarea")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For Each myCell In .Range("Q2:Q" & LastRow).Cells
Res = Application.Match(myCell.Value, _
OrigWks.Range("Q1").EntireColumn, 0)
If IsError(Res) Then
myCell.Offset(0, -1).Value = "No Change"
Else
myCell.Offset(0, -1).Value = myCell.Offset(0, -2).Value
myCell.Offset(0, -2).Value _
= OrigWks.Range("q1")(Res).Offset(0, -2)
End If
Next myCell
End With

End Sub

I concatenated Ax:Ox into column Q--but with chr(1) between each field. Then I
used that to match.

I also used column P for either the old value (if there was a match) or a
message ("no Change") if there wasn't a match.

You can delete columns O:P if you don't want them (on both sheets).


wrote:

Hi Everyone,

I have an excel file, with 2 sheets. I need to take the original, and
row by row compare cols a through n, if those all match exactly, then
copy what is in col o from the original to the new sheet col o, on the
line that matches.

There are lines inserted in the new sheet that are not in the original,
and need to stay untouched, therefore the order of the two sheets is
not the same.

These files are 2400+ rows long.

If anyone could lend a hand, that would be great!

Thanks!

Jeff Basham


--

Dave Peterson

Comparing then Replacing
 

Thanks, Dave.

I got here this AM to work on it, and my boss had decided to stay late
and compare it line by line manually.

I really appreciate the help.

Comparing then Replacing
 

Line by line manually????

If I were you, I'd try the macro to see what was missed (or done in error).

If I know the program works, I know I'd trust it more than a manual effort.
Yeah, that's a big IF!

Jeff Basham wrote:

Thanks, Dave.

I got here this AM to work on it, and my boss had decided to stay late
and compare it line by line manually.

I really appreciate the help.

--

Dave Peterson