if cell value matches then copy another cell

[billbeecham]

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

[Joe Fish]

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

[billbeecham]

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

[Joe Fish]

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

[billbeecham]

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

[billbeecham]

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?

[DataCollector]

Here's a manual way to do it...

Add a new worksheet, copy Column A and C from your Sheet2 into the
Column A and B of the new worksheet. Give the range (both columns) a
name like MyData and sort it on Column A

Skip a few columns over and paste the data from Sheet1. Sort on the
first column (the once that should match Column A)

Let's say the data from Sheet1 starts in Column H2. In G2, immediately
to the left of the data from Sheet1, type = Vlookup(H2(the cell
immediately to the right),MyData, 2,FALSE)
Copy the formula all the way down your column of data. If there is a
match for H2 in MyData, the formula will result in the value in Column
B (which is your Column C that you wanted to add to Sheet1.

When you're satisfied that everything looks okay, do a Copy, Paste
Special on the new column created with the formula (to retain the value
rather than the formula) and delete Columns A and B and cut, paste the
resultant column into the position you want it.

It's really pretty fast once you get the hang of it.


--
DataCollector
------------------------------------------------------------------------
DataCollector's Profile: http://www.hightechtalks.com/m368
View this thread: http://www.hightechtalks.com/t2295167

[billbeecham]

Datacollector,

Thanks for trying but as I stated the data in sheet1 cannot be sorted. It must remain the same. I tried the vlookup anyway just to see what would happen and it doesn't work. I'm guessing that "the cell immediatley to the right" means h2 again... that just produces an error. I followed what you said and put things where you said but it still errored.

What I need is a simple compare a1 to a1 and copy c1 to c1.

the data I am comparing and copying is but two fields out of about 25, and there are about 3500+ rows of data.

Maybe you meant something specific with the vlookup. If you think it will work, maybe you could elaborate on what the whole formula looks like assuming columns A and B are A and C from sheet2 and that column a from sheet 1 is residing in H2.

Thanks again,

Bill

[DataCollector]

Hi,

Sorry you're having so many problems. VLOOKUP won't work if you can't
sort the data.
I use it all the time for spreadsheets with 50,000 rows, so give it a
try when sorting the data is an option.

DataCollector


--
DataCollector
------------------------------------------------------------------------
DataCollector's Profile: http://www.hightechtalks.com/m368
View this thread: http://www.hightechtalks.com/t2295167

[billbeecham]

Yeah, me too. Thanks for trying to help.

I just need to take the time to learn more about this.

I have code to compare like I want using column a. I just need to figure out how to incorporate a range for C so I can do a copy paste. I have the copy pste code down, but cannot seem to get the result I want.

Maybe if anyone else out there would like to give a try at it?

Here is what I have so far, it compares sheet1 a to sheet 2 a but only copies straight down. I need it to copy the same C location as the A location.

Like, if it compared A23 to A23 I need it to copy C23 to C23 (in sheet1 and sheet2 of course)


so here is my awful macro:

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

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

'Dim MidRange As Range
'Set MidRange = Range("'walloffame-u864xprt.xls'!C:C")


Dim NrIndex As Long
Dim OrIndex As Long
'Dim MrIndex As Long

Dim SearchedFor As Range
'Dim SearchedForAgain 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 MidRange.Item(NrIndex).Value <> "" Then
'Set SearchedForAgain = MidRange.Find(NewRange.Item(NrIndex), LookIn:=xlValues)

'If Not SearchedForAgain Is Nothing Then
If Not SearchedFor Is Nothing Then


'Range("'toystore-112105.XLS'!c" & NrIndex).Value = OldRange.Find(NewRange.Item(NrIndex), LookIn:=xlValues)
Range("'walloffame-u864xprt.xls'!C" & NrIndex).Copy Destination:=Range("'toystore-112105.XLS'!c" & NrIndex)
'End If
'End If
End If
End If


Next NrIndex

End Sub

[Pete]

Back to DataCollector's suggestion, why can't you add another column
containing a sequence - i.e. fill it with 1,2,3, etc, then you can
always sort the data back into the original sequence, then remove this
column. Like him, I do similar things with upwards of 40,000 rows, so
3,500 shouldn't create too many problems.

Pete

[DataCollector]

I think Pete's idea is great...Numbering your original sequence and then
using that to return your data to the original order after using the
VLOOKUP.

I've never had to worry about retaining a certain order so hadn't
thought that through. That's what's great about this forum. No
matter how long you've been doing this, you can learn something
everyday.


--
DataCollector
------------------------------------------------------------------------
DataCollector's Profile: http://www.hightechtalks.com/m368
View this thread: http://www.hightechtalks.com/t2295167