Compare Cells & If Equal Perform Function
Jan 25, 2008
I need to vba help on the following:
For 1st A1 to Lastrow
{
if cells are equal
{
F1 = (C1+E1) - (B1+D1)
}
}
below is the records that i'm trying to worked on..has about couple thousands rows...
Description Debit Credit Debit Credit Profit
74HBHZMPXC 3,321.00
74HBHZMPXC 3,321.00
74HBHZMPXC 10.00 10.00
75HBCTIWSI 30.55 30.55
75HBJWFZEU 1,095.60
75HBJWFZEU 1,095.60
75HBJWFZEU 2,198.00 8.00
76HA25FYGB 925.00
76HA25FYGB 925.00
76HA25FYGB 1,856.00
76HA25FYGB 1,856.00
76HA25FYGB 1,856.00 6.00
View 9 Replies
ADVERTISEMENT
Jan 31, 2010
I need to compare the data in three fields and if the same ignore but if different then add them with a space and a hyphen.
I use this formula for 2 fields and it works great I just can't get it to work with three.
=IF(TRIM(Data!BC2)=TRIM(Data!AY2),TRIM(Data!BC2),TRIM(Data!BC2)&" - "&TRIM(Data!AY2))
I use the trim command as the users entering data often add unnecessary spaces etc. and it combines the data even when the same.
View 13 Replies
View Related
Jun 10, 2009
I'm trying to create a formula where I can compare two cells but accept them as equal is one is plus/minus 0.1.
Example:
Cell A1 is 3, Cell A2 is 3.2, I want Cell A3 to turn red (or report "1")
Cell A1 is 3, Cell A2 is 3.1, I want Cell A3 to turn green (or report "0")
View 2 Replies
View Related
Aug 6, 2013
I have two sets of data to compare. One is real world data, the other is forecasted data. I have real daily data for every day over about 3 months. The predictions though, there may be 4-6 predictions for ever one real daily value.
What I'd like to do is have a function that looks at Column A's date (real daily value) and Column C's date (predicted data) and if Column C equals Column A, then divide corresponding data in Column B by value in Column D.
View 2 Replies
View Related
Nov 27, 2013
I want the function to return a value in A1 based on the values in B1, C1, or D1.
For instance:
If B1=1, then A1=Confirmed,
if C1=1, then A1=Ordered,
if D1=1, then A1= Confirmed.
My attempt:
=IF(B1="1","Confirmed",IF(C1="1","Ordered",IF(D1="1","Confirmed")))
I realize this is a nested IF function, and in this case does not seem entirely appropriate, evidenced by the circular reference warning.
View 4 Replies
View Related
Jun 10, 2006
As an example, cell values as follows.
A1 = 1, 2 or 3, B1 = 2.5
Is it possible to say, if A1= 1 then make C1 equal to B1+4, otherwise return the value of 0?
View 6 Replies
View Related
Jan 6, 2010
I need to look at 2 different cells and have a 3rd cell provide a number based on the first two cells.
Column A: either 1 or 2 (2 results in +5)
Column B: either yes or no (yes results in a +5)
Column C: function would result in either 0, 5, or 10.
View 3 Replies
View Related
Apr 8, 2014
I have two sheets. One is named sheet1 where all my data is and sheet2 where all my values are. I want to do a find a replace on column N (sheet1) using the data in sheet2. Column A on Sheet2 has all the values that are found in column N and column B on Sheet2 has what the data should change to.
So for example:
Sheet1 says the following on column N:
cat
dog
lion
bear
Sheet2 says:
Column A
A1: Cat
A2: Dog
A3: Bear
Column B
B1: 2
B2: 8
B3:15
I want the values on column N to be replaced with 2, 8, 15 and so forth. I use excel 2010.
View 1 Replies
View Related
Feb 6, 2014
In the past I have used VBA script to change column with size based on cell values as per follows:
[Code]....
Now I wish to change row size in a similar fashion only the criteria will be a maths function rather than a string in a single cell. The maths function will simply be a sum of cells in a row. I will need to test whether or not the row ads up to a number greater than zero.
To illustrate what I am trying to achieve I have (poorly) written out a rough attempt at the code below:
[Code] ....
I am unsure how the syntax works for this, I considered defining the range as a string first so I could test the value of a single variable (by defined name) rather than directly referencing the row of cells in the formula. As this code needs to be repeated many time for many rows the simplest solution would be b.
View 7 Replies
View Related
Jan 31, 2013
Exel 2010 cells formatted to Time
need to look at h1 and h2 if no data ignore
other wise add data together if total exceeds 08:45
then deduct 45min from h1
if combined the are less than 8:45 then h3 must equal 8:00 by altering h1 only
Example
h1= 5:00
h2= 2:00
h3= 8:00
h1=
h2=
h3=
h1= 8:00
h2= 2:00
h3= 7:15
View 3 Replies
View Related
Mar 10, 2014
Converting old reports to usable excel format. I am having trouble using VBA to set up a looping macro that would search Column A for cells with the text string: "ACTIVITY TOTAL". If cell has that value I would like to perform text to column action on the adjacent cell (R0C1). The macro recording for the test to column settings i need is :
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True
I have attached a sample of what I am currently working with. The actual file has roughly 6000 lines of data, so I would like the macro to go through all lines.
Attached File : LoopingVBA.xlsx
View 3 Replies
View Related
Oct 12, 2009
I am trying to work out a formula. I know it's a simple one but i can not work it out. If i work 9.5 hours or more per day I get an extra $10.50 for food allowance.
For example (see attachment) IF E17 is equal to 9.5 or higher then cell E23 should automatically read $10.50
View 3 Replies
View Related
Jun 3, 2011
I am using Excel 2007, and here is my scenario. I have a spreadsheet with a different sheet for each month of the year. I want certain cells in a month's sheet to mirror or be equal to the same cell in the previous sheet so that if I change the value of a cell in April, the same cell will automatically be changed in May.
An example is that I have a cell with the value of "Comcast". If I want to change that value in April to "Directv", I want it to automatically change in May, too.
Now I know that I can individually program a cell to be equal to a cell in another sheet and it will do exactly what I want. The problem is that I have about 60 cells in each sheet that I want to mirror the same cells in the previous sheet, and I don't want the entire sheet to mirror the previous one. So with 11 months/sheets with cells mirroring the previous sheet, individually programming 660 cells and switching between sheets to do that would take a very long time.
So my question is this. Is there any way to select multiple cells and have them all mirror the same cells on a different sheet without my having to program each cell individually?
View 5 Replies
View Related
Feb 14, 2013
Say I have data in one tab that is cells
A9, B9, C9, D9, etc.
On the next tab, I want the cell in D5 to equal A9, D6 to equal B9, D7 to C9, etc.
I can't seem to lock the cell correctly to do this. If I use =A$9 in cell D5 and then copy down in cell D6 I'll get A$9 also, but if I use $A9, in D6 I'll get $A10.
View 10 Replies
View Related
Apr 24, 2013
I am confusing of using the equal function in vba, I need to have some cells between sheet1 and sheet to be equal but i cannot just put = sign in the sheet as it may be deleted. Can I have it as VBA code? how to do that and where to put this code, in what event?
View 3 Replies
View Related
Jun 7, 2006
I think there's a function that compares two text values and determines if
they are "similar." For instance ABC would be considered a match with ZABC.
Is anyone familiar with this function?
View 10 Replies
View Related
May 13, 2009
Is there any way to make one cell on a separate sheet appear EXACTLY the same, including formatting and values?
As in this for example:
Pieces of Paper (Blue)
I would like to keep the formatting for (Blue) , color and bold, but I have not figured out how to do this.
View 12 Replies
View Related
Jul 17, 2013
I have a spreadsheet with Golf Stats on it, and on my latest import, the lookup stopped working. I am no longer able to look up any of the golfers names.I have attached a portion of the spreadsheet as an example.Lookup Failure.xlsx
Before the data was pulled in 'last name','first name' but is now 'first','last'... so that explains why I have the lookup column in Column A, because it used to convert the last,first format to first,last but that is besides the point. What my real problem is not being able to lookup Column D. As you can see in Cell L4, I tried to test if K4=D4 (Both say Nicolas Colsaerts but Excel does not say they are equal).
View 2 Replies
View Related
Mar 19, 2009
I am trying to hide rows if cetain cells in that row equal zero using a button on the page. Each cell has lookup formulas that will return a value. If coulmn B,F & I have a value of zero I want to hide the row. As of right now I am using the following code but I keep getting the following error message runtime error '13': type mismatch
View 4 Replies
View Related
Jan 15, 2010
Columns L, M and N are always fully populated.
Columns D, E and F may have some missing values, along with H, I and J.
Lets just concentrate on D.
I need to create a formula where I get the anser in C1 This is an average of L only where D has a value.
View 3 Replies
View Related
Aug 6, 2012
Can you make a TextBox equal the value of two Cells without first summing the two cells in the workbook?
TextBox1.value = Range.("B1").Value + Range("B2").Value ???
View 1 Replies
View Related
Nov 21, 2006
How can I select all rows where certain cells are equal to zero?
(i.e. in Column A
1 Bob
2 Joe
3 Smith
4 0
5 0
6 0
I want to select rows 4, 5 & 6.
I've used Go To>Special:Errors to write a macro to hide all rows with errors, but I also want to hide all rows that have zero values
View 9 Replies
View Related
Aug 21, 2008
I have a date on one sheet, and what I thought was the same exact date on another sheet. I want to vlookup off the date, however the values aren't the same. i.e. when I check to see if they = I get a false. They're both formated the same, custom date m/d/yyyy h:mm, and when I ran the check to see if one was text (t=(A1)) I get confirmation that they are both non-text!
View 9 Replies
View Related
Apr 10, 2009
I have a macro to insert a row and I would like it to be triggered by conditions in a IF statement or whatever is best? Macro has a ctrl+shortcut.
eg =IFa10=a12
do nothing
or run macro
View 9 Replies
View Related
Mar 1, 2008
I have 2 text cells (A1, B1) that appear to be the same, however, when I typed the formula "=if(A1=B1,"Yes","No)" in cell C1 the result is NO! (see attachment)
View 4 Replies
View Related
Dec 8, 2013
I wish to be able to add adjacent cells as follows, assuming I have a number in a1. (in B1) =a1-4.
If the answer is zero I would like to change it to -4 and then continue the process for more cells to read 16 ; 12 ; 8 ; 4 ; -4 ; -8 ; -16 etc.
View 6 Replies
View Related
Apr 17, 2012
I'm trying to do a SUM array to count the number of instances where Column C AND Column D are equal to 0, but are NOT BLANK.
I tried the formula:
=SUM((C:C="0")*(D:D="0"))
But the answer comes up 0. However, if I use the formula:
=SUM((C:C=0)*(D:D=0))
The answer includes blank cells (as I assumed it would).
How do I get a SUM array to count only numerical zeros?
PS: If there's a much easier way to do this, I welcome those comments as well, though if it can be done in SUM array I'd love that answer as well.
View 4 Replies
View Related
Mar 27, 2007
Columns E and F can have a 1 or a Zero
If e2, d3, e4 = all 0's or all 1's, count = 3 in col F
If either e2, d3 or e4 is not equal the count is 2 in F
Example
D E F
0
0
0 3
0 1 2
1 1 2
1 1 3
Also, why would this formula work with numbers but not letters?
=IF(B2="x",0,IF(C2="x",1,""))
View 9 Replies
View Related
Dec 27, 2008
In the range A1:Y66, for every cell whose formula returns a 0, I want it to delete that cell and shift the cells up.
Here is the code I tried, but nothing happened:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 25 Then Exit Sub
If Target.Rows > 66 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = 0 Then Target.Delete Shift:=xlUp
End Sub
View 9 Replies
View Related
Aug 25, 2006
Is there a formula or macro that will identify which cells on a spreadsheet containing various dollar values will add up to a predetermined total? I work in Accounts Receivable and sometimes vendors send in payments for several invoices with no instructions as to the application. I want to be able list the available invoice amounts on a spreadsheet, and be able to tell which of the cells add up to the payment amount.
View 2 Replies
View Related
