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


+-------------------------------------------------------------------+
|Filename: walloffame-u864xprt.txt |
|Download: http://www.excelforum.com/attachment.php?postid=4073 |
+-------------------------------------------------------------------+

--
billbeecham
------------------------------------------------------------------------
billbeecham's Profile: http://www.excelforum.com/member.php...o&userid=16286
View this thread: http://www.excelforum.com/showthread...hreadid=488417

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


--
billbeecham
------------------------------------------------------------------------
billbeecham's Profile: http://www.excelforum.com/member.php...o&userid=16286
View this thread: http://www.excelforum.com/showthread...hreadid=488417

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").Cop y _
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 s
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
want is so simple as far as explaining. I do know from experience tha
sometimes the things that seem simple are really complex as far as ho
to go about getting that result.

Thanks again,

Bi

--
billbeecha
-----------------------------------------------------------------------
billbeecham's Profile: http://www.excelforum.com/member.php...fo&userid=1628
View this thread: http://www.excelforum.com/showthread.php?threadid=48841

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?


--
billbeecham
------------------------------------------------------------------------
billbeecham's Profile: http://www.excelforum.com/member.php...o&userid=16286
View this thread: http://www.excelforum.com/showthread...hreadid=488417

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

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

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

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

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

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

--
DataCollecto
-----------------------------------------------------------------------
DataCollector's Profile: http://www.hightechtalks.com/m36
View this thread: http://www.hightechtalks.com/t229516

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


--
billbeecham
------------------------------------------------------------------------
billbeecham's Profile: http://www.excelforum.com/member.php...o&userid=16286
View this thread: http://www.excelforum.com/showthread...hreadid=488417

Hi,

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

DataCollecto

--
DataCollecto
-----------------------------------------------------------------------
DataCollector's Profile: http://www.hightechtalks.com/m36
View this thread: http://www.hightechtalks.com/t229516

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


--
billbeecham
------------------------------------------------------------------------
billbeecham's Profile: http://www.excelforum.com/member.php...o&userid=16286
View this thread: http://www.excelforum.com/showthread...hreadid=488417

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

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

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

--
DataCollecto
-----------------------------------------------------------------------
DataCollector's Profile: http://www.hightechtalks.com/m36
View this thread: http://www.hightechtalks.com/t229516