Extract Data Based On Logical Tests
Sep 1, 2006
I have a set of data in two columns, i.e. 1) dates and 2) names. Now I would like to post in the third column the name "x" if there is more than 2 years (or 730 days) between the current and the previus observation for name "x".
More specifically:
I would like the code/ function to always include the first "new" name then: 1) check whether subsequent names are equal, if equal; 2) check whether the difference between each obervation and the first "same" name is more than 730 days. The code should also always consider every observation with more than a 730 days difference as a "new beginning" for that specific name.
I have tried to use IF and OR functions but the function becomes too long and it does not quite solve the whole problem. Problems with my function occurs when the names change in column two. That is, my function is not able to distinguish between dates that belong to the same name and dates that belong to different names, when performing the second logical test.
I have attached a short example of my data. The problem is solved manually in the example file, just to give a better picture of what I am actually trying to do (I have a few thousand rows in my real file).
View 3 Replies
ADVERTISEMENT
Oct 11, 2008
I am working on a homework assignment, the books method was crap so I did my own method. I am almost done except for 1 last thing. I cannot find any function that will allow for 3 logical tests. I have a screen shot here explaining what I need to happen.
View 2 Replies
View Related
Nov 4, 2008
I inserted the following logical tests for cells E5 and E6. I saved the workbook with SE(B) in cell G3.
View 3 Replies
View Related
Feb 12, 2009
How do you do two "if" statements?
I need to have two tests before i get a final result so i need to know the protocal to get two if statements.
View 14 Replies
View Related
Jul 10, 2014
Basically, I'm trying to calculate a pooled z-score from z-scores already stored in different cells.
Each individual z-score are calculated like this :
D1=IF(A1=999,999,((A1-10)/3)).
They are derived from standard scores with a mean of 10 and a SD of 3. I use 999 to mark missing values and this work flawlessly for single z-score cells.That way, if A1 is a missing value (999), Excel returns a value of 999.
Now here's my problem. How do I tell Excel to ignore 999 values if I am pooling multiple z-scores together?
Let's say A1, B1 and C1 are single z-scores. I want D1 to be the pooled z-score result, but I want it to ignore A1,B1 and/or C1 if their value is 999. For instance, if A1=+2.0 , B1 = 999 and C1= -1.50, I want my D1 cell to ignore B1 in the calculation.
I've tried : D1=IF(A1,B1,C1=999,999,(LC5+LI5+IK5)/3)) but it sure doesn't work...
View 7 Replies
View Related
Apr 8, 2009
I am trying to create a logical test with three possible results:
h2 <10.2 "needs improvement"
(if false) h2=10.2 "meets standard"
if false h2>10.2 "exceeds standard"
Basically, I am referring to a cell to determine if it meets, exceeds or does not meed a specific standard. In this case 10.2.
View 4 Replies
View Related
Mar 30, 2014
View attached file. I have a complexity rating that I have developed for a set of projects that I am working on. Management of my company have signed off on this matrix as they believe it is most appropriate. In my projects pipeline tab, I have created the risk and complexity ratings using logical commands.
projects.xlsx‎
View 10 Replies
View Related
Dec 2, 2009
Sometime it’s so hard to explain what it is you are having problems with, specially in the little title. I'm trying to write a formula which should be very simple but isn’t . The formula is in box F10 if B10 has anything in it and its going to have a mixture of text and numbers (first text then numbers) then it’s should equal to the value in the box C7. Here is the formula I wrote that doesn’t work.
View 4 Replies
View Related
Feb 28, 2014
I am looking for VBA code to extract data from each tab into master tab based on data.
I have 3 tabs (inputs)
Tab1 (Dept A-NAME) (RANGE B4:I7)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
A
2/25/2014
Customer 1
10
1
3
ABC
[Code] .....
Tab 2 (Dept B-NAME) (RANGE B4:I11)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
B
2/25/2014
Customer 3
10
1
3
RTY
[Code] .......
Tab 3 (Dept C-NAME) (RANGE B4:I7)
Is it possible to run vba code to get below result in new tab
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
Result
1
A
2/25/2014
Customer 1
10
1
3
ABC
Dept A
[Code] ......
Condition here is date 2/25/2014, is possible when running code message box pops up to ask date, when we give conditional date it extracts those dates.
Excel -2007 & Windows 7
View 7 Replies
View Related
Jun 28, 2010
I have a data sheet with 20 columns and about 300+ rows. In the results sheet there are 3 criterias (from column A, B, H in datasheet) which are in the form of dropdown in cell A1, A2, A3.
I would like to extract the data from the data sheet based on the criterias selected from dropdown list, to results sheet starting from row 6, with only 12 headers out of 20 (non-continuous, e.g. columns A-C, E, G-K etc.)
View 3 Replies
View Related
Sep 25, 2012
I created a master data sheet to enter data manually to analyze. It contains 20 columns, and rows(continue change, because we enter data all of the days). I need to extract data to another sheet, when the user enters data in the master data sheet, but with some conditions, when the column 6 contain the word ASQ, and then extract all of the data, but the new sheet do not contain 20 columns, because the data that contains the word ASQ have only 16 columns.
View 6 Replies
View Related
Sep 28, 2012
I need to create a macro to extract data from sheet1 to sheet2.
The sheet1 contains 15 columns, and the data starts at row 5.
The sheet2 contains 15 columns, and the data starts at row 5.
We enter data all of the days, so we have like 500 rows in the sheet1.
Only 2 conditions to extract data from sheet1 to sheet2:
if the column9 of the sheet1 contains the word "NO" and column11 of the sheet1 contains the word "ASQ".
View 4 Replies
View Related
Nov 30, 2006
I have a raw database that contains various data all in one column and I only want to extract certain data to a new column. All I want is to extract the time, like 14:00, 14:15, 14:30, etc...... from column "A" and populate column "D" if it meets this criteria.
I have tried everything I can think of, very difficult yet it seems to be so simple.
The "date" is really a date format but the "time" is really text.
Here is what the data looks like:
Column A has....
Name1:
Name2:
Name3:
10/16/2006
14:00
14:15
14:30
14:45
15:00
10/27/2006
16:30
16:45
17:00
View 9 Replies
View Related
Jun 23, 2009
method to extract certain data from a column of values.
The attached workbook has two sheets. From the worksheet named "Data" I would like to be able to extract any values >0.00% and then display the corresponding "model" (C:C) on the other sheet called "Results". I need to be able to achieve this for each day in the month and disregard all other values so I am left with a concise list.
I have manually entered the info for the 1st June as an example.
View 6 Replies
View Related
Nov 6, 2007
I'd like to have a formula in Column C which would extract the quantity from either Column A or Column E depending on the value in Column D.
A ---- B ---- C ---- D----------------E-----------F
QTY -UOM----------Tariff #----------QTY 1-----UOM1
46----PCS---------3402.12.5000----94.3--------KG
85----PCS---------3402.12.5000----174.25-----KG
4-----PCS----------3701.10.0030----4.8---------M2
5-----PCS----------3701.10.0030----1-----------M2
15----PCS----------3701.10.0030----1-----------M2
View 9 Replies
View Related
Nov 15, 2009
I have a workbook with data extracted from our ERP (transaction sheet), and a list of cards (card list sheet) which ties the relevant cards to companies and vehicle.
I would like a macro to extract all transactions in the "transaction" sheet into a new file (see "unit" sheet), with the relevant company name, report dates, vehicle number, card no, date, time, polling point name, volume, unit rate. The "Amount" column will be the volume X unit rate.
Vehicle numbers can only extracted from "card list" sheet, and not found in the "transaction" sheet.
these are the details I need:
- all company's transactions details (not sorted in the "transaction" sheet) should be extracted into new individual company's excel file
- headings I need are shown in the "Usage" sheet (company name, vehicle number, etc)
- depending on the number of transactions, there should be a " subtotal" row below the last transaction (as shown) of each vehicle
- with all vehicles extracted from each company, a grand total will be added
- filename should be created with company name
View 9 Replies
View Related
Apr 20, 2014
i am looking macro code to extract data from sheet1 to sheet2 & sheet3, with criteria background color :
1) extract data from sheet1 to sheet2 for data contains/highlighted yellow color background
2) extract data from sheet1 to sheet3 for data contains/highlighted red color background
For detail information, see attached workbook : extract after coloring.xlsx‎
View 3 Replies
View Related
Mar 24, 2014
I have some problem with the extraction, I would like to extract the data to another sheet based on variable conditions but I can not do it. My code in just case working if the all conditions are specified. The target is that if want the code could extract the data based on one condition even, or two or three or more.
View 7 Replies
View Related
Apr 16, 2012
What I am trying to do is extract all the data from a excel database based on criteria. When i specify the site and month I need to pull all the corresponding data and write it to a worksheet. Below is an example. I am not allowed to show all the data it is confidential.
Site Date Operator Month
Chicago 12/3/11 Daniel December
San Jose 4/8/12 Mike April
New York 4/8/12 John April
View 1 Replies
View Related
Dec 5, 2013
I would like to extract competition naps from the competition entries and then remove the string ending "nap" from the data so it can be pasted to another sheet in it's 'clean' format.
on the example sheet column B will always be the first column and the range will extend to either G,H or I (always the max range as there are always 6 to 8 races)
i would like the code to search through these columns of data and find the cell ending in 'nap' then to return this in column i, once done remove 'nap' ending from both the source cell and column i.
once done to then go through all the rows and do similar
the end result would be all naps returned into cell j and all the 'nap' endings through the data range B to I (max) to be removed to leave the horses name only.
in book 3 the first row nap was sommersturm so i have shown the outcome i would like with the nap ending in I1 removed and the horse name returned in J1 again minus 'nap' ending.
the data will always be clean with no leading/trailing/excessive spaces and always be lower case too. sometimes a space is not in between horse name and nap but it's always last 3 characters i want removed still.
View 4 Replies
View Related
Apr 19, 2014
I am looking some macro code to extract data based on background color (yellow color) from sheet1 to sheet2
for detail like this below :
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
[Code] ........
View 5 Replies
View Related
Jun 16, 2014
I'm trying to format a worksheet to show a one year due date with an alert 30 days out so that I can complete the task before the date due. I am using this for a preventative maintenance sheet for respiratory protection equipment that needs to be serviced annually. For instance; if I service a mask today the next service will be in one year. I'd like it to alert me 30 days out. If I could get that alert through outlook that would be fantastic.
View 5 Replies
View Related
Oct 12, 2008
I have a rather frustrating issue that dates a few months back, so I hope to solve it today. The following code tests a value in Column A. If the value is between 1-5 or equal to 11, column B will return the string A. It works.
View 3 Replies
View Related
Aug 9, 2006
I have a form in which a user pastes test names into a certain column of a spreadsheet. I have a listbox (not an ActiveX control) where I want the tests to be displayed. I want to resize the listbox depending on the number of tests pasted in-depending on where the last row is in this column of test names. Is there a way to do this automatically when the user pastes a new test set in the spreadsheet?
View 2 Replies
View Related
Nov 18, 2011
Formula that counts the number of people who are on my course and are taking their tests.
I have 5 classes, Class1, Class2, Class3, Class4 & Class5.
Column A details which class the pupil is in.
Column B details the pupils name.
Column C details the state of the course. This is either a DATE which they started, or "Paid" (they have paid for the course but not started), "Not Paid" (havent paid for course and havent started) or "In Progress" (Payment is in the process of being arranged).
Column D details the state of their final exam. This is either a DATE as to which they have finished and passed the exam, "Not Taken Yet" (Havent taken the exam yet) or "Fail" (They failed the exam).
This is where I am having problems, at the top of my sheet I want to summarise each classes statistics, but I am having problems with the COUNTIF. For example:
What formula can I use to add up the number of people in each class who have started the course?
E.g.
=countif(C10:C100," Is a Date ? ") AND is in Class1 ?
What forumla can I use to add up the number of people in each class who haven't passed the test ?
E.g.
=Countif(D10:D100,"Not Taken Yet" & "Failed") AND is in Class2 ?
I know I need an array to work this out.
And finally to really complicate things how about :
People in each CLASS who have STARTED the course (Date in C) AND haven't passed their test (Col D). I understand this is relying on a three part array whereas the others are 2 parts ?
View 7 Replies
View Related
Oct 9, 2007
In sheet1 I have the following
1. Names (Column A)
2. Date (Column B)
My current list if from A2:B300
I am trying to have a piece of code go through all the records in this list, if any of the dates are within the next 2 weeks (from now()) then copy this record (name, date) and put in sheet2. And loop through all 300 records or possibly additional records.
View 10 Replies
View Related
Feb 20, 2009
I am trying to use an OR in a VBA IF statement. The condtition finds one vale but not the other. Due to the nature of the data, a value is marked either on, off, or both.
I have two seaches in the macro: one looks for on and both, and runs a sub, the other looks for off and both and runs a different sub. However I can't get the both case to run as it should.
If (Cells(i, CurCol).Value = "On") Or (Cells(i, CurCol).Value = "Both") Then
Cells(i, CurCol).Select
View 9 Replies
View Related
May 27, 2008
I would like to extract numbers based on the first 2 digits (22....) of a column along with its next column (on the right) and put them on another sheet.
View 9 Replies
View Related
Jul 27, 2007
I want to know how I can make Excel look at a list of information on one worksheet, extract certain items from the list, and place it on another workshet
For example, the following list would be imported from an inventory program as text and pasted on worksheet 1:
P+green beans
d-2.99
c=2
cd+10258
p+cherry tomatoes
d-3.99
c=1
cd+11204
p+red apples
d-.69/lb
c=3
cd+08902
p+tissue
d-1.99
c-2
cd+20023
How can I have Excel take this information and create a list like the following on worksheet 2? ...
View 9 Replies
View Related
Oct 19, 2007
I have a spreadsheet wherein I've got a list of names, some of which say (See XXX) where XXX is a number. I've extracted the number to column 2. In column 3 I have another reference number. I essentially want to use column 3's numbers only if there is no value in column 2 (i.e. where there's no "See XXX in the list of names). When there is no value in column 2 I have #VALUE! - but I can't seem to use it in a logical statement. How can I do so? Or is there an easier way to pick between 2 cells?
View 4 Replies
View Related