# If Statement For Comparing 3 Values

Jun 25, 2014

How would I write a IF statement comparing 3 values. For example the field that it would be referencing would have values cell E51(1,2,3,4,5,6,7,...99). The pull back would be:

1-10 = \$250,000
11-15 = \$550,000
16+ = \$750,000

## If Statement Failing When Comparing Text Box Values On Userform

Jun 12, 2014

I am trying to compare to values that a user inputs in a user form. It's ok if they are not the same I just want the user to know it. I am only including the part where the if statement is failing:

VB:
If Me.VSLongQuant.Value <> Me.VSShortQuant.Value Then
MsgBox VerUnBal
If VerUnBal = vbNo Then
Exit Sub
End If
End If

I have VerUnBal as Long and defined as:

VB:
VerUnBal = MsgBox("The Long and Short option Qunatities are not equal." & Chr(13) & _
"Is this intended to be an unbalanced vertical spread?", vbYesNo, _

I can provide more info as necessary. There are two problems 1) the message box is appearing whether the numbers are equal or not, and 2) clicking No in the message box does not cause the Sub to exit.

## Comparing Dates In Two Columns Using IF AND OR Statement

Apr 14, 2014

I'm trying to determine if one date meets the criteria to be considered 'on time'. I have two columns: estimated completion date and completion date. I want to compare the completion date to the estimated completion date and if completion date is <= completion date but is NOT = 1/1/2099 then I met my date. I've tried this several different ways and problem is I cannot make it work.

When estimated completion date is 1/1/2099 'on time' should be 'no'
When estimated completion date is not 1/1/2099 but is greater than completion date 'on time' should be 'no'
When completion date is <= estimated completion date and estimated completion date is not equal to 1/1/2099 'on time' should be 'yes'

And I have a null date to deal with also but if I can satisfy the first 3 criteria I can manipulate the null values manually.

## Excel Sheet1 Comparing Data - Get Stuck After Second IF Statement

Aug 1, 2013

=IF(M7="","",IF(K8=J8,K8&\$J\$3&I8,IF(K8=I8,K8&\$J\$3&H8,IF(K8=H8,K8&\$J\$3&G8,IF(K8=G8,K8&\$J\$3&F8,K8&\$J\$3&J8)))))

I have tried several options, ISERROR(FIND(.................also EXACT(........ and they all seem to get stuck, after the second expression.

Basically, I have a ROW of Cells, with numbers inside them, like so:-

F8.....G8....H8....I8...J8....K8
27.....33....10.....4....4.....4

And looking from right to left, I am asking this question:-

If K8=J8, then I will have K8 & I8 as my result.
If K8=I8, then I will have K8 & H8 as my result.
If K8=H8, then I will have K8 & G8 as my result.
If K8=G8, then I will have K8 & F8 as my final result, Else its K8&J8 as my FALSE.
(the \$J\$3 = a comma, for number separation, and further LEN string capture.)

Every time I have made this formula, with other types of Logic, it only ever finds the second tier depth as the result,
and I wonder why, since they are several nested IF statements in line!!

The incomplete Excel sheet is attached : S 1_2 tracker Forum.xlsx

The result of the above formula should be 4,10

## Macro To Give Count Of Unique Values After Comparing Comma Separated Values?

Mar 2, 2014

I need a Macro (not formula) which compares the comma separated values present in Column "I" with individual values present in Column "D" and generate the count of unique values in Column "J".

The sample sheet has been attached for reference.

## Comparing Input Value From Set Of Values 1 And Output Data From Set Of Values 2

Sep 28, 2012

Got a road block on one of the trackers which I am trying to create which compares some of our agreements. Basically we need to compare current production with our contracts and then bring the tier level they are at in order to then calculate how much in overrides we receive. Belwo is an example:

Input Value = 140000
Out Should be = 1.5%

Set of Values 1
T1
T2
T3
T4

[Code]...

In a nut shell it will look for 14000 in set of values 1 and pick T3 to then go in set of values 2 and display corresponding T3 which will be 1.5% in above example as output.

## Comparing Values (to See Any Duplicated Values Using Macro) ?

Sep 14, 2008

on a sheet i have 2 column with values.

For e.g

DevID DevNum
123 s123
123 s234
987 sabc
987 sabc

Is there a way where i can compare the values in DevNum with the same DevID
to see if theres any duplicated values using macro?

## Comparing 2 Values.

Nov 5, 2008

Comparing 2 values.

I need to compare the value's on 2 cells. I have a sheet setup for my sales I would like to compare each day of the week. So in other words I would like to compare this Monday with last Monday and know what the percentage of my sales ether up or down is.

Example:
Last Monday = sales \$100.00
This Monday = Sales \$200.00

100%

So If sales are up I would like the percentage amount font to be in green. But if sales are down I would like the percentage amount to be in red.

## Comparing Cell Values?

May 28, 2014

I have a football score system and wish to compare the values of various cells - 2 if possible, but more if not possible.

Essentially I have a list of predictions for the score of a game (e.g. 2-1, 1-0, 0-0, 2-2 etc) in a spreadsheet, and I have a cell where I enter the correct scoreline (e.g. 2-1). The way the points system works is that if the prediction is correct (i.e. 2-1) then that player gets 3 points. If the result/outcome is correct but the scoreline is incorrect (i.e. 1-0) then the player gets 1 point. If they result/outcome and scoreline are incorrect, then that player gets 0 points.

Ideally I would link to compare 2 cells - predicted score and actual score, where the format of the scores is entered as 2-1.

However, I feel this may be easier having to columns for the score - Column A = 2 Column B = 1. In this way the scores only need to be compared cell by cell, however they would need to be compared by value for the correct outcomes.

## Comparing Values In Two Columns?

Jul 13, 2013

There are two columns A and B and values there in

A B
1 1
2 2
3 3
4 4
9 5
10 6
11 13
15 15

As you can see there are certain numbers that are missing between column B and A. I want to know which of the numbers that are present in column B are missing in column A ...any handy calculation ?

## Comparing With Key Values In 2 Workbooks

Dec 13, 2005

I work for a financial firm. Every morning I compare today's cash balance file (sent from our custodian) with yesterday's to see if there is anything different in each portfolio cash balance column. Right now I'm doing it manually checking row by row. It'll become tedious if more and more portfolios are added. I'm looking for an efficient way I can use the portfolio codes column (as a key) to compare with previous day's file�s portfolio codes column , and list only those portfolios(in another workbook) if the corresponding cash balance column doesn't match. This is what I started doing:

Private Sub compare_cash_bttn_Click()

Dim b1 As Workbook, b2 As Workbook, b3 As Workbook

Dim w1 As Worksheet, w2 As Worksheet, w3 As Worksheet

Dim i As Integer

i = 2

Set b1 = Workbooks("CashA")

Set b2 = Workbooks("CashB")

'Set b3 = ActiveWorkbook

Set w1 = b1.Sheets(qry4rCash).........................

## Comparing Values And Returning Different Column

May 31, 2007

I want to return the proper value from sheet 2 into column C in sheet one. I have the current formula set up as an array but I only get the value that matches the first row in Sheet 2.

HTML Column A Column B Column C
Date Number Who
3/23/2007 902-555-2596=IF(\$B2=Sheet2!\$A\$2:\$A\$2001,Sheet2!\$B\$2:\$B\$2001,)
3/23/2007 980-123-6621=IF(\$B2=Sheet2!\$A\$2:\$A\$2001,Sheet2!\$B\$2:\$B\$2001,)
3/23/2007 980-123-6621=IF(\$B2=Sheet2!\$A\$2:\$A\$2001,Sheet2!\$B\$2:\$B\$2001,)
3/23/2007 980-555-6621=IF(\$B2=Sheet2!\$A\$2:\$A\$2001,Sheet2!\$B\$2:\$B\$2001,)
3/23/2007 980-678-0352=IF(\$B2=Sheet2!\$A\$2:\$A\$2001,Sheet2!\$B\$2:\$B\$2001,)
3/23/2007 902-555-7958=IF(\$B2=Sheet2!\$A\$2:\$A\$2001,Sheet2!\$B\$2:\$B\$2001,)
3/23/2007 980-555-6621=IF(\$B2=Sheet2!\$A\$2:\$A\$2001,Sheet2!\$B\$2:\$B\$2001,)
3/23/2007 980-555-6621=IF(\$B2=Sheet2!\$A\$2:\$A\$2001,Sheet2!\$B\$2:\$B\$2001,)
3/23/2007 902-555-7958=IF(\$B2=Sheet2!\$A\$2:\$A\$2001,Sheet2!\$B\$2:\$B\$2001,)
3/23/2007 902-555-7958=IF(\$B2=Sheet2!\$A\$2:\$A\$2001,Sheet2!\$B\$2:\$B\$2001,)
~~~~~~~~~~~ Sheet 2 ~~~~~~~~~~~~~~~~~
Column A Column B
902-555-7958Big Bird
902-555-5100Daffy Duck
902-678-0352Elmo
902-555-5752Donald Duck
902-555-7420Bert
980-123-6621Ernie
902-555-2596Grover

## Comparing Columns For Values Greater Than Each Other

Apr 27, 2009

I have two columns A and B. I need a count of how many times a value in B is greater than the same row in A. I don't want to insert a new column (e.g. for if statements to add up).

Can I insert a countif at the top to do that? I tried various countif variations but could not find the right one. Additionally any other option that does not involve adding new columns/rows.

## Comparing Previous Values Of A Cell

Nov 4, 2009

I have a cell (F2) that is auto updated with total sales units for today (Its a simple counter of individual sales). I want to detect when there is a "batch" of sales >= 10. ie. F2 may start at zero for the day and increment by 1,2,5 or 10 units at a time. If the increment is 10 units or more I would like to detect this and store the result in cell G4.

Typical values in C2: 0, 1, 6, 7, 9, 19, 21, 22 etc as new orders arrive. The larger order between 9-19 (viz 10 or more), I need to detect and store in G4. I know that I need to use both the current value of F2 (say 19) and the previous value of F2 (say 9) to perform this, but the solution eludes me.

## Comparing And Copying Values Between Dates

Feb 19, 2010

I�m attempting to find values that are within a band of .001 of the values of cells in columns L through O and searching columns B through I for values that meet the .001 criteria and copying the values to columns Q, R, S, and T respectively.

I mentioned the values in L, M, N and O are where the comparisons will be made and these values are tagged to a specific date in Column K. I need to find the dates in Column A that are in between the values of K and the cell beneath it. For example, in K4 I have a date of 10/3/99 and the next date I have in K5 is 10/5/99. I would then query Column A for the dates 10/06/99 (A4), 10/07/99 (A5), and 10/08/99 (A6). The values I need to find within the .001 band are in Columns B through I (B4:I6). Lastly, when reach the last value in Column K, query all values in Column A greater than last value in K and copy the appropriate values in B through I to Q through T.

Rules for copying data to Column Q, R, S, and T:

For Column Q-
Find values in B, C, or D that�s within plus or minus .001 of M and copy the value of B, C, or D into Column Q of the same row.

For Column R-.................

## Macro - Comparing Values In One Column

Nov 13, 2009

I'll try my best to explain what I'm trying to do here using Excel macro (i've very limited knoledge in VBA ). I am working on a project where I'm using an actuator to measure micro-switches. The values are then extracted using HyperTerminal via RS232 and transferred to Excel to filter the values and display only the ones that are important.

Here is what I'd like to do via a Macro:

Compare cells in column A (e.g A1 and A2, A2 and A3, A3 and A4 etc.)

Example:

A
0
300
550
1200
1700
1900
2200
2000
1850

[Code]....

So first, if the values increase, do nothing and keep comparing other values. If we find the values decrease, display the larger value between the two, in this case it's the value 2200 that we want to display.

Next, the values will be decreasing progressively. We'll do the comparison between 2 values but this time if the values decrease, do nothing until we find a value increases. Display then the smaller value between the 2, here it's 1500.!

## Mod Function For Comparing Looping Values

Dec 30, 2006

The following formula (thanks to Barrie) determines if the difference between 2 values equals a third value, within a fourth �margin of error� value, and returns either True or False.

=AND(C1-D1

## Macro: Comparing Values In One Column

Nov 13, 2009

I'm trying to do here using Excel macro (i've very limited knoledge in VBA ). I am working on a project where I'm using an actuator to measure micro-switches. The values are then extracted using HyperTerminal via RS232 and transferred to Excel to filter the values and display only the ones that are important.

Here is what I'd like to do via a Macro:
Compare cells in column A (e.g A1 and A2, A2 and A3, A3 and A4 etc.)

Example:
A
0
300
550
1200
1700
1900
2200
2000
1850
1750
1600
1500
1950
2500
4000

So first, if the values increase, do nothing and keep comparing other values. If we find the values decrease, display the larger value between the two, in this case it's the value 2200 that we want to display.

Next, the values will be decreasing progressively. We'll do the comparison between 2 values but this time if the values decrease, do nothing until we find a value increases. Display then the smaller value between the 2, here it's 1500.

## Comparing Cell Values In Two Worksheets

May 11, 2007

I am aware this question must have been asked to death on this forum and I already searched and found some answers but for some reason I can�t get it to work. I have two worksheets (sheet1 and sheet2). In Column E of Sheet1 I have product descriptions that I want to compare cell by cell to the product descriptions in Column F of Sheet 2. If they are not the same, color in the cell in Sheet 2. There are 1810 rows in both columns. I tried this method but it gives me an error (in spanish) that says that I cannot compare ranges of data.

I have also seen some code to do something similar (http://www.ozgrid.com/forum/showpost...85&postcount=3 ):

Sub find_cell()
Range("A:E"). Find(What:=Cells(2, 6).Value, LookAt:=xlPart).Activate
ar = ActiveCell.Row
ac = ActiveCell.Column
' the two lines above store the row and column values of the cell found
' in your example ar is going to be equal to 229 and ac is 3
' now this following lines are to change the colors to red
With Cells(ar, ac).Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End Sub

I don�t quite get the "Range("A:E").Find(What:=Cells(2, 6).Value, LookAt:=xlPart).Activate" part though.

## Hiding Particular Rows By Comparing Values In Array?

Mar 25, 2013

I need to hide the rows in an excel whose value is equal to the array list that has been already hardcoded...How to do this...

## Comparing Two Columns Of Data And Returning Values That Do NOT Appear In Both

Apr 22, 2014

I have an attendance sheet that employees scan their badge number, and returns their name and time/date stamp using a simple VLOOKUP.

I want to create a report that compares the names in the attendance sheet against the all the names in the master sheet, and only returns the names that have not attended the training.

I have tried a few test formulas using COUNTIF function, but with no luck.

I have attached an example. Use the worksheet labeled April 2014 A (2nd shift)

New Six Point Safety with Attendance Sheet Barcode Scanning.xlsx‎

## Comparing Two Rows Replacing Values In Columns

May 29, 2014

I am facing a problem in evaluating a problem. I am referencing from the screenshot of excel file. I want to replace the values of Column-C for which column-A matches with column -G. And, the remaining will remain same.

Capture.jpg

I am also attaching the excel file.

## Print Unique Values By Comparing Two Columns

Feb 24, 2014

Am looking for a technique to print unique values by comparing 2 columns

Below is example

Col A
Col B
Col C (Output expected)

1
4
1

[Code]...

## Weeknumbers - Not Work At Year End When Comparing Values

Jun 16, 2014

I am using a formula for weeknumbers and formatting cells based on their values but have just realised it will not work at year end.

The formula in D2 is as follows:
=IF(\$C2<\$O2,"green","blank")

C2 is the weeknumber of an incident based on the actual date in B2 (e.g. 01/06/2014) and uses the formula: =WEEKNUM(B2). O2 is the weeknumber of the current day and uses the formula: =WEEKNUM(NOW()).

So cell D2 states "green" if the incident happened over a week ago, and states "blank" if the incident happened in the current week.

If I used this at year end when the incident weeknumber may be 52 and the current weeknumber may be 1 - the formula would not work as it would state "blank" rather than "green", even though the incident happened over a week ago.

How can I get the year encorporated within the weeknumber?

## Comparing Cell Values And Moving Whole Rows?

Jul 2, 2014

I'm trying to compare the values of columns B and G and if any of the cells are G < B, I would like to move the whole row into the second worksheet. And only if possible, it would be awesome if the macro could check column A for duplicates before the value comparison and sum up the column B if there are duplicates. In my example row 6 and 7 on column A have the same suppliers code and the amount totals to 2.

In this case for example, the value of the third row of G is lower than B, and therefore I would like the whole row 3 to be cut out from worksheet 1 and moved to worksheet 2.

Original view of sheet 1:
SUPPLIERSCODE
ORDERAMOUNT
PRODUCTNAME
BARCODE

SUPPLIERSCODE
WAREHOUSE STOCK

And then cut out the rows in which G

## Comparing 2 Lists (names) And Finding Missing Values

Jan 23, 2014

I have a list of over 2000 names and need to establish which names from column A appear in Column B, as well as which do not appear in column B.

I attach an example : NameCheck.xlsx‎

## Copy Rows Based On Comparing Values In Columns

Nov 26, 2012

I'm new to macros and VBA so I am wondering if it is possible for a macro to compare values in column B of sheet2 and column B of sheet3 and for the values that DON'T match i.e. unique values to column B of sheet2, copy row from sheet2 to sheet4? This will aid my processing time drastically if I can copy all the rows from sheet 2 to sheet 4 where the model number in column B of sheet 2 doesn't appear in column B of sheet 3!!

## Comparing Values And Returning Info From Separate Cells

Sep 18, 2013

If I am working from cell (D16), I am looking for a formula to compare the value in 2 different cells (D8 and D12) to a value in a third cell (D14) and the one with the closest value without going over returns a seperate value from either (D7 or D11), But if both D8 and D12 are above the value of (D14) then the closest value would be the answer and return the answer from (D7 or D11).

Example: D7=Bob and D8=25
D11=John and D12= 40
D14= 45
OR:
D7=Bob and D8=46
D11=John and D12=48
D14=45

## Comparing 2 Columns With Numbers / Letters And Alphanumeric Values

May 13, 2014

I have 2 columns i want to find out which items match in each column and put the matching value in column c. I have tried Vlookup and continue to get an N/A .. I tried countif and I get either an N/A or a value error. I have tried turning the cells into text but that is not working either..

column A has about 1700 rows and column B has about 4000

MOST CELLS ARE 6-7 VARIABLES.. satrting with either 01, 02, 03 with 4-5 letters following or have a 6 digit number or 6 letter value.

## Automating Comparing Cell Values And Then Processing A Formula

Apr 10, 2007

I have the need within a spreadsheet to automatically compare the value of 2 cells and if they match exactly to move the cursor to a group of cells. If they do not match then move to another group of cells. The only way that I can see to do this is via Macros. However I still need to do the automatic compare first.