I'm trying to figure out a formula to that will pull the newest data from a list. So say I have a list A2:A20. Each week a new number will be entered into the next cell (in this case A21) making the list longer. Is there a formula or some script that will allow me to do this? I have tried a few different things and cant seem to figure it out
View 4 RepliesI want to sort a column in Excel 2007 which is having dates. Normally, we use oldest to newest or newest to oldest option in sort & filter. But i am unable to find this option in my excel sheet. how can I find this option in Sort & Filter.
Note: I have imported this data column from Access 2007
I am doing a bit of data spring cleaning... I need some VBA that will look at column FT for duplicates. I want it then to keep only the most recent one, so it would delete the others rows completely. Column B contains a date and time. When removing the items it should make a copy of them to the sheet Delete Log.
View 1 Replies View RelatedI have two columns of data. In column A is a date and in B is a dollar amount. I need a formula to average the dollar amount in Column B for the newest 42 entries. Every month I have to add a row at the top of the sheet to enter a date and dollar amount. Eventually there will be more than 42 entries. Each time I add a row to the sheet my formula's range shifts down with it. Here is an example of the data that will eventually go past 42 rows.
A B
112/1/08 $4000
211/1/08 $4250
310/1/08 $5000
49/1/08 $4300
I have attached a sheet that has part of a data list, sheet called (Full Data) what i am trying to do is seperate the data into event locations into individual sheets.
The data ref will be column F which is the different event locations.
I thought the best way to try and do this was to create an if/ match formula using the sheet tab names as the if match, with the event locations in column F.
I have added two sheet tabs so you can see that i require the data for (Ain) to be put into the Ain sheet.
The data list in Full Data sheet will be continuously updated so i will need the range to be around 10,000 entries.
I have an excel spreadsheet that has a list of pathways to different folders (ex. Q:TechnicalY001) in each row. I want a macro that will search each of the folders I list in a range of cells and return the most recent file by the Date in the filename:
Example: Cell A1 contains folder path Q:TechnicalY001
Cell A2 contains folder path Q:TechnicalY002
The macro would return the most recent file in Y001 and dump the filename into cell B1. Then it would return the most recent file in Y002 and dump the filename into B2.
After I have all of the most recent filenames, I want to be able to search each of those files for data in certain cells and then dump that data into the spreadsheet. The cells are always going to be the same in each file.
I am doing a project for work at the moment where by I have to manipulate a report compiled by an external program and do various bits and bobs to it. Trouble is, the other program creates a new version every time it runs the report (i.e filename.1.xls, and the next day filename.2.xls, and so on). Is there any way of searching the folder these files are in to find the newest file and select that one as the one to open and manipulate? There will be no files other than these in the folder.
View 5 Replies View RelatedI have a pretty good size file here where I need to remove duplicated date BUT keep only the Newest Record.
What is Needed is to look at "test Group Identification" and compare it to "Actual Start Time" and keep only the Newest Record and remove any Exact Duplicated data
See example of Data.
Test Status
Test Group
Test Group Identification
Actual Start Time
Want I need
Completed
CMTS
cdn01.bellsrd.va.richmond.comcast.net
09-Jan-2013 15:48:25 EST
Delete
[Code] .....
I want to seach and select the newest date in a column. There are green, red and black fonts in the column. I only want to only seach the cells with black fonts.
View 9 Replies View RelatedIs there a way to pull out the oldest date ((04) Apr 25, 1950) and the newest date ((02) Feb 14, 2050)...?
i have tried the Min function (min(P28:P31)) and the Max function (max(P28:P31)) both resulting in a "0"..
i have also searched this forum as well as others and found formulas that were posted by Aladin Akyurek..but those did not work either..
Sheet12
P28(02) Feb 14, 205029(04) Apr 01, 201330(04) Apr 25, 200931(04) Apr 25, 1950
Spreadsheet FormulasCellFormulaP28=IF(O28=FALSE,"Who Knows?","("&RIGHT(O28,2)&")"&" "&LEFT(O28,3)&" "&LEFT(RIGHT(O28,4),2)&", "&LEFT(RIGHT(O28,8),4))
Excel tables to the web >> Excel Jeanie HTML 4
Im Using Windows Xp With Sp2 With Microsoft Excel 2003
I've got one very strange column of dates. The dates in the column are in different formats and probably the first thing would be convert them into one format.
They are, like...
04/07/2014 04:50
and
08/24/12 09:46:59
... type cells. The next thing would be sort them in some logical way not just by first number.
Column with dates.xlsx
What I am having now and how far I am, is...
They might be sorted in some way by month and date but not by actual year that is left out completely. It is just total mismatch and doesn't make any sense.
I inted to invert stock quotes from newest to oldest but I can't cope with it.
How to invert for example numbers like 2, 10, 3, 15, 1 into the form of 1, 15, 3, 10, 2 ?
I have a table that contains names and dates. I need to find the most recent / latest date in each record. For example:
Names Dates
A 01/02/09
A 05/25/09
B 06/09/09
B 07/30/09
B 01/02/09
C 03/09/09
C 02/28/09
D 12/12/09
The results should be:
A 05/25/09
B 07/30/09
C 03/09/09
D 12/12/09
I have a spreedsheet with 17,000 rows in it. In column D I have ID numbers and in column B I have the dates. There are multiple entries for each ID in column D which gives multiples dates each entry was given in column B. I need a VBA Macro that will identify duplicate entries in Column D and keep only the most recent entry by date in column B.
View 9 Replies View RelatedIn the included workbook I've got a sheet that needs to be updated every time a userform is completed. So I intend to run a macro after the data is entered to find duplicates, and delete the older row.
so, column A must match then column C must match then look in columns AK & AL to find the newest entry to keep. I also need to delete any rows where column A is empty(light Green), no matter what other columns have data.
In my example sheet all the yellow's match for column A but the bright yellow's are the only "keepers", because one has a different number in "C" and the other is the newest.
Also remember that this is just part of a much bigger, multi-sheet book so I will need to I.D. this sheet in the code.
I am trying to make a simple planning sheet.
I have a workbook with 2 sheets.
1.sheet contains Columns : DayDateTimeClassTeacherCovered
2. sheet contains the availability of teachers. so all columns start with a date and the rows contain the name.
A row therefore looks like this.
7/1
Harley
Lidia
Livia
Thais
Salete
Carla
My issue: The 1.sheet has a column Teacher, now in this column I want to have a drop down list. (E2, E3, E4, E5 ... etc..) This list is depended on the date which is in column B.
Essentially I want the list to look up date in its row go to the second sheet and give me all the possible names in it. That is all.
I simply need to "break down" the numbers from the first box. The problem is that the distributional data (A, B, C) are not always the same. One day there might me 10 A's, another day 2, etc...
View 5 Replies View Relatedi wanted to create a macro that will autofilter my starting dates in I to the newest date entered. So ive recorded the macro: ....
View 9 Replies View Related[URL]
You can download the excel from the above link.
This excel includes some text data, in each cell there is an email address along with additional text. I only want to get the email addresses from the entire data.
I have a list of rules on how to group account numbers. An example is below. I have a very long list of full account numbers (no wildcards). There are roughly 75 rules on how to group the over 40,000 accounts. I want to use the vlookup to determine what group each full account number (always 11 characters) would belong to? It seems that the vlookup isn't treating the ?'s as wildcards. I can use the full account number rules with the wildcards in the cell in the sumif function though.
So ultimately I would like to have my list of account numbers like 40000000000 and 40000000001 know that they belong in group 1 based on the rule table below.
Full Account Number
Account
Subaccount
Group
[code]....
if there was a way to automatically add each student to a class list as their information is inputted. What i mean is, after input Sally Student in the main enrollment list she would automatically go to the class list for the class she has chosen.
I have attached a sample workbook with my desired results. Basically what i want to do is make this easier for those in charge to get the class lists from the main list without having to copy and past all the time.
The class lists are in the tabs in the workbook.
how i can select the most recent 10 cells from a list of data to be included in a formula. I have selected it manually, for example F122:F131. This is fine but i don't know how i can put it so that it will update to the 10 most recent results when i input another line of data. So when i put in more data it stays on the previously selected. I want it to move shift down to include the new data but only the 10 most recent.
View 5 Replies View RelatedI have a list of data in columns Q R S and i need to in between each line insert the data from columns D E F underneath and then underneath that the data from G H I
so bascially i have a list at the moment like this
Q
R
S
[Code]....
There is 2 sheets in total , 1 i need to inc 1-1 draw as above and the other sheet to be exc the 1-1 draw . I have attached an example of what i am trying to achieve . can offer as i have had this on excelforum.com with over 150 views and no solution
I have a complicated situation with Excel since I need a formula that can SUM data from the Pull 1 worksheet into the summary Sheet. I attached my sample for your reference because it is a bit complicated therefore I can not explain. It is better if you look at my Summary Sheet, need formula that can retrieve the same information.
Formula Test.xlsx
I've been working on a macro that opens me up to a certain path so I can select a file.
This is the path sofar:
TheFile = "\datawhse
ootLAW81LAWSONprintRBLEVINS2anrvwfins1"
My code takes me there, but I want to go a step further if it is possible.
\datawhse
ootLAW81LAWSONprintRBLEVINS2anrvwfins1THE NEWEST CREATED FOLDER"
A
B
c
d
[Code]....
So the data I put in Is Cells A to D the reast are calculated for me. so for example E2 is looking down column D to see if the number matches the number in A2, If any of them do it will put the name in the corresponding cell in E2 (Or you ca use a different cell if you prefer) so in this case D3=A2 so B3 is the answer also in the cell next to it I need whatever number is the cell next to the answer so in this case C3
I can't seem to find a way to make a data validation list automatically show the first item in the list rather than showing blank.
View 10 Replies View RelatedI'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.
Insert > Name > Create
Name: Fruit
Refers to:
banana,apple,orange
When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.
I need to consolidate these two lists of data into one list.
First set is just a basic list of individuals with their data.
Second set is multiple entries for those same individuals. Each entry shows a subscription to a programme. The final single sheet should have one row per individual which shows all of the programmes they are subscribed to.
See attached, example sheet.
The real list is 3000 IDs, so need some kind of formula to do this.
I am trying to create a drop down list that when selected will display data in the 10 rows below and 4 columns, so a total of 40 cells. I would like the data to be based on the selection in the drop down list.
The data looks like this,
RequirementsQtyResourcesQty
Level 1 Sentinel10Level 1 Sentinel105
Level 2 Sentinel10Level 2 Sentinel167
Level 3 Sentinel10Level 3 Sentinel156
Level 1 Banga20Level 1 Banga401
Level 2 Banga20Level 2 Banga307
Level 4 Banga20Level 4 Banga0
i have 27 different combinations of the above list with up to 10 different rows. I have a label for each one, but i cannot get the rows below my drop down list to display the proper values base don the selection in the drop down list.