2-Condition Statement - 2 Different Columns
Oct 28, 2009
Rows:
Each row contains a new client (a new entry)
Columns:Clients first and last name.
Case number
Case type - Case type can be AAA, BBB, CCC, DDD, EEE, (and many more).
Case outcome - Case outcome can be Granted (G) or Denied (D)
A case can be of type AAA, and be Granted.
A case can be of type MMM, and be Denied.
A case can be of type ZZZ, and be Denied.
Etc...
I am trying to keep track of how many cases of each type have been granted and denied. If you look at the file i attached, you will see that the table is a list of clients, with different case types and different outcomes to their cases.
The problem i am having is that i need a way to have a two-condition statement as such:
if case type is "AAA" and case outcome is "G", then add 1 to a "AAA GRANTED" column on another sheet.,....................
View 3 Replies
ADVERTISEMENT
Jun 27, 2006
I have a problem with a SUMIF statement. I think it is =SUMIF(AND(...
I have attached I want to sum in column E lines 255-316 if in cell D 34 and if cell B25 equals GL and cell C255 equals 0001.
Maybe this would be handled better with a sumproduct formula...
View 3 Replies
View Related
Nov 20, 2006
I'm having a problem with negative values. Somehow if the pitchX is -11.1877, let's say, and it is the right value, the code will still perform the 2nd if statement, when it should not. Am I using the wrong data type? Or is there anything I'm missing out?
Dim pitchX As Double
If pitchX > 10000 Then
pitchX = pitchX / 1000
End If
If pitchX <> Range("C8").Value Then
With Range("C8")
.Font.Bold = True
End With
End If
View 6 Replies
View Related
Nov 17, 2009
=IF(SUM(Q4)=0,"",MAX(0,G4-MAX(F4,Q4))/7) - to calculate number of days for year
=IF(SUM(TODAY()>G4),(G4-MAX(F4,Q4))/7,SUM(TODAY()-MAX(F4,Q4))/7) - to calculate number of days year to date
I am using this condition to calculate the length of a service which falls with in a specific review period, eg
Service starts 1 Feb 09 and finishes 30 April 09
The review period is 1 Mar 09 and finishes 31 May 09.
I would need to measure the number of days that fall within the review period, which would be 1 Mar 09 - 30 April 09
At the moment I am missing a condition which identifies whether to subtract from 'service end date' or 'review end date'.
View 6 Replies
View Related
Mar 26, 2009
I am attempting to create a multi condition IF statement for work. Essentially what we have is a column dedicated to the date in which a piece of equipment is supposed to go into service.
What we want to do is make it so that if the current date is 40 days past the scheduled in service date, a status column displays the term "Verify".
Additionally, if the scheduled in service date is 365 days from the current day, a status column should display the term "Future".
If it is any day in between the two, it should display the term "Active"
I have two seperate lines of code, one that covers verify and active and the other that covers future and active but I can't combine the two so that I have one function. The code I compiled is included below, any help would be appreciated.
=IF(D7=0,"Active", IF((TODAY()-40)-D7>0,"Verify","Active"))
=IF(D8=0,"Active", IF((TODAY()+365)-D8<0,"Future","Active"))
(D# corresponds to the respective cell that contains the scheduled ISD)
View 13 Replies
View Related
Sep 25, 2013
I have simplified my problem to its basic parts:
Column A Contains Customer Numbers,
Column B Contains a Balance.
Customers can appear more than once, but the data is sorted by customer number.
In column C, I need to put a 1 when the balance reaches 0 or less, but only for the first instance. Everything else should be 0.
here is how i'd want it to look:
customer
Balance
1st
View 9 Replies
View Related
May 6, 2014
I am running a macro which filters a column to show only records with "#N/A" (ie an error), and copies these to another worksheet. As I am aiming for no errors, there will be occasions when there is no filtered data.
My problem is that, unless I use something like xlCellTypeVisible, when there is no data shown it still copies and pastes all the hidden records. And if I do use it when no filtered data, I currently get an error "No cells were found".
I have tried many iterations of code correction and i think i'm currently blinded by staring at it so long!
My current if statement reads as follows:
[Code] .....
I think I might need to use some sort of On Error Resume Next statement, but I have never used these before. When I tried adding that before the If statement, it just ignored the criteria and tried copying hidden rows - the exact opposite of what I want.
I'd like something that said, if this condition = error, skip over entire if statement and carry on with rest of the sub.
View 5 Replies
View Related
Oct 6, 2011
I am using the formula below to count the occurences of relevant text strings (names) in a cell that can contain many separate strings. It works great. "References!$F$2:$F$34" contains my list of names and the formula returns how many occurences there are in each cell.
What i'd like to do know is work in a date criteria. Each name has an associated relevant date attached to it as does each cell that I am looking for these names in.
So...in english what Id like to do is alter my formula so that when it finds a text string that exists in "References!$F$2:$F$34", it then compares the associated date in "References!$G$2:$G$34" with the date associated with N3 which happens to be O3.
SUMPRODUCT(COUNTIF(N3,"*"&References!$F$2:$F$34&"*"))
View 5 Replies
View Related
Apr 10, 2012
I am trying to pull certain members loads from our global design force spreadsheet and because of the naming convention we used for our structural members the 2nd to last character is unique to what I am looking for. I am trying to get a simple condition statement that will display the load only if the 2nd to last character of the name of the element is satisfied.
For Example, if "T" is what I am looking for then :
EDT4 will be true and give me the value accordingly
D10T1 Will also be true and return me the value
D10B2 Will not be true
I know how to use the Mid() and right() function, but I need to check the 2nd from the right.
View 2 Replies
View Related
Feb 22, 2009
I have created a spreadsheet which creates an average of feedback for trainers in a training company. The form adds up the feedback score into column L of the summary sheet and I have created a summary sheet which I want you use to calculate the average for each trainer.
I have cobbled together an array formula which creates the overallaverage for each trainer based on the named ranges entered via the form.
It looks something like this:
View 10 Replies
View Related
Jul 2, 2009
I have a column with different cities that customers visit (Barcelona, Lisbon etc) and another column with their booking requirements (All inclusive, breakfast only etc)
I'd like to set up a count on the total number of customers who are going to Barcelona and having all inclusive.
I've tried using nested if statements with vlookups and a countif but can't seem to get that working.
View 7 Replies
View Related
Jun 12, 2014
Using VBA or by other means, I want to disable user entry in cells G1 to X1 if the user places a value in A1, similarly if the user places a value in A2 then disable user entry in cells G2 to X2, and so on.
View 7 Replies
View Related
Aug 13, 2012
I'm trying to complete my if condition formula, wherein I've stuck between, how I need to complete, here is the attached one.
The IF formula should take, all 'A,B,C and D columns in result.
Book1.xlsx
View 7 Replies
View Related
Jan 24, 2014
I have three columns - as listed below. I need a script - That compares "result 1" and result 2. If there are data in the "result 1" column then input that into the "City" column. Else input "result 2" data (if any exist), else leave blank.
"Result 1" always has priority over over "Result 2".
City
result 1
result 2
San Fran
San Fran
New York
Tampa
View 4 Replies
View Related
Aug 29, 2007
If you open the attachment.. then you will see that Column A has all the names and Column B has it's Value.. likewise.. Column D has all the names and Column C has it's values...I'm trying to first match the names from Column A to Column D and then see if Column B matches to Column C..
View 8 Replies
View Related
Jul 28, 2009
I have a formula that looks at 5 columns. If any one of these 5 columns meets the specified criteria, it will "Fail" the row. What I want is, when the row is failed, to display the column that was failed. Well I have that part under control using the following formula.
View 2 Replies
View Related
Jun 23, 2013
I'm attempting to put code in that will hide 2 individual columns when a particular cell in another column has no value in it. The cell, however, has a formula in it, and I'm not sure if that is my issue.
Essentially, if the formula in A32 returns a value, I need columns M and P to Hide. If it doesn't return a value, I need them to stay put. The value returning in A32 is a number, but it can be treated as text as it is an ID number and is not needed for any calculation.
Here's how I have it set up now:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A32") "" Then
Columns("M","P").EntireColumn.Hidden = True
Else
Columns("M","P").EntireColumn.Hidden = False
End If
End Sub
It's not working - nothing is happening.
View 5 Replies
View Related
Jun 30, 2014
I was wondering if there is a more beautiful way of this formula
Code:
{=MAX(IF(IF(B14=A1:A12, B1:B12)>IF(B14=A1:A12, C1:C12),IF(B14=A1:A12, B1:B12),IF(B14=A1:A12, C1:C12)))}
A
B
C
1
A
10
10
[Code] ...
Highest B is 80
View 7 Replies
View Related
Jan 17, 2014
15.png
Attached is the file & snap shot for the problem.
TABLE A
[table="width: 500, class: grid"]
[tr]
[td]Catagories state names devices
A4-100HP A ALT
[Code] .....
TABLE B
Catagoriesstate names devices
A4-100HP
A4-101MP
A4-102AP
[Code] ....
In above condition we want to have an automatic filling up of data in table B in column (name & device) but it should match first with state name with category codes of table B & then same with table A in order to avoid any wrong entry because in table A certain states are repeated but codes are unique so we want that filter should match two column of each table before filling data in table B.
View 5 Replies
View Related
Dec 21, 2004
I have a column (A) in sheet1 with these values:
Code
a1 04800128
a2 04800178
a3 04800128
a4 04805555
a5 04800128
And in Sheet2 - Column A and B has these values
Code
a1 04800128
a2 04800128
a3 04805555
a4 04800128
a5 04800128
Status
b1 Y
b2 Y
b3 Y
b4 Y
b5 N
I need to count in sheet1, where the code of sheet1 will be matched with sheet2 code and its status should be equal to "Y" .. I do not want to hard code these values as I have a huge data.
View 4 Replies
View Related
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.
View 9 Replies
View Related
Oct 2, 2013
I need a If statement that can be used on 2 columns.
If there is a 1 in the first column I want to indicate if the 2nd column is 50% or greater with a number 1.
View 3 Replies
View Related
Mar 28, 2008
I am sure this is a very simple questions. I am using the below code to work on the worksheets on a workbook called "MF BANK EXPOSURE SUMMARY.xls" so far the code that I am using is:
Sub Commandbutton2()
Dim iCol As Long
Dim Isheet As Long
Dim Item As Worksheet
Set MFBANK = Workbooks("MF BANK EXPOSURE SUMMARY.xls")
For Each Item In MFBANK.Worksheets
With Item. Range("A1:T65536")
For iCol = .Column + .Columns.Count - 1 To 1 Step -1
If IsEmpty(.Cells(65536, iCol)) And IsEmpty(.Cells(1, iCol)) Then
If .Cells(65536, iCol).End(xlUp).Row = 1 Then .Columns(iCol).Delete
End If
Next iCol
End With
Next Item
End Sub
I would like to extend the code so that it works on the worksheets of two workbooks at the same time the one being the "MF BANK EXPOSURE SUMMARY.xls" and the other being "MF CP EXPOSURE SUMMARY.xls" .
View 3 Replies
View Related
May 9, 2008
I have been trying the whole day to create a formula to solve this but I just can't get it. To make it simple, let's say I have column A filled with different people names, column B filled with Level of Complexity and column C with the Activity (work task) title. This table is fed dynamically, so the number of rows can vary from 2 to 2000.
What I want to do is count how many times a single name appears related to the same complexity, so I can tell how many "simple, medium or complex" tasks each person has in their work queue.
A simple table example would be:
colA colB colC
Dan Complex Build House
Dan Complex Build Shopping center
Dan Simple Take dog to walk
Jose Simple Clean desk
Maria Medium Paint office roof
Then, on another cell I'd like to have a formula automatically telling me that Dan has 2 Complex tasks and 1 Simple, and so on - considering number rows is not a fixed number. What I have so far is this: =SUMPRODUCT((Data!$I$2:$I$457="Dan")*(Data!$L$2:$L$457="Complex"))
But I need to tell a specific last row, and if the data worksheet gets updated with less data (say 450 rows) the formula breaks...
View 3 Replies
View Related
Mar 31, 2014
Column A: T/F
Column B: True/False
Here's what I want in a sentence: If one of the cells in grouped row is "T", column B = True, if not then column B = False.
(row groups are separated by a blank row)
Example)
Col A,Col B
TTRUE
FTRUE
[Code] ....
I came up with following formula:
=IF(MATCH(TRUE,INDEX(ISBLANK(A1:A17),0),0)>MATCH("T",A1:A17,0),TRUE,"")
It checks to see if blank cell is found before "T" but it doesn't look rows above.
inincubus.grouprows-1.xlsx
View 3 Replies
View Related
Nov 21, 2012
Lets say we have data in Column A, B, C and D and no row left blank. In the column A no cell left blank however in B, C, D any one cell only contain a value in that row. If B10 has any value in it then C10 and D10 are left blank (not empty). I want to segregate the data in Column A based on the value in B, C or D. So this one column data ( that is Column A) will split into three column. this segregated data to be put in E, F and G.
Wherever Column B has any value that's greater than zero content from the column A from the same row should copy to the E, Wherever Column C has any value that's greater than zero content from the column A from the same row should copy to the F, Wherever Column C has any value that's greater than zero content from the column A from the same row should copy to the G.
View 4 Replies
View Related
Feb 28, 2014
A
B
C
D
1
$10
$2.95 to $11.00
$4.50 to $19.00
$17.45 to $40.00
Is there a formula that will add column A to the left and right sides of columns B and C to give me an overall answer range in column D?
View 2 Replies
View Related
Jul 4, 2012
Excel 2010. I set up a trial balance as following:
1/31/2000 2/28/2000 3/31/2000 ------- 12/31/2012
Acct # 1
Acct # 2
Acct # 3
Acct # 4
I was able to use pivot table to set up the balance sheet and income statement.
1. now each month is a field which I can choose to add to the report, but the field list is getting too long (and growing). Is there a way I can insert 1 field (for the user to input the exact date) or 2 fields (to input year and month) and the pivot table can pick up the input and refresh either automatically or manually.
2. I looked into the "group" function in pivot table, but how do I group the above data in quarterly and yearly fashion so the user can get those information quickly?
View 1 Replies
View Related
Mar 24, 2014
I am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.
The Cell formula that I am trying to use is as follows:
=IF(YEAR($A$3)<2014,"0",(IF(YEAR($A$3)>2014,H114,(IF(MONTH($A$3)<>2,H114,AA118)))))
A3 contains TODAY()
H114 is the cell being calculated
AA118 is the cell containing the calculated value for the current month.
View 2 Replies
View Related
Aug 13, 2009
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
View 6 Replies
View Related