Look at the name in Column H2 of the current spreadsheet
Locate that name in Column H on worksheet 'Project Info' for the match with the most recent date in column F also on 'Project Info'
Return the value of corresponding column K on 'Project Info' divided by column I on 'Project Info'
Project Info has a header line so the data starts on line 2.
I don't know how to tell it to find the most recent date.
i have some large reports that i get each day and have to sort into date time order and only keep the newest occurance based on column 2
at the moment i have a vba that converts the list to space delimetd columns, then selects all sorts into date time order, then deletes dupliactes as of column 2 keeping the most recent,
is there a more efficient way just to keep the most recent occurance and removing the rest
example file. so the 2nd row would be what i am left with
I have used the MAX function to populate the most recent date in a series. -EX. the most recent date input into the cells S8, T8, U8 will populate in cell R8. There is a default value of 0-Jan-00 that automatically populates in cell R8 as a result of the MAX Function. Any value that is input into cell S8, T8, U8 will show in cell R8 immediately after being input as a result of the MAX Function.
I need cell R8 to remail blank until the last of cells S8 or T8 or U8 has been input (in no specific order). Once the last cell has been filled; it is only at that time I need cell R8 to show that most recent date input into any of cells S8 or T8 or U8. I can't hide the column, I need cell R8 to remain empty until the last of the three cells- S8, T8, U8 has been filled (the last cell filled will not always contain the largest value or most recent date).
I want to find the most recent date in column D, when the variables in column E are all the same. There are many different variables in column E, and for each group of the same variables (eg. all cells containing "A"), I want to find which is most recently dated (dates are in column D). This is my code so far...
Sub adddiv() Dim ticker As Variant Dim freq As Long Dim csheet As Worksheet Set csheet = Worksheets("Sheet2")
I have database that needs to be updated automatically. Thus far, I have written a macro that will take out and add all necessary data from a file I open, however I would like to write a macro that will find the most recent files and use them to update the database. I would need the macro to 1) Find the folder name that corresponds to the part number in the database, and open that folder, 2) find the most recent file addition to the folder 3) open that file and run the macro that I've already written.
I am trying to find an in-cell formula to find a cell with a specific value and return the value in column A of the same row. Typically this is handled by Lookup or VLookup or HLookup. However, these only find the first occurrence of the value, I even know how to find the last occurrence, but what about in between? In my spreadsheet I have reoccurring values that I need the value in column A of the same row, but I need more than just the first or last but every instance in between. How to do this?
I understand that the function FIND(find_text, within_text, start_num) will find the first occurrence of a stated string. And by adjusting the start_num, you can skip a set number of characters before the search begins.
But, is there a way to find the Last occurrence of said string? In my case the find_text string may occur multiple times in the variable length within_text cell to be searched.
In the above 2 sample strings the output of the Find() function needs to be 26 & 40. Having a cell formula would be desirable, but if it can only be done by VBA that is acceptable, also.
In one column, i get the ID. In the next column of the same row i get the status. My question is to find the number of occurrence of a specific ID with a specific status on another sheet. Attached worksheet sheet "count" has the source with ID at column C and status at column D. My expected result is in another sheet "expected result". I think a macro is needed, but i can't really figured out the way to do this counting.
I would like to find the position of the last occurrence of a character in a string. For example, I have a string with the following:
"c:wwgpeToolbidsTest File.GP$".
I would like to find out the position of the last '' in the string because I want to pull the filename, 'Test File.GP$' into a variable. I would like to avoid writing code to do this. Are there some Excel functions I can use for this?
This section of code I am working on requires me to find an exact text (I tried lookat:=xlWhole, but I can't get it to work right). The problem being is when it searches for Q1, Q10 is an acceptable answer ans so on. I need to get the 4th occurrence, but my research into Nth occurrence stuff is confusing.
The search is in a single column.
With Workbooks("FY12-Q3 Data Tables.xlsx").Sheets("PBA Crosstabs").Columns(1) Set c = .Find(rCell, LookIn:=xlValues, lookat:=xlWhole) Set c = .Find(rCell, After:=c, lookat:=xlWhole) Set c = .Find(rCell, After:=c, lookat:=xlWhole) Set c = .Find(rCell, After:=c, lookat:=xlWhole) End With
I am working with a data sheet that holds a cell containing a number of position entries with each entry separated by a carriage return within the cell. I need to determine the entry associated with Accountant. The formula below determines if the entry Accountant exists and if so, displays 14 characters of information starting after the 12th character. The entry after "Accountant" is variable, but will have a carriage return at the end of the line. How can I identify the placement of the carriage return after the Accountant: entry?
I can use the FIND function to find the first occurrence of ":" and therefore select everything to the right of it, but how do I locate the second occurrence of ":"?
I want to segregate the 7985 Medicare. What if the data varies, i.e. some rows have one occurrence of ":" and others have two?
7900 Personnel: 7970 Bonus. I always want to segregate the last 4-digit account number and description.
I've been working on my original problem and have gotten a little closer to the results I'm looking for but not quite there. Basically, I'm now running into a problem of only finding the 1st occurence in a range of cells when I want to find every occurrence and show the result. Also, the range of cells to look for in my argument has 2 criteria, the start date and the end date. Can anyone tell me if there's a way to search through a range of cells and return every instance of that cell even if it's duplicated?
I've also attached my project to better understand what I'm trying to accomplish which is the use of a Gantt Chart as an employee scheduler as opposed to a task (or project) scheduler.
I am writing a program which would look for look for the word " TOTAL" in sheet1 and then assign several values in sheet 2 based on cells offset of address of "TOTAL". This is repeated and down rows of sheet 2. Everything works except that it is giving repeating the first "TOTAL" address; i.e. it doesn't seem to go to the next met criterion.
I have a file that the user selects and when they enter a value (in this case, a job title) into the input box, my macro looks for the value in that file. If the value is there, a msgbox pops up that lets the user know that the value was found and it then goes to that cell, displaying in the next msgbox the cell address of where the value was found. I already have the code down for this part of the macro.
My problem is what happens if the file has the same value more than once. Ideally, I'd like to display a message box that returns the addresses of both cells with the same value and then prompts the user to select one of these values as the value they are looking for. I am not sure if a msgbox or a msgbox and then an input box are most appropriate for this situation. Once the user does this, the macro continues. The rest of my macro is built on the cell where this value is, so it is crucial that I make sure there is at least one value selected. Any help is much appreciated. I have included a sample worksheet of what this situation might look like.
Here is the code I presently have for this part of my macro.
Sub GetOpenFileName() strFind = InputBox("Please enter the job title you wish to search for:", "Search for job title in this file")
If strFind = vbNullString Then Exit Sub
With Sheets(strSheetsMainCompProfile) If WorksheetFunction. CountIf(Range(Cells(1, 1), Cells(100, 100)), "*" & strFind & "*") = 0 Then MsgBox strFind & " cannot be found on this sheet" Else
I'm getting an export from a CDR. This export contains the date and times people log on and off from a queue. For logging in they dial 511, for logging out they dial 512. They get a voice prompt and type their password. I need to know how much time they daily spend in this queue
At first this looks pretty easy. I just make a sum of all the times they called to 511, then a sum of all time they called to 512 and finally I substract those values and I end up with the correct time spend in the queue.
The problem comes when they call multiple times to 512 without actually logging off. For example, they type the wrong password or simply hang up.
Result is this in the CDR
FROM TO DATE TIME 101 511 23/12 08:34 101 512 23/12 11:58 101 511 23/12 12:34 101 512 23/12 14:45 101 512 23/12 14:47 101 512 23/12 15:00
The actual time spend in the queue is 5 hours and 50 minutes. But Excel calculates this as 35 hours and 22 minutes, because it counts the 512 values no matter what.
How can I make sure that Excel only calculates the values of they are either the last value in the row OR if they are preceded by 511?
I have a 2 groups of column headings with a different month and year in each heading so
1st Group of columns range Columns AJ through AX Column Heading example "Expense Ratio February 2013......next Column over is "Expense Ratio March 2013"
2nd Group of columns range AY though CE Column Heading example "Capital Balance February 2013......next Column over is "Capital Balance March 2013"
Each new month I need to add a new Expense Ratio column after the most recent expense ratio Column. (i.e. Find "Expense Ratio March 2013" and I need to add a column after that with heading "Expense Ratio April 2013"
Same thing for Capital Balance - add a new Capital Balance column after the most recent Capital Balance Column. (i.e. Find "Capital Balance March 2013" and I need to add a column after that for "Expense Ratio April 2013"
Because the ranges keep changing month over month, how do i do this.
I have a range of cells, for this example I will use 2.
Cell E17 = 77/170 Cell E18 = 8/9
Using the following formula: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)))
This bring back an #VALUE! Error as the second part of the formula keeps picking up "/9" however the first part works fine, displaying "170"
Now if I use: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)-1)) It all works. The problem is that I need this to be automatic using the above way means having to add a "-1" to every formula for a cell with only 1 char to be added.
Using the formula: =SUM(RIGHT(E17,FIND("/",E17)-1))+SUM(RIGHT(E18,FIND("/",E18)-1)).....
I have 31 cells (say in A1 thru A31), representing days of the month, that daily number values will be entered. I want to be able to select the value in the most recent day cell, not the most recent updated cell... i.e. the cell in the A1:A31 range closest to the A31 cell.
For example... Say I have values: 2,4,7,12 in A1 thru A4, I want the value in A4, even if I just updated Day 2's cell... because A4, or Day 4, is closer to the end of the month.
Column Q is a vlookup which looks up dates contained in column P within a range called "Query_From_Price_Database" and returns data from column 3 as indicated below:
=VLOOKUP($P4,Query_from_Price_Database,3)
What I'd like to achieve for each time a value is returned as being "0", is for the cell to display the most recent data.
Where there would be zeros, data is displayed from the most recent date actually containing non-zero data. Is this possible with a "not too complicated" formula?
I've tried a few methods, but they only seem to result in "formula too long" errors.
I am familiar with SUMPRODUCT and {SUMIF}, but I am stumped on how to create a formula that can sum (or count) the only the most recent entry for each Name. The Name field is dynamic and constantly updated with a unique reference number, so I don't want totals by Name/Type, but rather only the newest Ref for each Name by Type.
I have one spreadsheet with a list of materials by material number. I have another with all sales activity. How can I pull into the first spreadsheet the date of the latest activity from the second spreadsheet?
I have a workbook to keep track of names and dates of a specific action. There are 10-15 names that recur on the list. The most recent action is added to the bottom of the list. The names are in column I and the date of the action is in column J.
The problem is having to scroll through the list to find the most recent action for each person. Ideally i'd like to have a list of the names in column N and then in column O the most recent date in the list for each name.
I am trying to figure out how I can write formulas that look for trends in the data. I will be entering more data every day. I need to look at the 5, 10, 20 most recent results for each number.
For example, I need to see how many times yes was answered for #3 in the most recent 5,10, 20 entries.
I am sure there is some simple way to do this with a control + f formula, but I don't know how to do it.
Is there a way to list the recent changes to an excel document on the document itself?
For example, I have just 4 or 5 columns in the sheet, but out the the right on the 7th column I'll have a header that says "Recent Changes". Below that for X amount of rows, I would like it to keep a running change list something like this:
B14 was changed from '6.021' to '6.5' by username on 10/21/07 3:45 pm A23 was changed from 'Sally' to 'Billy' by username on 10/21/07 2:30 pm
I thought this up by looking at the way the "Track Changes" functionality works on the Tools menu. That will highlight the cells in blue as they are changed and will also let someone look back through changes to let them accept or deny.
I have a table in a sheet where the rows of data are filled in automatically from other sheets. Each row represents a different week. The data, (lifted from from the relevant worksheet for each row) represents the no. of calls in each week.
Now, I've set up an average to be calculated from the data - it'll do this for every row as long as there is data present. (If the row's corresponding worksheet doesn't have any data the result will be "NA()" - not included in the calculation.)
However, I don't want the average to include the no. in the "most recent" row, as this is "live" data and until the week is over does not represent the data for a full week and therefore skews the average.
So I want the average to exclude the data in the most recent row with a numerical value in it and not a "NA()"
I have data, which has an oil well that has had sections of its pipe perforated.
This has all happened on different days throughout the wells life.
I need an equation that says TRUE if the Perforation is the Most Recent for that well.
But I also need it to say True at different Top and Bottom Depth intervals throughout a specific well.
(for example: the last row in this data. Says TRUE even though there are more recent dates in this well.
This needs to say true because this is the most recent perforation in the top and bottom depth interval 9526 to 9536.
The other most recent perforations happened in shallower intervals, ie. 9456 to 9480.
So I need something in the equation that will say true to the most recent start date, if that top and bottom depth interval has not already been selected as true.
I need to have a column that says True IF the start date is the newest (most recent) for each individual well.
Easy enough.. But. I also need this to say True multiple times at different dates for each well.
For example below. it says true on 7/22/2001 three times and then True again on the last row on 10/14/1992.
It has to say true there because this is the most recent perforation that Top_Depth and Bottom_Depth interval.
WELLNAME TOP_DEPTH BOTTOM_DEPTH START DATE IF most recent Perf
