Extracting Records Based On Expiry Dates
I have created a database at work which collates various information on the sale of products. Part of this information is the date that each particular product is up for review (In DD,MM,YYY) format. Using the internal clock function I have produced a formula that calculates the numeber of days until expiry.
However, using this information I want to be able to run a macro that looks at accounts that have for instance 5 days left until expiary and populate a new worksheet with various information extracted from my 'main' database. For example, search all the porducts that are due to expire tomorrow and then populate my new workseet with data from cells such as
Product 1 = Cell A1, Cell a2, Cell a6, Cell a7,
Prodcut 2 = a1, a2, a6, a7,
Product 39 = a1, a2, a6, a7
View Complete Thread with Replies
Related Forum Messages:
Extracting Data Based On Dates
I have a table with 'Product Rereference', 'Date', and 'Estimated Assets' Columns. I need to run a formula that will extract the number in my 'Estimated Assets' column, based on speicific list of 'Product Reference' numbers that I have filetered, and a specific date - 1/31/ of each year.
I've been trying to create a formula, however I can't seem to work it out.
I have attached a file, which should make things a lot clearer :P I am trying to make the data extract into the table to the right. I have filled in the first few columns manually, to show what I would like to formula to achieve.
Flagging Expiry Dates
I have a list of expiry dates in excel, to save me manually looking through the list to spot up-coming expiry dates, is there a way excel can flag them for me i.e.: giving me 2 months notice of an expiry date? Someone even told me you can get excel to generate an email, not sure if this is true.
Extracting Records Without Duplication
i am having data in ColA & ColB in both Sheet1 & Sheet2
1) some records are both in Sheet1 & Sheet2
2) in Sheet1 itself some records are duplicated
3) in Sheet2 itself some records are duplicated
what i want is to extract the all the records in Sheet1 & Sheet2 in Sheet3
without duplication of records..!
ADVANCED: Extracting Unique Records
At the moment i am having great trouble taking two sets of records, comparing the two based on certain criteria and then extracting any records (rows) that do not appear twice, that is unique records.
So here it is:
I have two lists of sales.
1. our list from our point of sale system with order numbers and other details, entered by the sales agent.
2. the company that provides the products we sell for them sends us a list of these sales back to us from their end so we can see outcomes of these sales, that is the progress of the order, like cancelled, accepted, etc
The two lists need to be compared to see what sales are missing from either one..
so, I could colour the second list RED and the first list GREEN and add the second list underneath the first list (on the same sheet) and then sort by ORDER NUMBER, which would provide a red, green, red, green, red, etc pattern and i could easily identify sales that are unique, but there are so many thousands of sales this manual process is impossible.
IS THERE A WAY TO AUTOMATICALLY COMPARE THESE TWO LISTS BASED ON ORDER NUMBER AND SHOW ONLY THE RECORDS THAT ARE UNIQUE? THAT IS, GET RID OF SALES THAT APPEAR TWICE AND LEAVE ONLY RECORDS THAT I HAVE TO LOOK INTO WHY THEY ARENT ON BOTH SALES SHEETS...
Here is an example of the data i am using:
the numbers do NOT have the .. - or + next to them, thats just there to show you what list each is from.
so as you can see the order numbers with ".." next to them are reconciled, in that they have a partener record and do not need to be shown at all..
and the order numbers with "-" next to them are from our list
and the order numbers with "+" next to them are from THEIR list
i want to end up with a list like this:
Extracting Date Related Records In Pivot Table
I have a master list that is updated in real time by linked worksheets from several people. While my actual report covers 7 managers and 7 different products (“contract types” in this example), I have summarized and simplified the input in the attached file. There are three pertinent dates – the date assigned to the manager (col A), the date completed by the manager (col F), and the date approved by the executive (Col G).
These dates will span month ends and I need to be able to provide monthly reports that provide details on the number of contracts and the dollar value all contracts assigned and completed by the manager during the month (say, February) as well as any contracts assigned in any month which remain uncompleted at the current month-end (e.g. the $7,500 Smith contract assigned on Feb 21st that remains uncompleted and the Feb 26th $200 Jones contract which was uncompleted at Feb 28th, but completed in early March, in this example). Can this be done directly in a Pivot table from the full master list, or will I need to have separate sheets in my workbook where I extract the pertinent records for that month?
Deleting Rows Based On Expiry Date
I have a worksheet. Column A contains employee name, column B contains the date of the first day of the week's holidays they are booked for, i.e. 06/12/08 means they start their holiday for one week starting 06/12/08. That's probably really obvious, sorry.
Once a week I update the vacation list. I want to write a VBA code that looks at the date in Column B and deletes the entire row if it has occured. I'd like to use a value in C1 as the reference so if that cell has 06/15/08 in it then the code would delete every row where the date is prior to 06/15/08.
Extracting Two Dates
how to extract this two dates (sample below) in separate cell and format it as like this: "January 1, 2009". "12/1/08 thru 1/1/09" and another question, is there any way, that i can make it as "JANUARY 1, 2009", it seems upper function is not working with dates.
Extract Certain Records Based From List Based On Date
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.
Filter Records Based On The Value Of A Cell
I have an excel file that gets records from access database. I have a field named "Class" in column C which starts at row 4. I want to filter the records in such a way that only those records with Class equal to whatever value I put in cell B2 will be shown.
A B C
Filter Class: _______
ProjID Name Class
001 Project A 4
002 Project B 4
003 Project C 4
004 Project D 8............
Find Records Based On More Than One Column
I have a very large table that has a one to multiple relationship in a GIS. GIS doesnt support that type of relation ship. I need to find records that are related to the single key but have different values than the first record for that key.
Col 1 col 2 Col 3(key)
ab ab 1
bc bc 1
In this example, only the first row (ab) will be regarded in the GIS. so I need to pull out the second row and put it into another dbf based on the three columns so that I can then put it into the GIS so that the second row is considered and not ignored.
Add To Dates Based On Criteria & Flag Overdue Dates
I have a very large Excel spreadsheet that is generated through an Oracle application at least once a month at work. I would like some code (either a worksheet function or a VBA) to check and report the following. If the frequency in column C is "Monthly" or "Weekly" just go to the next row. If the frequency in column C is "Annual", add 183 days to the date in column B; if it is "Semi-Annual", add 92 days to date in column B; if it is "Quarterly" , add 46 days to the date in column B; if the frequency in column C is "2-Year", add 365 days to the date in column B. Once the check has been made; I need the new total or date that was calculated checked against the date the report is being run (system clock date). If the date or total days is less than today, a new worksheet, titled "Late" needs to contain that row of data. If the date or total days is greater than today's date, just go on to the next row. I've attached an example worksheet.
Extract Unique Records Based On 1 Column
I have a single excel data sheet with 10 rows of header information and then multiple rows and columns of data
I need to extract the 10 rows of header data plus the rows for each unique record in Col A into its own separate worksheet, with the work sheet name being the unique record from Col A
To further add to the challenge, the data in col A may have "/" in so will not comply with excel sheet naming convention so would like an error message to remind me to manually change a sheet name.
I attach an dummy data sheet just to show what I mean!
Extract Records Based On Single Condition
I am working on a data mining project and need to extract records based on a single condition. In the attached workbook you will see 5 columns – 4 Inputs and 1 Output. I need to automatically copy and paste on a separate sheet those records in which the Output value is greater than 970. A Record includes the values of all 4 inputs and the corresponding output value. I did this by hand for this subset of data but the actual number of record is extremely large so I need a macro which will do this for me automatically.
Combine Multiple Records Onto 1 Row Based On Unique Number In Column
I searched and searched and I can't find an easy way to do this without using Access which I am rubbish at. Is there an easy way to do the following in Excel.
I have several thousand records by row each with a unique numerical identifier. The unique identifier is the "Household". Within the household there are sub "Accounts". The sub accounts are truely unique. All the "Accounts" are in the same column.
What I am trying to do is combine the records into one row per "Household" with the accounts listed in successive columns. The maximum number of accounts there may be is 7 but it can be as few as 1. Example:
HH Acct1 Acct2 Acct3 Acct4 Acct5 Acct6 Acct7
1 1234 2345 3456 4567
2 9876 8765 8765
4 5556 4447 3335
Pull Found Records From Table Based On Single Column
I have the following data :-
COL A_______COL B________COL C
COL A_______COL B________COL C
I need to search data from range defined A1.C3 and if any data in that range found in the sheet 2 that having the same records.
Extracting A Continuous List Based On Criteria
Is there a way I can get a continuous list, ie no missing rows, from a much larger list. (New list in separate columns to the R of the list.) I want to extract data from column A, only if the data in col C matches my specified criteria - I want this info to appear as a list with no gaps, ie I can do this easily using "If" but there will be lots of blank rows doing it this way.
Furthermore, as I need two different lists from the same data block, I need to specify whether number (extract these to col N) or text (extract to col O).
Extracting Rows Based On Date Criteria
I have a sheet of data, and i need to extract rows that contains dates containing 5 in them. Eg, 5 Jan, 15 Jan, 25 Jan... 5 May, 15 May, 25 May and copy them into a new sheet. Is there a faster way to do it via VBA rather than manually extracting them?
Extracting Data Based On Drop Down List Choice
I'm trying to do is build a form that will allow me to select from a list of options, that links back to a catologue of data so that when i click on the generate button it will pull the data associated to the item selected from the list into a text box in excel. I have attached the form that I have created.
Extracting Data And Posting In Another Sheet Based Upon Macro Selection
I have written basic macros (probably not the most efficient solution, but its what I know how to do). Up to the present point things are going well. (I tried to attach file, but without success - So I will email the file directly it is pretty easy to figure out) I have option buttons tied to macros that populate a data sheet based upon the input.
This works out quite well and I am able to generate the statistics I desire quite easily. Now comes the second phase, (this is where I am stuck) - when I click the option button "no", I would like this to generate a line item in a separate existing worksheet which lists the item number, tells the discrepancy (as written in the column on the inspection criteria sheet), has a text field for the inspector to briefly explain the exact nature of the discrepancy, and also keeps discrepancies in proper order. Conversly, if "yes" is selected, I would like to be able to check the associated comment page, search the page for that discrepancy number, and if found, delete the line item, then re-oder the page.
Expiry Date With Password
Ive been trying to do this for ages now, but decided i need further help!
Im fairly new to VB but getting the hang of it, however this one gets passed me.
I have made a spreadsheet that works out figures and conversion rates and stuff, however i would like it to expire with the option of a password lets say for example after 30 days, this will effectivly dissable, or close the spreadsheet down until a password is put in, then it will all come back to life.
ive been trying to think of a way round this now for days, but headache after headache i still cant get it to work. The formula doesnt have to be that secure, as the people using it im certain would never even look for it untill its too late!
Calculate Time Before Expiry
I am trying to make an Excel formula to calculate the number of hours and minutes remaining before an entry expires. I am using the formula below
No need for code tags on formulae
where B1= a due time of an item/task and A1= =now()
It should display the hours and minutes until the task is due but it is giving incorrect results.
now = 9:03
task is due = 12:00
result = -21:03
Showing The Expiry Date
I have a file that has start date column. I'd like to show the account's expiration date on another cell but it's going to be dependent on another column.
Column A - customers name
Column B - start date
Column C - rate
Column D - End date
Column E - Follow up Date
Rate column will have the ff inputs :
rate 1 = .09
rate 2 = .11
If rate is .09, then end date will be 2 years from the start date
If rate is .11, then end date will be 3 years from the start date
There are several things I need to happen :
1. Show the customer's individual end dates in Column D
2. I'd like to show in column E the follow up date which is 90 days before the end date
Password Protect W/ Expiry
We are making a complex financial calculator that we will let licensees use for a limited time.
The calculator could be an online php form, however we decided to go with excel for its ease to program the code required.
Since the licensees are allowed to use the calculator only for a short period of time, we want to be able to password protect it with an expiry date.
Meaning that if they want to use it pass their time limit, they would need to re-enter a new password that we would provide them.
Is there such an application made for excel worksheet?
I want to track the dates we begin working with them, and after a certain timeframe (12 months from the start date, for example) I would like a little "flag" (change the font colour, highlight the cell, etc) to automatically appear to indicate when each client has reached this milestone.
Add / Sum Based On Dates
The object is to automate a weekly time sheet based on daily information. The "Daily Time" worksheet contains all 365 days of the year. The work week runs Monday through Sunday. Daily time is posted to the "Daily Time" sheet from handwritten time cards. The Weekly Time is then automatically calculated for each person and each category based on the dates in the "Pay Period From:" field and "To:" field.
For example if 01/01/09 (Monday) is in the "Pay Period From:" field and 01/07/09 (Sunday) is in the "To:" field, then the "Weekly Time" sheet will total the hours posted between those two dates. In this manner there will be a continuous record of daily time in the "Daily Time" worksheet and the "Weekly Time" worksheet will reflect whatever dates are used in the two fields. I realize that because it is simply two dates, it is possible to show any combination of days.
Basically what I need is to be able to add the hours between two dates and obviously the dates will change on a weekly basis. EXAMPLE IS ATTACHED
If/Then Based On Dates
I want to run a SUB based on if a date in a named range ("last_date") is greater than a week and another if it is greater than a month.
Do I do this by subtracting "Last_date" minus current date to see if it is greater than 7 (or 30)? Any help is appreciated. I seem to always have difficulties with VBA/Excel and comparing dates.
Extracting Data From Cells In A Row Based On Other Cells
Please check the attached workbook. Sheet "Data" has some bank account data, and whether the dues are paid or pending.
What I need is, I need to extract only the data of Column A and B into a new worksheet, only if G is in the Pending status.
I don't want to use a macro, nor a pivot, nor autofilter. I need to do it through formulas. I have seen this been done before, but can't figure it out. The max number of pending accounts wont be more than 10, so need to just pull the formula down 10 rows. I have already entered the data in the second sheet that would be the result.
This second sheet is sort of a supervisor sheet, so that whenever they want they can open it to see the number of Pending accounts.
Date Based On Other Dates
In A8 I have a number 1 - 5.
This identifies the weeks in a month 1 - 4 for four week months. The 5 is for a five week accounting month.
In A9 I have a day of the week it could be any day formatted as dddd
In A11 I have the 1st day of the month 01/01/2007 formatted as ddd/yyyy
I need a formulae that will look at the number in A8 i.e. 2
Then look at the day in A9 i.e. Tuesday
Then look at the month in A11 i.e. January
Based on all of that I want it to return the date of the 2nd Tuesday in January 2007 in B11.
In a five week month I need it to return the last Tuesday of the month.
Weeks per month are show in starting in D11 for January.
Sum One Column Based On Dates In Another
I have a cashbook in Excel, the first column of which (column A) has the dates of various transactions. What I want to do is sum another column (column J) where the costs of the transactions fall between two dates in column A eg. between 01/04/07 and 31/10/07. I would like to do this using VBA.
Sum Based On Month Of Corresponding Dates
I have a worksheet of data I need to sum based on a monthly date range criteria onto a separate summary worksheet. Both are in the same workbook. I tried using SUMIF and SUMPRODUCT but can't seem to get the criteria correct when I add in LEFT into the argument for the date criteria "6/" or "06/". Here's where I'm at so far:
My table looks something like:
Log NoShip DateQty
The 6/1/08 entry is intentionaly blank to be filled in as the value becomes known and then the totals would of course need to be updated.
Conditional Format Based On Dates
I have a spreadsheet with due dates in column X. I want to create conditional formatting so that cells turn red when the due date is less than or equal to 30 days away, but turn gray when the due date has passed.
I've accomplished the first step by using the following: =AND($X6
Look Up Customer Information Based On Dates
I'm still having trouble with this formula. I have a sheet, sample attached, that has a list of transactions, with customer name, date, cash-in & cash-out information that is listed down a set of rows. I'll have a tab for each year & the information will be seperated by months.
My other sheet is a monthly report that I will be printing. I have a seperate "section" for each customer & I want excel to fill the information from that customer based on the month. I tried using a index match formula & a vlookup formula in a helper column, but it doesn't bring out the info that I want.
Conditional Formatting Based On Dates
I am trying to use conditional formatting to color a cell based on a date. I have a date in cell "a1" as my base date (no formatting needed). I have dates in cells "a5:a10".
I need those dates (a5:a10) to change color based on the time from "a1".
Less than 1 year = no formatting.
Between 1 year and two years = yellow.
Over two years = red.
I have tried different settings / formulas in the conditional formatting option, but I can't make it work. Do I need to use visual basic to do this?
Sum Values Based Between Two Dates And Another Varible
I want to sum values in column "C" based on if they fall between and equal to two dates, dates in "A" and a varible in "B"
Column "A" a2-a32 dates of each day in Jan 09 31 days.
Column "B" b2-b32 letters either X Y Z randomly through range some cells may have a letter.
Column "C" Dollar value
in cell D1 Startdate in cell D2 Enddate
Formula located in D3 is the sum of all values falling between the date ranges and by one of the letters X. D4 Y. D5 Z.
Conditional Formatting Based Upon Various Dates
I have a spreadsheet I'm trying to make which will have various cells change color based upon the date entered compared to today's date. Here are the details of it, and what I've tried to do so far:
Various safety tickets that employees acquire have a 3 year expiry date on them. What I want to do is be able to enter in the issue date, and then have the conditional formatting color the cell that contains the date based on the following criteria:
Green = More than 6 months remaining
Yellow = Between 6 months and 30 days remaining
Red = 30 days until expiry
Black = Expired (I wanted to eventually add this one, but Excel 2003 was only allowing me to add 3 conditional formatting conditions, so I just went with the 3 colors. I'm getting Excel 2007 later today, so I might be able to add more with that)
Now here is what I have so far:
3 years = 1095 days, 2.5 years = 913 days, 2 years and 11 months = 1065 days
N3 = 913
N4 = 1065
Conditional Formatting on cell C21
Condition 1 - color Green
Condition 2 - color Red
Condition 3 - color Yellow
Assign Dates Based On First Or Last Cells
1. Start Date (Col. C) - assign date to respective position based on first cell that contains value greater than 0 or "". eg. at first cell with 1 in it, looks up to row 4 and assigns date include in that cell in same column.
2. End Date (Col. D) - assigns date based on last cell with value greater than 0 or ""....
Create New List Based On Dates
I am trying to create a list which will summarise information from a dataset depending on two input cells in which dates are inputted by the user. I would really appreciate it if you could have a look at the file I have attached and give me some idea as to what formulae I should be using!
It is important that the position of the output list remains where it is (as ideally I want to draw graphs using the information summarised in the output list.
If your confused now when you look at the file it should make a bit more sense.
Paste A Value Within A Row Based On Column Lookup Containing Dates?
I'm looking to create a small section of VBA code on Excel 2007 that looks for a specific date on a column and then pastes a value on the correct row.
I have weekly dates in columns along the top of my spreadsheet on row 3:
C3 = 07/11/08
D3 = 14/11/08
E3 = 21/11/08
The row further down the spreadsheet that I wish to use is selected by previous code and could be any row number within column B.
I need a snippet of code that searches for a particular date along row 3, and then once it finds the correct column, it pastes the value in the corresponding cell on my active row.
For example, if my active cell/row happens to be on B18, and the date I'm looking for happens to be on L3, I need to paste a value into cell L18.
I imagine there needs to be some form of HLookup, but I've no idea how to implement this in VBA code.
Conditional Formatting More Than 3 Conditions Based On Dates
I want to create a chart than logs the progress of a project at work. Column 1 will have a series of target dates for certain tasks and column 2 will be filled in with the completed dates. I want automatically colour a cell (RC) based on the date given in the cell to the left (RC[-1]) giving a traffic light style warning of an upcoming target date and colour coding the cells when a task is completed on time (or not!). I can work out what the conditions are by creating 2 sets of 3 conditions (1 set if cell (RC) is empty and 1 set if cell (RC) has a date or "N/A" in it).
My conditions are:
1. Turn the cell background colour red (action overdue) if:
2. Turn the cell background colour orange (action required) if:
Formula Is: =IF(AND(RC[-1]"",RC="",RC[-1]
The Next 12 Month End Dates Based On Today's Date
I am trying to project the next 12 month-end dates, based on today's date. I can do that using the EOMONTH function ... see exhibit below ... present month, 1 month out, 2 months out, last month. However, this workbook must be sent to many people and many of those folks will not have EOMONTH functionality because that requires the Analysis Toolpak functions to be added in. How can I accomplish this using standard Excel functions?
Present Month >>> =DATE(YEAR(NOW()),MONTH(NOW()),1)
One Month Out >>> =DATE(YEAR(EOMONTH(NOW(),1)),MONTH(EOMONTH(NOW(),1)),1)
Two Months Out >>> =DATE(YEAR(EOMONTH(NOW(),2)),MONTH(EOMONTH(NOW(),2)),1)
Eleven Months Out >>> =DATE(YEAR(EOMONTH(NOW(),11)),MONTH(EOMONTH(NOW(),11)),1)