Free Microsoft Excel 2013 Quick Reference

Highlight Differences Between Two Worksheets

Hey Guys,
I have a centrally located excel document. it is updated by my team mates frequently during week's time. I need to compare last week's and current sheet to find out updates during the week's time.
I want to compare the two excel sheets and generate a report in to third one.
The document contains
1. 5 lines of header ( common in both sheets)
2. 2 columns (non editable and common to both the sheets)
3. Values in cells starting from (6,C) can be changed.

I want to generate a report with 1,2 in above list as they are and specific cells (3 in above list) highlighted only if different.

I wrote a function to do this. it worked but, I could not map it with userform.
My userform accepts paths, names and sheets of 2 worksheets.

Please help.

Thnx a lot.


Post your answer or comment

comments powered by Disqus
I have two different worksheets but with similar data ( text values ). But I
want to find the difference between two worksheets. I tried to compare side
by side but its time consuming. If excel is going to have this auto kind of
function to find the differenct between two worksheets, that would be useful
to millions of excel users!

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Find differences between two Excel workbooks or worksheets

Often an Excel spreadsheet gets modified over time and you want to find

out what has changed.

Or you may import data into Excel worksheets and want to see how the
imported data has changed as well.

There is a neat little utility called DiffEngineX which produces
difference reports when run on two different Excel workbooks. It also
automatically copies the workbooks being compared and highlights the
different cells.

It has advanced options such as grouping equivalent changes together
such that one change to a range of cells is reported instead of tens of
separate changes.

If you want to download a free 30 day trial go to

http://www.florencesoft.com
Compare Excel Workbooks Files Diff Tool

--
savage_planet

Find differences between two Excel workbooks or worksheets

Often an Excel spreadsheet gets modified over time and you want to find

out what has changed.

Or you may import data into Excel worksheets and want to see how the
imported data has changed as well.

There is a neat little utility called DiffEngineX which produces
difference reports when run on two different Excel workbooks. It also
automatically copies the workbooks being compared and highlights the
different cells.

It has advanced options such as grouping equivalent changes together
such that one change to a range of cells is reported instead of tens of
separate changes.

If you want to download a free 30 day trial go to

http://www.florencesoft.com
Compare Excel Workbooks Files Diff Tool

--
savage_planet

Hi
I'm trying to calculate the difference between two dates and times and returns the answer in days hours mins for workdays only. I've seen similar threads but nothing seems to be quite right or I can't get to work

E2 contains date/time call raised in custom format dd/mm/yyyy hh:mm
M2 contains date/time action taken in custom format dd/mm/yyy hh:mm
on another worksheet I have a list called "holidays" that will have all the public holidays

I basically want AA2 to show M2-E2 in days & hh:mm only or even just hh:mm for working days only assuming a standard 5 day week with standard working hours (I have not specified working days or working hours anywhere). Also what format should cell AA2 be set up as?

Oh and if M2 isn't filled in yet I need the cell AA2 to remain blank.

Help would be gratefully appreciated
Tammy

Hey guys,

I have used the macro below to great effect to find the differences between two worksheets. However the results are output into a new spreadsheet. I was wondering whether this could be tweaked to overwrite the results onto worksheet 1 instead?

N.B. This code is was beyond my VBA capabilities so take it easy on me!

Regards,

jyoung86


	VB:
	
 Worksheet) 
    Dim r As Long, c As Integer 
    Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer 
    Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String 
    Dim rptWB As Workbook, DiffCount As Long 
    Application.ScreenUpdating = False 
    Application.StatusBar = "Creating the report..." 
    Set rptWB = Workbooks.Add 
    Application.DisplayAlerts = False 
    While Worksheets.Count > 1 
        Worksheets(2).Delete 
    Wend 
    Application.DisplayAlerts = True 
    With ws1.UsedRange 
        lr1 = .Rows.Count 
        lc1 = .Columns.Count 
    End With 
    With ws2.UsedRange 
        lr2 = .Rows.Count 
        lc2 = .Columns.Count 
    End With 
    maxR = lr1 
    maxC = lc1 
    If maxR < lr2 Then maxR = lr2 
    If maxC < lc2 Then maxC = lc2 
    DiffCount = 0 
    For c = 1 To maxC 
        Application.StatusBar = "Comparing cells " & Format(c / maxC, "0 %") & "..." 
        For r = 1 To maxR 
            cf1 = "" 
            cf2 = "" 
            On Error Resume Next 
            cf1 = ws1.Cells(r, c).FormulaLocal 
            cf2 = ws2.Cells(r, c).FormulaLocal 
            On Error Goto 0 
            If cf1  cf2 Then 
                DiffCount = DiffCount + 1 
                Cells(r, c).Formula = "'" & cf1 & "  " & cf2 
            End If 
        Next r 
    Next c 
    Application.StatusBar = "Formatting the report..." 
    With Range(Cells(1, 1), Cells(maxR, maxC)) 
        .Interior.ColorIndex = 19 
        With .Borders(xlEdgeTop) 
            .LineStyle = xlContinuous 
            .Weight = xlHairline 
        End With 
        With .Borders(xlEdgeRight) 
            .LineStyle = xlContinuous 
            .Weight = xlHairline 
        End With 
        With .Borders(xlEdgeLeft) 
            .LineStyle = xlContinuous 
            .Weight = xlHairline 
        End With 
        With .Borders(xlEdgeBottom) 
            .LineStyle = xlContinuous 
            .Weight = xlHairline 
        End With 
        On Error Resume Next 
        With .Borders(xlInsideHorizontal) 
            .LineStyle = xlContinuous 
            .Weight = xlHairline 
        End With 
        With .Borders(xlInsideVertical) 
            .LineStyle = xlContinuous 
            .Weight = xlHairline 
        End With 
        On Error Goto 0 
    End With 
    Columns("A:IV").ColumnWidth = 20 
    rptWB.Saved = True 
    If DiffCount = 0 Then 
        rptWB.Close False 
    End If 
    Set rptWB = Nothing 
    Application.StatusBar = False 
    Application.ScreenUpdating = True 
    MsgBox DiffCount & " cells contain different formulas!", vbInformation, _ 
    "Compare " & ws1.Name & " with " & ws2.Name 
End Sub 
Sub TestCompareWorksheets() 
     ' compare two different worksheets in the active workbook
    CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2") 
     ' compare two different worksheets in two different workbooks
     'CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
    Workbooks("WorkBookName.xls").Worksheets("Sheet2") 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Credit to Erlandsen Data Consulting for the original macro.

Hi

I have an excel worksheet that has two date columns.

I want to calculte the difference between two dates and express this in Years and Months (without days shown).

I can already calculate the total of days between the two dates but thats no good for me.

So, 31 March 2007 minus 31 January 2006 would need to equal "1 year 2 months".

Look forward to hearing from you.

I have tried many different formats and formulas and the worksheet will not
calculate the difference between two times formatted as follows.

6/12/2006 8:03:14 PM 6/12/2006 8:08:55 PM #VALUE!
B8 C8

the formula I used is =C8-B8 and I continue to get #VALUE!!!!!!!!!

I have read the Excel help and it did not help.

i need a formula/function to calculate the difference between two dates in
microsoft excel worksheet.

I have two worksheet contain same customer's name in column B, but they might
be in different row, how can I make sure they are in same row between two
worksheet, also when I add new customer's name by inserting row in worksheet
one, how can it be added to worksheet two in the same row so that they are
always consistent?

Thanks a great!!

Hank

I'm looking to automate the process of making a chart that highlights the difference between two lines on a chart. I have figured out how to denote the area between lines using an area chart, but I'd like to have the area between the two lines be a different color if the delta is positive or negative.

I've attached a simple example, which I made by doing a line chart and drawing the areas between them using the free form shapes tool. This is not too time consuming, but would have to be redone every time the data changes. I'm hoping somebody knows a better approach.

Thanks

I have tried many different formats and formulas and the worksheet will not
calculate the difference between two times formatted as follows.

6/12/2006 8:03:14 PM 6/12/2006 8:08:55 PM #VALUE!
B8 C8

the formula I used is =C8-B8 and I continue to get #VALUE!!!!!!!!!

I have read the Excel help and it did not help.

Hi All,

Was wondering if someone was able to help me with a little problem that i'm having. I am trying to find the difference between two dates and times but i need to minus the weekends.

My Spreadsheet currently looks like like this:

B3 has 15/11/2010 8:56:00 AM
C3 has 22/11/2010 11:34:00 AM
D3 has =(INT(C3)-INT(B3))+MAX(MOD(C3,1)-MAX(MOD(B3,1))) Which = 7.109722
E3 has =INT(TEXT(D3,"[h]")/24)&" days"&TEXT(MOD(D3,1),""" and"" h ""hours and ""mm"" minutes""") Which = 7 Days and 2 Hours and 38 Minutes

What i'm looking to do is have a list of all the weekends for the month in my spreadsheet and if the dates go through the weekend they will be subtracted from the total days. So my example for the example should be: 5 Days and 2 Hours and 38 Minutes.

Any help would be great

Thanks in Advanced

How can find the largest difference between two neighbouring cells? Say I have data like this:

12
14
15
7
5
8
32
33

But extending down 50000 cells. How do ifind the largest difference (in the case above its between 8 and 32). Thanks for your help,

Luke

I need to create a list between two worksheets in Excel. My example would be that column D on worksheet A needs to have a drop down list in each cell. A selection from Worksheet B is where the information to fill that drop down list comes from, such as cell A1-A8. I know it has something to do with the Validation selection under Data but I can't remember all the steps.

Thanks, Mike

I need to write a macro that would create a graph that shows a difference between two data series. Mainly, I need to figure out which chart type is best suited for such a task, then I can probably figure out VBA code for it myself, though, any help is appreciated.

This is an age old query, I received help some months back on calculating the difference between two dates. There remains one problem:

What I have at the moment:
I have a cell called CURRENT and one called TARGET.

Current has todays date and target has 29/07/05.

I have a cell called TO GO which has the formula = Target - Current

The cell is formated in the following: yy mm dd hh:mm:ss

There is VBA which updates NOW so that I have a countdown.
(Dim SchedRecalc As Date


	VB:
	
 SetTime() 
    SchedRecalc = Now + TimeValue("00:00:01") 
    Application.OnTime SchedRecalc, "Recalc" 
End Sub 
 
Sub Recalc() 
    Range("Current").Value = Now 
    Call SetTime 
End Sub 
 
Sub Disable() 
    On Error Resume Next 
    Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False 
End Sub) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
All this works fine.

The Problem:

The result in the cell TO GO is not accurate. It does not show the exact number of days to go. For example if Current = 29/07/04 and Target = 29/07/05 I want to see TO GO showing that there are 12 Months and 0 days to go.

Any ideas why the DAY part is not giving the answer I am expecting?

MZP

I have looked for this but im not sure what to call the method. I need a formula that calculates the difference between two times and give the result as follows.

Example 1

2:00 AM - 5:00 AM = 3.0

Example 2

2:30 AM - 3:00 = 0.50

Eample 3

2:15 AM - 3:00 AM = 0.75

I need it to show a period style answer and the range from 0.0 to 24.0 depending on the times of course any help would be great.

Fellow Forum Members,
Calculating the difference between two times is easy to do in Excel when only two columns are involved such as:

Column A Column B
StartTime EndTime
---------- -----------
1/3/11 4:00 PM 1/6/11 2:00 PM

A formula like: (B1-A1) calculates the difference.

However, how does the formula look like when instead of two columns you have four columns? My START time has two columns. One for the Start DATE (i.e. 1/3/11) and a second for the Start TIME (i.e. 4:00 PM). Then I also have two columns for the end time. One column for the END date and a second column for the END time. I would appreciate it a lot if some one can help me develop a formula that will calculate the difference between two times when four columns of time related data are at play. Thanks in advance.

Dear Experts,

While using the datedif function to find the difference between two dates in months and days i am getting error value comparing to the manual calculation login. Is there any other function to find out the result as per the desired requirement. Pls find the example below.

Manual Calculation Using Datedif Formula
Start Date End Date Month Days Month Days Difference
3/12/2007 8/1/2008 16 21 16 20 -1
1/10/2006 8/1/2008 30 23 30 22 -1
6/2/2006 8/1/2008 25 29 25 30 1
4/2/2006 8/1/2008 27 29 27 30 1
2/3/2005 8/1/2008 41 26 41 29 3
2/2/2007 8/1/2008 17 27 17 30 3
11/1/2007 8/1/2008 9 0 9 0 Correct Value
11/1/2007 8/1/2008 9 0 9 0 Correct Value
11/1/2007 8/1/2008 9 0 9 0 Correct Value
11/1/2007 8/1/2008 9 0 9 0 Correct Value
12/25/2004 8/1/2008 43 7 43 7 Correct Value
10/25/2004 8/1/2008 45 7 45 7 Correct Value
10/25/2003 8/1/2008 57 7 57 7 Correct Value

formula used DATEDIF(A3,B3,"M")
DATEDIF(A3,B3,"MD")
Hope some one can solve this.

Dear Experts,

I would like to know which function is to be used for finding out the difference between two days in months and balance days.

For example

18/1/2004 to 01/08/2008 - 54 months & 14 days

Thanks

Hi,
I want to calculate the difference between two times (these will always be in the same day, and will not span midnight). For example:

Start time (Cell B2): 08:30:00
End time (Cell C2): 18:00:00
Duration (Cell D2): 08:30:00

I now want to subtract 30 minutes lunch period from this calculation.

Many thanks,
Doug.

I am trying to show the percentage difference between two numbers and also
show the overage in red....Help!

Hi,
How can I calculate with a formula the time difference between two times
when the second time is on the next day ( the cells are formatted as time ).
i.e. the difference between 17:00 today to 01:30 next day.
Thank you
GL

Hi there,

I am trying to get a difference between two months, I looked every
where in excel date functions and on excel groups to no avail.

I came across the datedif function but for some reason this function
works very strange. lets say I have a date in cell A1 as 12/31/2005 and
the date in cell B1 is 2/1/2006. now the difference in months should be
2 and not 1 as this function indicates. when I use 12/1/2005 as my
start date then I get 2 as an answer, not sure why the inconsistency.

this is the current formula --> DATEDIF(A1,B1,"m")

I realize that 12/31 is the end of the month for december and therfore
it is caluclating the month difference between 12/31 and 2/1 as one
month. but I want the difference in months, so if I have 12/1 or 12/31
as my starting date and my ending date is 2/1 or 2/28, I still want the
difference to show as 2.

I would appreciate any feedback/input for this problem..

thanks a lot...


No luck finding an answer? You could always try Google.