if cell value matches then copy another cell

Hey there,

I was able to get some help before with a problem that consisted of checking two excel files for cell matches and placing either a "Exists" or "Does Not Exist" in a cell depending on if the cell value in one excel file exists in another. This was done to help sort out what products existed in the one file but not the other.

That has been working great!!!

Now I have a new problem and I have tried to do it myself but not being to familiar with excel macros I am having a hard time.

I still want the macro below to do what it does, but is there a way to put the standard "Exists" and "Does not exist" as well as copy cell C from the new to the old as well?

I have two sheets that have mostly the same data, though not in the same order. The old sheet has descriptions that have been cut off, the new has the full descriptions. I need to compare cell A of new to cell A of old and if they match it writes "Exists" and then copies cell C from the new to cell C from the old. Get it?

I just want to make sure the full descriptions are copied over the incomplete ones. Would be easy if all the data was in same order, but it isn't. When I tried to do it it just copied straight down the list... which doesn't match.

Here is the macro that was first given to me to figure out which items in the new existed in the old, and vice versa.

Sub CheckExistenceCopy()
Dim NewRange As Range
Set NewRange = Range("'walloffame-u864xprt.xls'!A:A")

Dim OldRange As Range
Set OldRange = Range("'toystore-112105.XLS'!A:A")

Dim NrIndex As Long
Dim OrIndex As Long

Dim SearchedFor As Range

For NrIndex = 1 To NewRange.Rows.Count
If NewRange.Item(NrIndex).Value <> "" Then
Set SearchedFor = OldRange.Find(NewRange.Item(NrIndex), LookIn:=xlValues)

If Not SearchedFor Is Nothing Then
Range("'walloffame-u864xprt.xls'!B" & NrIndex).Value = "Exists"
Else
Range("'walloffame-u864xprt.xls'!B" & NrIndex).Value = "Does Not Exist"
End If
End If
Next NrIndex
End Sub

I see where the match occurs and does not. I just can't figure out how to tell it that when it matches insert the value "Exists" AND copy cell C from new to old sheet.

I'm sure it is something pretty simple. It always seems to be simplicity that gets me. Kinda like not being able to see the forrest for the trees.


I am attaching new and old sheets so you can see what I mean.

I hope I have explained this well. I need to copy the description from new to old as well as do the "exist" and "does not exist"

The only difference is the attached are .txt and in the macro they are .xls (Because I can only attach a .txt)

Thanks,

Bill

Bill,
Without looking too closely at the files, an easy suggestion is to have
two more procedures in the module that do everything you want done.
Also, you might find your code more easy to read (and consequently get
more people to help you) if you indent procedures and functions (thus
the expression 'nesting'). Take a look at this:

Sub CheckExistenceCopy()
Dim NewRange As Range
Set NewRange = Range("'walloffame-u864xprt.xls'!A:A")

Dim OldRange As Range
Set OldRange = Range("'toystore-112105.XLS'!A:A")

Dim NrIndex As Long
Dim OrIndex As Long

Dim SearchedFor As Range

For NrIndex = 1 To NewRange.Rows.Count
If NewRange.Item(NrIndex).Value <> "" Then
Set SearchedFor = OldRange.Find(NewRange.Item(NrIndex),
LookIn:=xlValues)

If Not SearchedFor Is Nothing Then
ExistsMacro
Else
DoesNotExistMacro
End If
End If
Next NrIndex

End Sub

Sub ExistsMacro ()
Range("'walloffame-u864xprt.xls'!B" & NrIndex).Value = "Exists"
' Copying and pasting you want to do
End Sub

Sub DoesNotExistMacro ()
Range("'walloffame-u864xprt.xls'!B" & NrIndex).Value = "Does Not
Exist"
' Copying and pasting you want to do
End Sub

Have fun,
Joe

Thanks joe,

I still get errors.

Maybe I should make this easier for everyone. Lets forget about putting the exists message in there.

Let's just say I have two excel files. New has all the data I need, and old has some of the data but is has been cut off.

How can I compare cell A in new to cell A in old and if they match copy cell C from new to cell C in old?

I think I have made it as simple as possible. I don't mind several macros that I must run in sequence if it will make it easier.

Both excel files have mostly the same data but not really in the same order.
It's real important that cell A2 gets compared to all cells in A and then cell A3 to all cells in A, etc....

I just want to take the good data from cell C in new and copy over the bad data in cell C of old, but cell A must match in both otherwise the data will become mixed and corrupt.

Maybe it's just not possible. Maybe I don't know how to explain it well enuff.

Thanks Joe, I'll be trying to get what you gave me to do what I need. But until then I welcome all help!!

Bill

Bill,
Am I oversimplifying what you are saying when I ask if you can just
overwrite all the old data with all the new data? Like,

Sub FixSheet
Workbooks("New").Sheets("Sheet1").Range("A:A").Copy _
Workbooks("Old").Sheets("Sheet1").Range("A:A")
End Sub

Also, try looking in Excel Help (not VB help) and check out the
functions OR and EXACT. It may require copying and pasting the ranges
to a staging area for comparison and sorting.
I'm in the middle of ten things right now, but when I get a chance I
will look at your files.
Have fun,
Joe

Thanks Joe,

Don't rush. I am just asking to see if what I need done is possible so that I don't have to hand copy and paste about 4000 items.

I'll look into the example you just gave and see can I figure it out.

I know there are ways of doing things but it just seems that what I want is so simple as far as explaining. I do know from experience that sometimes the things that seem simple are really complex as far as how to go about getting that result.

Thanks again,

Bil

Let me see if I can possibly clarify this. I sure hope this helps in understanding my problem.

I'm going to just skip the other stuff and go for a (I hope) *simple* compare and copy.


if sheet2 range(a:a) = sheet1 range(a:a) then copy sheet2 range(c:c) to sheet1 range(c:c)

That's only how my very limited understanding of excel would be expressed in a simplified manner.


I have two sheets. Sheet2 is like the master sheet, it contains everything (and more) that sheet1 contains, only both sheets are in a different (or random) order.

I need to compare (sheet to sheet) a1 to a1, if match then copy c1 to c1

so basically, I want something like this:

compare sheet2 a1 to sheet1 a1,a2,a3,a4,a5,etc
if get match then copy sheet2 c1 to sheet1 c1

then move on to sheet2 a2 to sheet1 a1,a2,a3,etc
if get match then copy sheet2 c1 to sheet1 c1

until each cell in column A of sheet2 has been checked against each cell in column A of sheet1, making sure to copy cell c as the matches occur.

column A in both sheets are unique identifiers that are alpha-numeric.

Sheet1 needs to stay in the order it is in.


can anyone help me?