How To Find First And Last Date That A Value Appears In Table
Jan 11, 2013
I'm looking to capture the first & last date that a value is entered across a range of dates. See the example below. Note that I will have some instances where a given person has more than one entry line. For reference I showed my desired output at the bottom.
A
B
C
D
E
F
G
H
I
J
[Code]....
Desired result:
start
end
name 1
2/1/2012
8/1/2012
name 2
3/1/2012
12/1/2012
name 3
1/1/2012
12/1/2012
In Column C I need a "yes" or "no" answer to if the country code in B is listed in column A. I have 700 orders from country codes and need to understand which ones I need to charge VAT.
If I have columns A-F and the 1st time a number appears in that row I want to know the column number, so if the first time a number appears in column 'C' I want it to return 3.
I have attached an example spreadsheet that shows my problem. Basically, in column A I have several thousand rows of 3 or 4 letter codes. In column B, I need a formula that will find one particular target code in Col A and then in the adjacent cell in Col B, display the first code to appear in Col A above the target code which matches the list of desired codes.
So the briefly re-state the problem, I need a formula which finds every case of one particular code in Col A (in the example spreadsheet the code is ABCD), and then read back up Col A to find the first value which matches the desired code (from a list of about 5-10 codes), ignoring other values which represent codes that are not on the list, and place this col B adjacent to the target code.
I am trying to use find and replace but the text that i'm searching for appears three times in the cell. I only need to replace the first occurrence in the cell. Alternatively, if there is a way to do this, can the second and third occurrence be changed?
Example:
Cheryl called Louie to advise she would be late for the meeting. Louie responded that he would meet Cheryl at her office. Cheryl confirmed.
I need to change the first 'Cheryl' to a job title and the second and third Cheryl to her initials (CL) so would read:
Manager of Aboriginal Affairs called Louie to advise she would be late for the meeting. Louie responded that he would meet CL at her office. CL confirmed.
Figuring out a SUMIF or SUMIFS formula which will clean up some weekly data. I am envisioning a SUMIF formula which looks at the client name in column A in a table and then it will search through the long list of data for all entries for that specific client on another sheet in column "A", for instance. It needs to take into consideration only the encounters which happened between the dates in the table for that client listed in column B & C. The sum will be the column next to the column with each client's name which has a procedure date in between the date criteria's from the table. I have attached an example to better illustrate.
I need a formula that links to another workbook where it looks in a column for the oldest date which appears more than 10 times. So if the oldest date was 01/01/2006 but it only appeared twice, it would ignore it, but if there was then 02/02/06 which appeared 11 times, it qould quote that date in a cell in the workbook
I have a single work book with 8 sheets (I am using Excel 2010 BTW) and I am trying to find a total of times a word appears across all the sheets in column "C"
I found this formula on another thread. =SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3}&"!C1:C1000"),"="&H3)) with an example. I made the changes that I needed for my purposes
This worked but only after I renamed the sheets to Sheet1, Sheet2, etc.
Is there a way get the same results from the above formula if all the sheets are named after our reps? Example: sheet1 is named Dan, Sheet2 is Nick, etc?
I'd like to search for a specific value like (1) in an array of cells like (A:A). The result needs to be the average number of a different column (B:B) in the same row as the searched value (1). And the resulting number should be rounded up to the nearest 10.
Example below: (Find value = 1 in A:A) ... 1 is found 4 times.
In the example above there are 4 (1's) found. By adding all the values in column (B) of the same row, we get 295+400+354+400 = 1449, dividing that in 4, the average is 362.25, rounding that up to the nearest 10... THE RESULT I'M LOOKING FOR IS: 370
the number of days when there were 0 cases the number of days there was 1 case the number of days when there were 2 cases. As yet there are no days in which there were more than 2 cases but there might be in the future.
I have a list of dates when operations took place in that room. On some days the it was not in use, so those dates don't appear in the list. Some days there was 1 case, so that date appears once in the list. On some days there were two cases, so that date appears twice in the list.
What I've done so far is create a pivot table that contains all the dates, then grouped it by day and counted the number of times there was 1 or 2 cases in a day by hand, then subtracted the total to get the days when there weren't any cases.
Is there a way to get Excel to look down a column for a particular value and where that value appears hide the row it appears in?
We have a series of reports which all have the same template, however we don't want some customers to be able to see some of the products as these are own-label products for which aren't offered to everyone.
I'd like to add this as a macro to a combo-box so that the user can view a report for their customer and then send a copy to the customer with the knowledge they won't see anything we don't want them to...
I have data going in to a small table which has some empty rows as that data is not yet available... My problem is, I need to sort this table in date order but with the date nearest to today's date at the top...
The sort function puts oldest at the top or oldest at the bottom which is no good for what I need...
In the attachment you will see an example of what I am trying to accomplish. What I am trying to do is find VBA code that is either specific to this worksheet or in a macro. When the sheet is opened I enter a date in B2. I then enter data into A7, B7, and C7. What I would like to happen is when the data is entered into A7, B7, and C7 the sheet goes and finds the same date that I entered in B2 and copies that data from A7, B7, and C7 into F7, G7, and H7.
In column A, I have dates; In column b i have security levels. I have made a table called "Security" it contains to columns, a list of security levels and no of years when each security level is required to be reviewed. the table is setup -
d1 e1 Restricted 5 etc
Example of data ie. a1 b1 c1 Restricted 1/06/2012 1/06/2017
What I am looking for is a formula to look up a1 "restricted". then lookup the security table and find "restricted" its value is 5 (years) then add the 5 years to date in b1, but place it in c1.
I have a couple of columns, once edited need to update a table in SQL. This is my code
[Code] .....
However it is not taking UK date formats, it is assuming it is an american format, I get conversion of varchar data type to datetime results in an out-of-range value. What should I add to change the format? The format is in UK on the spreadsheet seemingly....
I have an 'existing results table' as per my attached sample.
I have had help previously from this forum to create lists of 'sold' stock within date ranges (tax year periods) and these are represented as 'sold list' in my attached sample.
I now need to create a list of 'unsold stock' for each annual tax year end date; i.e. populate my table with items that have been created before the end of the date range and that have not been sold by the end of the date range.
Please can someone show me the formula on my attached sample?
Please see my example in red.
I am working with Excel07 however my attached sample is in Excel03 because I couldn't upload an XLSX file.
I'm trying to combine the hours that a employee worked on a single date, with one of multiple time periods that exist for that employee.
I have two sets of data. Set 1 (hours) Employee number, date, hours 12345, 1-2-2014, 6 12345, 1-3-2014, 8 12345, 1-10-2014, 8
Set 2 (periods) Employee number, start date, end date 12345, 1-1-2014, 4-1-2014 12345, 6-1-2014, 1-2-2014
What I'd like to do is to add the start and end date of Set 2 to Set 1 for every row in Set 1
In above example the result should be like this. 12345, 1-2-2014, 6, 1-1-2014, 4-1-2014 12345, 1-2-2014, 8, 1-1-2014, 4-1-2014 12345, 1-10-2014, 8, 6-1-2014, 1-2-2014
I have collected some data on economic factors for different countries. Unfortunately, the dates when I started to calculated my economic factors are different for each country (due to the data available to me).
What I would like Excel to have done is to take the date when I started to measure for e.g. country A (D3 ie 30/06/2007), copy it into column "I" (for country A, it's cell I3) and fill in the following months in the rows below (with always the date of the last day of a month) until it reaches 28th of Feb 2013. Then, it should go up to the next country (country B) take the starting date (D4, ie 31/07/2007), go to the last entry in "I" (ie I71) and paste the date in, fill in the months until 28th of Feb 2013, do the same for country C and so on.
I have started to code a VBA but I am unfortunately a beginner in VBA and totally stuck at the moment. My VBA code does paste in the months but for some reason, it also changes the starting date of the first month.
Moreover, I tried a workaround for the fact that Excel doesnt know when to stop; ie I introduced a "monthdiff" variable which should calculate the number of months between the starting date (which is variable and unique for each country) and the end date (which is always 28th of Feb 2013). At the moment, it only does this for country A.
VB: Set rng = ActiveSheet.Range("I3" & Cells(monthdiff, "I").Address)[SIZE=4][/SIZE]
I have tried to make this dynamic but have been unsuccessful so far.
Spreadsheet with data&code is attached.
VB: Sub Macro1() Dim mainrange As Range Dim rng As Range
I need to create a table of dates which is structured as such:
A B 1/7/08 Yes 7/7/08 no 25/7/08 yes
Column A is date, Column B is a binary selection (yes, no)
I then have a separate sheet with rows corresponding to each date in July 08. I need a function which will look up the date for each row with the above date range and return the value of column B.
for example - if the date is between 1/7/08 & 6/7/08, it would return Yes. If the date is between 7/7 & 25/7/08 it would return No.
The above table is not fixed and can have many dates, with no pattern for the dates. I though of using a nexted IF statement but it would become a bit clunky.
This is a sub that uses the Find method to find a series of dates and copy them to another worksheet. The following error comes up: Object variable or With block variable not set. I have tried using a set command etc. but other errors end up coming up.
Private Sub CommandButton7_Click()
On Error Goto errorHandler Dim startDate As String Dim stopDate As String Dim startRow As Integer Dim stopRow As Integer startDate = InputBox("Enter the Start Date: (mm/dd/yy)") If startDate = "" Then End stopDate = InputBox("Enter the Stop Date: (mm/dd/yy)") If stopDate = "" Then End 'startDate = Format(startDate, "mm/??/yy") 'stopDate = Format(stopDate, "mm/??/yy") startRow = Me.Columns("A").Find(startDate, _ LookIn:=xlValues, lookat:=xlWhole).Row stopRow = Me.Columns("A").Find(stopDate, _ LookIn:=xlValues, lookat:=xlWhole).Row Me. Range("A" & startRow & ":A" & stopRow).Copy _ Destination:=Worksheets("Report").Range("A1") End
errorHandler: MsgBox "There has been an error: " & Error() & Chr(13) _ & "Ending Sub.......Please try again", 48
How do i change this code so i can find the next available row in a table. At the moment it finds the next available row after the table. The table starts row 4 and finishes row 311 however it could grow. There is data in other columns in the Table so i need to stick to searching only column B for the next blank cell.
I have data spread across 76Rx68C - this table is raw data with no logic to placement of values within .
In another sheet i have one row of values.
I need to know if each of these values appear in the data spread. - a simple YES / NO would suffice - i dont need to know where exactly are they appearing in the data table.
