Free Microsoft Excel 2013 Quick Reference

Differences between two columns.

Does anyone out there know a simpleway to compare the values in two different
columns on an Excel spreadhseet and pick out all of the cells in the first
number that have a value that the second column doesn't have? Thanks.


How can view differences between two columns or rows number are not neighbors?
...and to see this different values in another color font or autofilling ??
Thanks anticipate..

Morning,

Having a few problems, I'm trying to work out the difference between two columns, so usually I would use:

=A2-B2

However the values in these columns range from:
N
B
3
4
5
6
7
8
EP

The value for N needs to be 4 and the value for B needs to be 2, and EP needs to be 9

Any ideas how I can do this?

Thanks for any help you can give

I would like to know if the Pivot Table can calculate the difference between
two columns of data for me. For example, I have the amount of loans in
December in column A and the amount of loans in January in column B and I
would like to see the increase or decrease. THe way I've been doing this is
manually entering in the calculation.

Thanks in advance!

Hi, ive created a pivot table from my data.

From the table I want to build a formula that calculates differences between two columns (asset and liability).
I want just the formula in vba form, because I want to paste that formula after the last column of the pivot table in autofill mode.

The pivot changes in column number and row number so i cant really hard-code the formula to any cell or use the conventional method of using the pivot field settings.

Attached is a sample data. Need to calculate columns A minus B based on the Header "Name", not column and in VBA using Pivot table.

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.

Need to calculate the difference between two cells in a column. Exemple

A B C
Start 1 date/time
open date/time
: date/time
: date/time
: :
Start2 date/time =date2/time2 -date1/time1 (HH:MM)

I can select two consecutive "Start cells" with "IF"s, but the max of "IF"
supported by Excel is 7. I have no solution if there are more than 7 lines
between tho consecutive "start"s (or any other like "open" etc..)

I'd be glad to get some help.

68magnolia71 (in france)

Hi forum champs,

I would like to know how to check two columns with two other columns(Comparing data).
My work is to find out the differences between the two columns with the other two columns of data namely,Receipt No & Amount in the main worksheet with the Receipt No & Amount column from other worksheet.Sometimes the RECEIPT No. APPEARS more than once in either sheets and the amount is bifurcated/splitted in two amounts.

I tried with VLOOKUP concatenating the two columns in Main sheet with the other sheet.But was not happy with the try...Attached a sample worksheet.

Please help me ....

EVEN VBA is also a welcome...

Thanks.......

Need to calculate the difference between two cells in a column. Exemple

A B C
Start 1 date/time
open date/time
: date/time
: date/time
: :
Start2 date/time =date2/time2 -date1/time1 (HH:MM)

I can select two consecutive "Start cells" with "IF"s, but the max of "IF"
supported by Excel is 7. I have no solution if there are more than 7 lines
between tho consecutive "start"s (or any other like "open" etc..)

I'd be glad to get some help.

68magnolia71 (in france)

Excel enthusiasts,
I am trying to create a subroutine that compares the difference between the two tests scores (column B & C) and highlights the row that show the greatest improvement (MAX) in green, and the worst improvement decline (MIN) in pink. I want to use a “For Next” loop to get this done. I have the following code but there is an error in my MAX and MIN functions. Can I get some help to obtain this objective? (See attached workbook.)
I would also like to run the subroutine with a shortcut.

'Shortcut key Alt-B to run subroutine
"TestDiff".’

Application.OnKey "%b", "TestDiff"

'Use "For Next" subroutine to find max and min differences between column ranges Test 1 & Test 2'
'Then highlight row (columns A,B,&C) for max value in green & highlight row for min value in pink'

Public Sub TestDiff()

Dim Test1 As Range, Test2 As Range, Diff As Double

Set Test1 = Range("B4:B13")
Set Test2 = Range("C4:C13")
Test1 -Test2 = Diff
    For Each Diff In ActiveSheet
            =MAX(IF(B4:B13=10,C4:C13))                'Determines MAX for Diff
End If
            =MIN(IF(B4:B13=10,C4:C13))                 'Determines MIN for Diff
End If

    Next Diff
 
'Color cell interior green'
If Cell.Value = Max Then
            Cell.Interior.ColorIndex = 4
End If
            
'Color cell interior pink'
If Cell.Value = Min Then
            Cell.Interior.ColorIndex = 7
End If

    End If

End Sub
Name Test #1 Score Test #2 Score
Bob Fenton 67 53
Mary Right 75 89
Gale Hernandez 98 95
Mark Deutsch 89 82
Gary Miles 72 73
Fred Flinstone 99 85
Barry Gillian 83 79
Nancy Aguirre 85 72
Sharon Fox 79 80
Martin Noonan 59 58

** This post is also found cross-posted at MrExcel.com. - Excelforum.com is the original post. So, please reply to this post in this forum right where you are at right here. (i.e., http://www.mrexcel.com/forum/showthr...41#post2936141 ) I will note this for the MrExcel participants to post here in this forum; and provide them with the link to this exact location. Thank-you.

Hi I'm back again,
Can you help if what formula do i use to compare two columns?
Or do I need to run macros to do these?

Basically I have 2 columns, A and B.
There are datas on both columns, i want to have a return value on column C which
will display the difference between the 2 columns.
You will see on my dummy excel sheet that i have datas abc etc.. on columns A and B
I was hoping to get a return on column C with the values that is not common on one another.
On this example i should be getting R,W,Z,X,R

Thank you in advance.

Hi, Need your help to calculate the time difference between two date and time columns.

I am new to excel functions. Can you please help me how to find the time difference between two date and time columns? I need the output in HH:MM format.

ex: A1 cell value is 2/1/2011 8:00AM and B1 cell value is 2/2/2011 8:30PM. The time difference is 36:30. I tried to use HOUR, INT and NETWORKDAYS functions. But, I am getting an error. "#VALUE!" is being displayed instead of the time output. I chose the cell type as TIME from the "Format Cells" option.

Please help me.

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 need a way to subtract the difference between two dates and exprss the
difference in a format simular to 10 years 6 months 7 days. Assume a start
date of 12/19/1943 and an end date of Today(). I have no trouble using
datedif to get the number of years as 61 years, months as 11. I have now
idea of how to convert the total number of days between the two dates to the
correct number days. sngNumDays = DateDiff("d", DateOne, Now) gives the
total number of days (22657).

My current Code:

Private Sub cmdAge_Click()
Dim DateOne As Date
Dim intYears As Integer
Dim intMonths As Integer
Dim sngNumDays As Single
Dim introw As Integer
For introw = 2 To 4 '3 different dates are
entered on rows 2 through 4
DateOne = Cells(introw, 13).Value 'get the start date from
the spreadsheet
sngNumDays = DateDiff("d", DateOne, Now) 'total number of years
in the period
intYears = Int(sngNumDays / 365)
intMonths = DateDiff("m", DateOne, Now) 'months is total months
in the time period
intMonths = intMonths - (intYears * 12) 'convert to months
difference in the current year
sngNumDays = DateDiff("d", DateOne, Now) 'total number of days in
the period
Me.Unprotect
'print the results to the row and column on the spread sheet
Cells(introw, 14).Value = " " & intYears & " Years " & intMonths &
" Months " & sngNumDays & " Days"
Me.Protect
Next
End Sub

I need a way to subtract the difference between two dates and exprss the
difference in a format simular to 10 years 6 months 7 days. Assume a start
date of 12/19/1943 and an end date of Today(). I have no trouble using
datedif to get the number of years as 61 years, months as 11. I have now
idea of how to convert the total number of days between the two dates to the
correct number days. sngNumDays = DateDiff("d", DateOne, Now) gives the
total number of days (22657).

My current Code:

Private Sub cmdAge_Click()
Dim DateOne As Date
Dim intYears As Integer
Dim intMonths As Integer
Dim sngNumDays As Single
Dim introw As Integer
For introw = 2 To 4 '3 different dates are
entered on rows 2 through 4
DateOne = Cells(introw, 13).Value 'get the start date from
the spreadsheet
sngNumDays = DateDiff("d", DateOne, Now) 'total number of years
in the period
intYears = Int(sngNumDays / 365)
intMonths = DateDiff("m", DateOne, Now) 'months is total months
in the time period
intMonths = intMonths - (intYears * 12) 'convert to months
difference in the current year
sngNumDays = DateDiff("d", DateOne, Now) 'total number of days in
the period
Me.Unprotect
'print the results to the row and column on the spread sheet
Cells(introw, 14).Value = " " & intYears & " Years " & intMonths &
" Months " & sngNumDays & " Days"
Me.Protect
Next
End Sub

Hi

I have used an If function to compare two columns and have match or mismatch. For the mismatches, I'd like to see how different they are. Is this possible.

IE

Column A Column B Similar/Different
ABC Holdings XYZ Corp Different
ABC Holdings ABC Holdings Plc Similar
Thanks in advance

(I'm using Excel 2002)

I want to make a croos section between two columns on a third column.
Actualy, I have two colums, first one containing a lists of numbers from 1 to
2000 and a second column with 700 numbers from the same range (i.e. 1, 4 ,17,
22, 23 ...). I need a third column that has the difference of 1300 numbers
that do not appear in the second column.
Thanks

Hello,

I’ve encountered some problems with Excel functions.

What I want is to rank a difference between two cells within the differences between values in the corresponding columns.

More specifically, in column D, I want to rank B3-C3 within all differences between values in columns B and C, without calculating first the differences in column D and only after to rank the values in column E.

B C
Man Woman
1 2,34 3,65
2 3,40 4,76
3 4,00 3,54
4 4,50 3,45
5 3,90 2,68
6 2,10 4,31

Sorry if you find this question very simple, but I just don't know how to do it.

Please answer if there is any solution.

I have two columns that contain mostly the same info and I want to highlight the ones that are different. The instructions in this thread worked great, but the problem is some of my cells are blank and Excel is highlighting these, too. Is there a way to tell Excel to compare cells only if they both have data in them?

Thanks in advance.

Hello,

I need to calculate the difference between two cells for an entire column. We have a total sales amount and deposit amount. I need the balance calculated in the next column for each sale.

On the back of that, I need the totals to do the same at the bottom of the spreadsheet.

I have attached an image for clarification.

Many thanks in advance for your help. I know I have been hammering the forums with newbie questions....but I really do appreciate your efforts to help!

Kind regards,

Dave

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

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

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

Hi All

can any one suggest me to find out inequalities between two columns such that,

if A1- testing

B1 - testng

then the char "i" should be higlighted with color

I am trying to work out overtime hours for maritime crews. In the underlined section below "Total Shift Length", this needs to be the number of hours difference between the column Completion of shift and the column commencement of shift. I think the difficulty is that start times and finish times may not necessarily be on the same date (overnight). The end result also needs to be as a decimal e.g 15.25 not 15:15.

Any help would be appreciated!
DATE SHIFT CREW COMMENCEMENT OF SHIFT - AT BASE COMPLETION OF SHIFT - AT BASE TOTAL SHIFT LENGTH 9/07/12 EARLY 23 01:15 15:30 15.25 10/07/12 EARLY 17 23:30 13:45 14.25

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.