Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Compare two spreadsheet
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default Compare two spreadsheet
Hello every one ,
Am a new bee to Ms excel. Am trying to find way to
compare rows in two different spread sheets lets say spread A and
Spread sheet B . I like to compare row with a key for example taking
teamnumber as Key in two sheets and update data under Jan Feb march
april in spread sheet B and put comments in a file on what was updated
with time stamp.

Spread sheet A.

Id teamname funds jan feb march april
3 greatteam 10 1 2 4 3

Spread Sheet B

Location teamname funds jan feb march april
Dallas greatteam 11 .5 1 2 1

Out put should look like
---------------------------------------------

Spread sheet B
Location teamname funds jan feb march april
Dallas greatteam 10 1 2 4 3

and it would be great if comments or any other form of log file is
creasted with what all are updated. Appreciate the help/input

thanks

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Compare two spreadsheet
Try this code. Change MyFolder as required. I used sheet1 and sheet2 as
sheet names. Change as needed.

Sub updateteam()

Const MyFolder = "c:\temp\team.log"
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Dim fs, f, ts, s
Set fs = CreateObject("Scripting.FileSystemObject")

fs.CreateTextFile MyFolder 'Create a file
Set f = fs.GetFile(MyFolder)
Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)

With Sheets("Sheet1")
.Activate
LastRowSh1 = Cells(Rows.Count, "B").End(xlUp).Row
Set TeamRange = .Range(.Cells(2, "B"), _
.Cells(LastRowSh1, "B"))

Sheets("Sheet2").Activate
LastRowSh2 = Cells(Rows.Count, "B").End(xlUp).Row
For RowCount = 2 To LastRowSh2

Set c = TeamRange.Find( _
what:=Cells(RowCount, "B"), _
LookIn:=xlValues)
If Not c Is Nothing Then
'compare fund
If Cells(RowCount, "C") < _
.Cells(RowCount, "C") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed Fund From: " & _
Trim(Str(.Cells(RowCount, "C"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "C")))
ts.writeline msg

Cells(RowCount, "C") = _
.Cells(RowCount, "C")
End If
'compare jan
If Cells(RowCount, "D") < _
.Cells(RowCount, "D") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed Jan From: " & _
Trim(Str(.Cells(RowCount, "D"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "D")))
ts.writeline msg
Cells(RowCount, "D") = _
.Cells(RowCount, "D")
End If
'compare feb
If Cells(RowCount, "E") < _
.Cells(RowCount, "E") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed February From: " & _
Trim(Str(.Cells(RowCount, "E"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "E")))
ts.writeline msg
Cells(RowCount, "E") = _
.Cells(RowCount, "E")
End If
'compare march
If Cells(RowCount, "F") < _
.Cells(RowCount, "F") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed March From: " & _
Trim(Str(.Cells(RowCount, "F"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "F")))
ts.writeline msg
Cells(RowCount, "F") = _
.Cells(RowCount, "F")
End If
'compare april
If Cells(RowCount, "G") < _
.Cells(RowCount, "G") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed April From: " & _
Trim(Str(.Cells(RowCount, "G"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "G")))
ts.writeline msg
Cells(RowCount, "G") = _
.Cells(RowCount, "G")
End If

Else
msg = "Did not find team " & Cells(RowCount, "B")
ts.writeline msg
MsgBox (msg)
End If
Next RowCount
End With

ts.Close

End Sub


" wrote:

Hello every one ,
Am a new bee to Ms excel. Am trying to find way to
compare rows in two different spread sheets lets say spread A and
Spread sheet B . I like to compare row with a key for example taking
teamnumber as Key in two sheets and update data under Jan Feb march
april in spread sheet B and put comments in a file on what was updated
with time stamp.

Spread sheet A.

Id teamname funds jan feb march april
3 greatteam 10 1 2 4 3

Spread Sheet B

Location teamname funds jan feb march april
Dallas greatteam 11 .5 1 2 1

Out put should look like
---------------------------------------------

Spread sheet B
Location teamname funds jan feb march april
Dallas greatteam 10 1 2 4 3

and it would be great if comments or any other form of log file is
creasted with what all are updated. Appreciate the help/input

thanks


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default Compare two spreadsheet
On Sep 9, 10:44 am, Joel wrote:
Try this code. Change MyFolder as required. I used sheet1 and sheet2 as
sheet names. Change as needed.

Sub updateteam()

Const MyFolder = "c:\temp\team.log"
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Dim fs, f, ts, s
Set fs = CreateObject("Scripting.FileSystemObject")

fs.CreateTextFile MyFolder 'Create a file
Set f = fs.GetFile(MyFolder)
Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)

With Sheets("Sheet1")
.Activate
LastRowSh1 = Cells(Rows.Count, "B").End(xlUp).Row
Set TeamRange = .Range(.Cells(2, "B"), _
.Cells(LastRowSh1, "B"))

Sheets("Sheet2").Activate
LastRowSh2 = Cells(Rows.Count, "B").End(xlUp).Row
For RowCount = 2 To LastRowSh2

Set c = TeamRange.Find( _
what:=Cells(RowCount, "B"), _
LookIn:=xlValues)
If Not c Is Nothing Then
'compare fund
If Cells(RowCount, "C") < _
.Cells(RowCount, "C") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed Fund From: " & _
Trim(Str(.Cells(RowCount, "C"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "C")))
ts.writeline msg

Cells(RowCount, "C") = _
.Cells(RowCount, "C")
End If
'compare jan
If Cells(RowCount, "D") < _
.Cells(RowCount, "D") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed Jan From: " & _
Trim(Str(.Cells(RowCount, "D"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "D")))
ts.writeline msg
Cells(RowCount, "D") = _
.Cells(RowCount, "D")
End If
'compare feb
If Cells(RowCount, "E") < _
.Cells(RowCount, "E") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed February From: " & _
Trim(Str(.Cells(RowCount, "E"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "E")))
ts.writeline msg
Cells(RowCount, "E") = _
.Cells(RowCount, "E")
End If
'compare march
If Cells(RowCount, "F") < _
.Cells(RowCount, "F") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed March From: " & _
Trim(Str(.Cells(RowCount, "F"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "F")))
ts.writeline msg
Cells(RowCount, "F") = _
.Cells(RowCount, "F")
End If
'compare april
If Cells(RowCount, "G") < _
.Cells(RowCount, "G") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed April From: " & _
Trim(Str(.Cells(RowCount, "G"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "G")))
ts.writeline msg
Cells(RowCount, "G") = _
.Cells(RowCount, "G")
End If

Else
msg = "Did not find team " & Cells(RowCount, "B")
ts.writeline msg
MsgBox (msg)
End If
Next RowCount
End With

ts.Close

End Sub



" wrote:
Hello every one ,
Am a new bee to Ms excel. Am trying to find way to
compare rows in two different spread sheets lets say spread A and
Spread sheet B . I like to compare row with a key for example taking
teamnumber as Key in two sheets and update data under Jan Feb march
april in spread sheet B and put comments in a file on what was updated
with time stamp.

Spread sheet A.

Id teamname funds jan feb march april
3 greatteam 10 1 2 4 3

Spread Sheet B

Location teamname funds jan feb march april
Dallas greatteam 11 .5 1 2 1

Out put should look like
---------------------------------------------

Spread sheet B
Location teamname funds jan feb march april
Dallas greatteam 10 1 2 4 3

and it would be great if comments or any other form of log file is
creasted with what all are updated. Appreciate the help/input

thanks- Hide quoted text -

- Show quoted text -

Thanks Joel ,
This might be a new bee question i get error message
at fs.createtextfile myfolder in

fs.CreateTextFile MyFolder 'Create a file
Set f = fs.GetFile(MyFolder)
Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)


routine. Inital guess was may be i should change privilalges on my
folder ...and try that ...that didnt really help should i change any
privillages ?
Appreciate it

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default Compare two spreadsheet
On Sep 9, 12:42 pm, wrote:
On Sep 9, 10:44 am, Joel wrote:





Try this code. Change MyFolder as required. I used sheet1 and sheet2 as
sheet names. Change as needed.

Sub updateteam()

Const MyFolder = "c:\temp\team.log"
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Dim fs, f, ts, s
Set fs = CreateObject("Scripting.FileSystemObject")

fs.CreateTextFile MyFolder 'Create a file
Set f = fs.GetFile(MyFolder)
Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)

With Sheets("Sheet1")
.Activate
LastRowSh1 = Cells(Rows.Count, "B").End(xlUp).Row
Set TeamRange = .Range(.Cells(2, "B"), _
.Cells(LastRowSh1, "B"))

Sheets("Sheet2").Activate
LastRowSh2 = Cells(Rows.Count, "B").End(xlUp).Row
For RowCount = 2 To LastRowSh2

Set c = TeamRange.Find( _
what:=Cells(RowCount, "B"), _
LookIn:=xlValues)
If Not c Is Nothing Then
'compare fund
If Cells(RowCount, "C") < _
.Cells(RowCount, "C") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed Fund From: " & _
Trim(Str(.Cells(RowCount, "C"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "C")))
ts.writeline msg

Cells(RowCount, "C") = _
.Cells(RowCount, "C")
End If
'compare jan
If Cells(RowCount, "D") < _
.Cells(RowCount, "D") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed Jan From: " & _
Trim(Str(.Cells(RowCount, "D"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "D")))
ts.writeline msg
Cells(RowCount, "D") = _
.Cells(RowCount, "D")
End If
'compare feb
If Cells(RowCount, "E") < _
.Cells(RowCount, "E") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed February From: " & _
Trim(Str(.Cells(RowCount, "E"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "E")))
ts.writeline msg
Cells(RowCount, "E") = _
.Cells(RowCount, "E")
End If
'compare march
If Cells(RowCount, "F") < _
.Cells(RowCount, "F") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed March From: " & _
Trim(Str(.Cells(RowCount, "F"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "F")))
ts.writeline msg
Cells(RowCount, "F") = _
.Cells(RowCount, "F")
End If
'compare april
If Cells(RowCount, "G") < _
.Cells(RowCount, "G") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed April From: " & _
Trim(Str(.Cells(RowCount, "G"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "G")))
ts.writeline msg
Cells(RowCount, "G") = _
.Cells(RowCount, "G")
End If

Else
msg = "Did not find team " & Cells(RowCount, "B")
ts.writeline msg
MsgBox (msg)
End If
Next RowCount
End With

ts.Close

End Sub

" wrote:
Hello every one ,
Am a new bee to Ms excel. Am trying to find way to
compare rows in two different spread sheets lets say spread A and
Spread sheet B . I like to compare row with a key for example taking
teamnumber as Key in two sheets and update data under Jan Feb march
april in spread sheet B and put comments in a file on what was updated
with time stamp.

Spread sheet A.

Id teamname funds jan feb march april
3 greatteam 10 1 2 4 3

Spread Sheet B

Location teamname funds jan feb march april
Dallas greatteam 11 .5 1 2 1

Out put should look like
---------------------------------------------

Spread sheet B
Location teamname funds jan feb march april
Dallas greatteam 10 1 2 4 3

and it would be great if comments or any other form of log file is
creasted with what all are updated. Appreciate the help/input

thanks- Hide quoted text -

- Show quoted text -

Thanks Joel ,
This might be a new bee question i get error message
at fs.createtextfile myfolder in

fs.CreateTextFile MyFolder 'Create a file
Set f = fs.GetFile(MyFolder)
Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)

routine. Inital guess was may be i should change privilalges on my
folder ...and try that ...that didnt really help should i change any
privillages ?
Appreciate it- Hide quoted text -

- Show quoted text -

error message is runtime error 70 permission denied

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Compare two spreadsheet
I made a change to the from RowCount to c.Row in a fedw places to improve the
code.

I tried duplicating the Error 70. If the path name doesn't exist the error
code wuould be Path Not found. The only way I got the 70 error was to make
the file team.log Read Only. Other error I got by illegal names was an error
76 such a leaving out the backslash in the path name c:temp\team.log.

Try making the file team.log with note pad. Open notepad and then save the
file in any location. This is a good test to see if you have the write
permissions. Also try the code in a directory you know that you have write
permission.

It is always best to split problems into pieces. Get the code working, then
worry about putting the file in the place you really want to write the log
file.


Sub updateteam()

Const MyFolder = "c:\temp\team.log"
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Dim fs, f, ts, s
Set fs = CreateObject("Scripting.FileSystemObject")

fs.CreateTextFile MyFolder 'Create a file
Set f = fs.GetFile(MyFolder)
Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)

With Sheets("Sheet1")
.Activate
LastRowSh1 = Cells(Rows.Count, "B").End(xlUp).Row
Set TeamRange = .Range(.Cells(2, "B"), _
.Cells(LastRowSh1, "B"))

Sheets("Sheet2").Activate
LastRowSh2 = Cells(Rows.Count, "B").End(xlUp).Row
For RowCount = 2 To LastRowSh2

Set c = TeamRange.Find( _
what:=Cells(RowCount, "B"), _
LookIn:=xlValues)
If Not c Is Nothing Then
'compare fund
If Cells(RowCount, "C") < _
.Cells(c.Row, "C") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed Fund From: " & _
Trim(Str(.Cells(c.Row, "C"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "C")))
ts.writeline msg

Cells(RowCount, "C") = _
.Cells(c.Row, "C")
End If
'compare jan
If Cells(RowCount, "D") < _
.Cells(c.Row, "D") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed Jan From: " & _
Trim(Str(.Cells(c.Row, "D"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "D")))
ts.writeline msg
Cells(RowCount, "D") = _
.Cells(c.Row, "D")
End If
'compare feb
If Cells(RowCount, "E") < _
.Cells(c.Row, "E") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed February From: " & _
Trim(Str(.Cells(c.Row, "E"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "E")))
ts.writeline msg
Cells(RowCount, "E") = _
.Cells(c.Row, "E")
End If
'compare march
If Cells(RowCount, "F") < _
.Cells(c.Row, "F") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed March From: " & _
Trim(Str(.Cells(c.Row, "F"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "F")))
ts.writeline msg
Cells(RowCount, "F") = _
.Cells(c.Row, "F")
End If
'compare april
If Cells(RowCount, "G") < _
.Cells(c.Row, "G") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed April From: " & _
Trim(Str(.Cells(c.Row, "G"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "G")))
ts.writeline msg
Cells(RowCount, "G") = _
.Cells(c.Row, "G")
End If

Else
msg = "Did not find team " & Cells(RowCount, "B")
ts.writeline msg
MsgBox (msg)
End If
Next RowCount
End With

ts.Close

End Sub


" wrote:

On Sep 9, 12:42 pm, wrote:
On Sep 9, 10:44 am, Joel wrote:





Try this code. Change MyFolder as required. I used sheet1 and sheet2 as
sheet names. Change as needed.

Sub updateteam()

Const MyFolder = "c:\temp\team.log"
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Dim fs, f, ts, s
Set fs = CreateObject("Scripting.FileSystemObject")

fs.CreateTextFile MyFolder 'Create a file
Set f = fs.GetFile(MyFolder)
Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)

With Sheets("Sheet1")
.Activate
LastRowSh1 = Cells(Rows.Count, "B").End(xlUp).Row
Set TeamRange = .Range(.Cells(2, "B"), _
.Cells(LastRowSh1, "B"))

Sheets("Sheet2").Activate
LastRowSh2 = Cells(Rows.Count, "B").End(xlUp).Row
For RowCount = 2 To LastRowSh2

Set c = TeamRange.Find( _
what:=Cells(RowCount, "B"), _
LookIn:=xlValues)
If Not c Is Nothing Then
'compare fund
If Cells(RowCount, "C") < _
.Cells(RowCount, "C") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed Fund From: " & _
Trim(Str(.Cells(RowCount, "C"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "C")))
ts.writeline msg

Cells(RowCount, "C") = _
.Cells(RowCount, "C")
End If
'compare jan
If Cells(RowCount, "D") < _
.Cells(RowCount, "D") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed Jan From: " & _
Trim(Str(.Cells(RowCount, "D"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "D")))
ts.writeline msg
Cells(RowCount, "D") = _
.Cells(RowCount, "D")
End If
'compare feb
If Cells(RowCount, "E") < _
.Cells(RowCount, "E") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed February From: " & _
Trim(Str(.Cells(RowCount, "E"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "E")))
ts.writeline msg
Cells(RowCount, "E") = _
.Cells(RowCount, "E")
End If
'compare march
If Cells(RowCount, "F") < _
.Cells(RowCount, "F") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed March From: " & _
Trim(Str(.Cells(RowCount, "F"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "F")))
ts.writeline msg
Cells(RowCount, "F") = _
.Cells(RowCount, "F")
End If
'compare april
If Cells(RowCount, "G") < _
.Cells(RowCount, "G") Then

msg = Cells(RowCount, "B") & ": " & _
"Changed April From: " & _
Trim(Str(.Cells(RowCount, "G"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "G")))
ts.writeline msg
Cells(RowCount, "G") = _
.Cells(RowCount, "G")
End If

Else
msg = "Did not find team " & Cells(RowCount, "B")
ts.writeline msg
MsgBox (msg)
End If
Next RowCount
End With

ts.Close

End Sub

" wrote:
Hello every one ,
Am a new bee to Ms excel. Am trying to find way to
compare rows in two different spread sheets lets say spread A and
Spread sheet B . I like to compare row with a key for example taking
teamnumber as Key in two sheets and update data under Jan Feb march
april in spread sheet B and put comments in a file on what was updated
with time stamp.

Spread sheet A.

Id teamname funds jan feb march april
3 greatteam 10 1 2 4 3

Spread Sheet B

Location teamname funds jan feb march april
Dallas greatteam 11 .5 1 2 1

Out put should look like
---------------------------------------------

Spread sheet B
Location teamname funds jan feb march april
Dallas greatteam 10 1 2 4 3

and it would be great if comments or any other form of log file is
creasted with what all are updated. Appreciate the help/input

thanks- Hide quoted text -

- Show quoted text -

Thanks Joel ,
This might be a new bee question i get error message
at fs.createtextfile myfolder in

fs.CreateTextFile MyFolder 'Create a file
Set f = fs.GetFile(MyFolder)
Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)

routine. Inital guess was may be i should change privilalges on my
folder ...and try that ...that didnt really help should i change any
privillages ?
Appreciate it- Hide quoted text -

- Show quoted text -

error message is runtime error 70 permission denied


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
excel spreadsheet compare chance diego Excel Discussion (Misc queries) 4 July 24th 08 12:41 AM
comparing spreadsheets spreadsheet compare add-in Janis Excel Programming 2 April 11th 07 06:10 PM
Compare two different spreadsheet Nobita_87 Excel Discussion (Misc queries) 1 April 25th 06 07:09 AM
Spreadsheet compare? Mike Excel Discussion (Misc queries) 1 May 31st 05 09:09 PM
VLOOKUP for spreadsheet compare bbquestion2003 Excel Programming 1 July 26th 04 08:36 PM


All times are GMT +1. The time now is 10:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement