Extracting Data Based On Dates

Aug 5, 2009

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.

View 4 Replies


ADVERTISEMENT

Extracting Records Based On Expiry Dates

Jan 12, 2010

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 2 Replies View Related

Extracting Dates Data From The Same Sheet?

Apr 1, 2014

I'm attaching the test file with one example sheet. Within the same sheet, there is month end dates in column A, and from column B till column E, there are 4 variables (Foreign LT Debt, Local LT Debt,Foeign ST Debt and Local ST Debt). Their respective month end values are already given from column G till Column R. How it is going to be extracted is that against each effective date for that variable (Foreign LT debt, for example), the Rating is given under Rating header. So, for instance, for Foreign LT debt, rating from 25/8/1993 till 4/2/1997 is BB-. So, for all CELLS in column B between these dates, the rating should be BB-. Before that, since there is no value given, I want to put it NA. I have filled the values in column B for demonstration. I want to write a function or perhaps a macro to do that automatically for these 4 variables. There are 50 + sheets with the same layout, so I hope the macro or look up function have not to be written 50+ times.

View 1 Replies View Related

Extracting Data Based On Partial Data In A Cell

Jan 23, 2013

I have a spreadsheet full of data and I need to extract only those lines of data which can be identified by the last three letters in a cell. I am adding an example spreadsheet which highlights the rows I am trying to extract based on the contents in column B but only where column B ends in KY.

Example data extract requirement.xlsx‎

View 5 Replies View Related

Extracting Data To New Worksheet Based On Two Values?

Jul 11, 2014

I currently have a macro that looks at one value and copies the data to another worksheet.

I'd like to modify it to look at two different values and then copy the data. I've tried modifying the VBA code, but can't seem to get it to work. In the "ETF Report (1)" tab, i want it to look at the "ETF Report" and "PO No" values.

The Final Version tab is what i'd like the results to look like.

View 4 Replies View Related

Extracting Data Based On Alphanumeric Pattern?

Jul 3, 2013

I have a column of data that contains text such as 'as per A3', 'B4 requested' and 'as per F6 Mark' (these aren't cell references).

I'm trying to find a formula that will just strip out the 'A3', 'B4' and 'F6' element of the text. I've tried SEARCH with wildcards but it's not working, and can;t use the LEFT RIGHT or MID functions due to the inconsistent data.

View 4 Replies View Related

Extracting Rows Of Data Based On A Cells Text?

Jul 9, 2014

one of my suppliers can only provide me with a basic .xls product list as shown in the attached test sheet.xlsx.

Is it possible to create and run a macro from test sheet.xlsm that will extract data from test sheet.xlsx, specifically from below the heading on row 64 down to and above the heading on row 123 and paste into the (new) worksheet in test sheet.xlsm

This sheet is updated daily and all the heading rows are constantly fluctuating as products are sold and added.

I can do this manually easy enough but if there's any way that this can be automated

View 4 Replies View Related

Excel 2011 :: Extracting Data That Is Text Based (Mac)

Jul 26, 2014

I have a workbook with 2 sheets.

Sheet 1 will act as a summary for sheet 2, showing only the last 5 entries from sheet 2.

Sheet 2 is based on football results of a season which will have a total of 34 league games plus cup games.

Sheet 2 has 4 columns:

Game Number Home Team Result Away Team
1 United 2-0 City
2 Sportive 4-2 Uniao
3 Real 1-0 Ateltico
4 Villa 1-3 City
5 United 2-1 Real
6 Atletico 0-0 Villa

So sheet 1 will show the last 5 results as the season progress's and new data is added.

I have been able to extract column 'GAME NUMBER' using the following formula:

=INDEX('Back end 1'!A:A,MATCH(9.99999999999999E+307,'Back end 1'!A:A)-ROWS('Back end 1'!A2:A$6)+1)

I am struggling to find a way to pull the last 5 data that is text based.

I am using Excel Mac 2011.

View 3 Replies View Related

Extracting Data Based On Drop Down List Choice

Oct 4, 2006

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.

View 4 Replies View Related

Extracting Data Based On Other Columns Data?

Nov 18, 2013

My query is , I require the UCF column for the ID having maximum number (last five digits) of UCF column for the respective year column values

IDUCF Yr
559589876543212610007971212
559589876543212610000333712
559589876543213610004703013
563829876543212630000323912

My answer should be like below (or) having an additional column saying Active/Inactive.
IDUCF Yr

559589876543212610007971212
559589876543213610004703013
563829876543213630004703113
563829876543212630002782312

View 1 Replies View Related

Extracting Data And Posting In Another Sheet Based Upon Macro Selection

Mar 9, 2009

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.

View 8 Replies View Related

Extracting Unique Data Based On Multiple Criteria With Formulas

Sep 27, 2013

I have data and after doing some data manipulation it looks like this

Job #
Part #
Lot #
Total
Grand Total

[Code]..

What I'm trying to do is making a summary sheet that has only the first record with unique values using the Job number, part number, and lot number as the criteria. The grand total in the first record is the total for all of that job,part,lot so i need to bring that value over to the sheet as well. I have been able to do this easily with the duplicate finder, but need a formula to automatically do this.

View 2 Replies View Related

Extracting Data Based On Multiple Criteria - Index Match Array?

Jan 2, 2013

I have sheet full of data containing results of multiple tests on various equipment.The sheet contains many columns of data but below are the specifc criteria i want to use to extract the data. As maintenance is carried out regularly the list is always growing. I want to create a dashboard summary of the "Machines" which i will colour using condition formatting. I will list the machines in the columns and would like the rows below each machine to be populated with the results.

Column 1 Lists the various factories
Column 2 Lists the Machine
Column 3 Lists the Part
Column 4 Lists the result.

Results can either be "ok", "warning", "Alert" only

View 6 Replies View Related

Extracting Data From File In Directory And Extracting Filename

Mar 20, 2014

I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.

In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.

View 4 Replies View Related

Extracting Two Dates

Aug 15, 2009

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.

View 4 Replies View Related

Extracting Dates From Text String?

Aug 20, 2014

If I have a cell that contains a sentence and part of a date, for example "delivery due 01/09", I would like a formula that reads that cell, identifies the 01/09 bit and returns 01/09/2014.

The only trouble is that the date would always be variable, so it's more a case of "delivery due #/#"... if that's even possible.

View 9 Replies View Related

Extracting Dates Of Group Of Occurrences Over Year Period

Jul 11, 2014

I have a hotel room schedule of up to 1,000 rows. Each row is a different room, each column representing a different day of the year. Each cell in a row either has the surname of a guest for that particular day, or the word "Available" to indicate no guest is occupying that room on that day.

analysing each room's occupancy and availability periods over the year. i.e.
- what day(s) a guest occupied and vacated a room,
- what day(s) a another guest then occupied and vacated that room, or
- what day(s) it was then "Available" from

The dates of occupancy are needed in case the guest queries the period.

Parameters
- All 365 days of the year are used - irrespective of holidays/ weekends - the room will either be occupied or available - The day after a guest leaves, it will automatically become available until unless another guest occupies it - The same guest may use a room more than once in a year. This will need to be shown as a unique occurrence, not accumulative

Design
- A1:A1000 is a list of room numbers
- B1:NB1 are all 365 days of the year regardless of weekends/ holidays
- Every cell has either the surname of the guest or the word "Available"
- The results of the analysis can be on another worksheet

Sample I have attached a scaled down sample of what I'm after. Row 1 has the dates starting from 30 April 2013 to 23 May 2013. Room numbers in Column A. C10:D26 are samples for the analysis I'm after. I've randomly chosen Room 100 and Room 500. Room 500 has Mr Mathews staying 4 times.

View 5 Replies View Related

Copying Data Based On Dates And Ages

Feb 19, 2014

I am finally at the end of this project. I need to find a code that will copy any lines that the person has turned 65 in the previous month or that will turn 65 in the current month to the Monthly tab.

Sample Macro jscalem.xlsx

View 3 Replies View Related

Deleting Data Based On Dates Using Macro

Jun 17, 2014

Where is that I have 2 sheets of data, sheet1 contains an ever expanding list of data and I'm trying to create a macro that deletes multiple rows of data from this sheet if the date held within column K is less than the date defined in sheet2.

View 4 Replies View Related

Copy Data From One Sheet To Another, Based Upon Dates

Jul 14, 2008

Copy Data From One Sheet To Another, Based Upon Dates ...

View 9 Replies View Related

Extracting Data From Cells In A Row Based On Other Cells

Apr 30, 2007

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.

View 3 Replies View Related

Add To Dates Based On Criteria & Flag Overdue Dates

Dec 31, 2006

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.

View 9 Replies View Related

Extracting Sublist From List Based On Criteria?

Jun 27, 2012

I have a table such that column A has a list of names, column B shows either "Yes" or "No" depending on if that person's info needs updating. I am trying to find a way to a populate a list (prefereably in column C) that has only the names of people from column a that show a yes in column b.

I know that i could just do =if(B2 = "Yes", A2, "") and then drag the formula down, but that leaves blanks in between the cells. If column A has 10 names and only 3 are Yes then I only want the first 3 cells in column C to be populated with the corresponding names.

I think I need to use a combination of INDEX and MATCH functions but i dont know how to do that with a criteria that falls in another column.

View 14 Replies View Related

Extracting Continuous List Based On Numerical Value?

May 3, 2013

I have attached the worksheet i am currently working on -snapshot of a 2.9 mb file :-).

I have a table i need the information compressed based on the values being greater than '0'. The original table Column A-E runs down 15000 rows but i am only interested in the values that are greater than '0'. I would like to see G3 to contain the first cell from A to contain a value greater than 0, G4 to contain the next sell with a value greater than 0 -- so on and so on.

This method will apply to columns G-K, their may be another formula that i am unfamiliar with that will get this to work.

I have already tried to play around with INDEX and MATCH, i think i am on the correct path but i am not looking at something the right way.

View 5 Replies View Related

Extracting Certain Rows Based On Multiple Criteria?

Oct 29, 2013

There is no where else for me to go. My problem is: I have a table with over 30,000 rows and columns A - W. The first column A contains UNIQUE ID. Column G contains CATEGORY CODE.

Example.png

Since Unique ID (column A) can be listed more than ones, I need to select only those records (rows) that correspond to the following: Category Code (column G) is either 14, 15, 16, 17, 18 and not any other. I hope you can see the attachment, UNIQUE ID = a;does not satisfy my criteria as it does contain Category Code 14, it also contain other codes. The final result from the sample provided would be extracting data for UNIQUE ID = e (since it is the only record that does not contain any values other than 14, 15, 16, 17, 18).

View 6 Replies View Related

Extracting A Continuous List Based On Criteria?

Nov 15, 2013

I want to extract text from column A into column D,E,F based on condition on column B. I am able to do this by IF statement, but the data is not continuous. I want to extract continuous data into D,E,F columns without any gaps.

Condition for Column D: >1
Condition for Column E: >0.5<1
Condition for Column F: <0.5

I am attaching the sample list for reference.

View 7 Replies View Related

Extracting A Continuous List Based On Criteria

Jan 3, 2009

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).

View 3 Replies View Related

Extracting Rows In Dataset Based On IF Criteria?

Jun 15, 2013

I have attached two documents. One is called "Zone Destination" which is a template I designed. The other file is called "Schedule5_4" which gets downloaded from a work server once a week that contains all employees and their shifts for the entire week. What I am able to do so far is extracting the first row using index and match but I don't know how to extract any of the rows that follow. an employee might have several rows for one day based on a lunch or if they are working in multiple zones during their shift.

Zone Destination File -- start tab has the template in place that gets copied over when you create a new tab.
-- employeeroster tab contains the employee roster that i use to match with the schedule5_4 file. i changed the names and also reduced the amount to make it easier to read. i have over 80 employees but for this example, i only made up a handful.

schedule5_4 File -- this file contains all the data that I need to pull from. The criteria that I am using is by employee and date. I'm matching from the employee roster tab and also the date in cell a1 located in the zone destination file.

[URL]

View 5 Replies View Related

Extracting Rows Based On Date Criteria

May 7, 2008

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?

View 5 Replies View Related

Excel 2010 :: Extracting Name From A List Based On Criteria

Nov 15, 2013

I am using Excel 2010 on Windows 7. Here is a link to the worksheet I have a question about : Example.xlsx

The only two relevant sheets here are "TestScores" and "Area 1." What I am trying to do is copy names over from the TestScores sheet to the Area 1 sheet based their test scores. It is important to know that a passing score is anything 85 or above. If you don't pass Phase 1, you move onto Phase 2, and if you don't pass that you move onto Phase 3.

Let me first explain how the TestScores sheet works. Here is a screenshot of the sheet:

I copied in column C, the names, from a roster sheet. Columns A, B, and D all VLOOKUP information from the roster as well. You can ignore columns E & F. Column G is just a test name that is manually inserted. They are all in the same order since this is an example worksheet I am doing the program on before I input the real data. Column H is also manually inserted, with data validation to only allow values 0-100 and a few different relevant terms (MS, EXT ABS, N/E). These next two rows are the formulas in columns I and J, starting in row 3.

I=IF(H3="N/E","N/E",IF(H3="EXT ABS","EXT ABS",IF(H3="MS","MS",IF(H3="","",IF(H3>=85,"N/A (PASS)","Need")))))
J=IF(H3="N/E","N/E",IF(H3="EXT ABS","EXT ABS",IF(H3="MS","MS",IF(I3="","",IF(I3="Need","",IF(I3>=85,"N/A (PASS)","Need"))))))

What these formulas do is the following: If N/E, MS, or EXT ABS is in column H, it copies those over to the next two columns. If someone scores 84 or below, it says "Need" in the next column. If they score 85 or above, it says "N/A (PASS) in the next column(s). The user is meant to write over these to insert the phase 2 and 3 scores.

Here is the "Area 1" spreadsheet I am working on that has the problem.

First off, I created these formulas by comparing them to another post and replacing my ranges and criteria. In column A, it returns all the names of Team Members who passed in Phase 1 (have a score 85+ in "TestScores" column H). The array formula is below and it works great.

{=IFERROR(INDEX(TestScores!$C$3:$C$1500,SMALL(IF(TestScores!$H$3:$H$1500>=85,ROW(TestScores!$C$3:$C$1500)-ROW(TestScores!$C$3)+1),ROWS(A$6:A6))),"")}

In column E I have a similar formula, except it returns the names of those Team Members who have "Need" in column I of "TestScores." This formula also works great. The formula in column D is just a VLOOKUP based on column E and works well, too.

{=IFERROR(INDEX(TestScores!$C$3:$C$1500,SMALL(IF(TestScores!$I$3:$I$1500="Need",ROW(TestScores!$C$3:$C$1500)-ROW(TestScores!$C$3)+1),ROWS(E$6:E6))),"")}

Here is where my problem is. In column F, I want to return all the names of people who passed Phase 2, which means they have a score of 85 or above in column I of the "TestScores" sheet. However, not only is it not returning the right names, I can't even figure out what criteria the names is returns has. The current list, in (mostly, oops) red, is incorrect. The formula, below, is almost the same as the formulas above, with only 1 difference in each case.

=IFERROR(INDEX(TestScores!$C$3:$C$1500,SMALL(IF(TestScores!$I$3:$I$1500>=85,ROW(TestScores!$C$3:$C$1500)-ROW(TestScores!$C$3)+1),ROWS(F$6:F7))),"")

It refers to column I, instead of H, compared to the formula in column A. Its criteria is >=85 instead of ="Need", in comparison to the formula in column E. Nonetheless, it still isn't returning the right names! The other weird thing, is if I replace the ">=85" with "=90" it will return the names of team members who scored 90.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved