I have a table in excel which looks something like this:
Product Ref
Date
Description
1
29/05/2013 19:58:50
Product A
[Code] ....
All I'm trying to do is get the only the first dates for each of the related Productsrefs. So the result should look like this:
Product Ref
Date
Description
1
28/05/2013 19:26:48
Product A
[Code] ....
Is there a built in function in excel that can achieve this?
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?
I need to do sorting and filtering the similar records to new worksheet. I need you help to do this job, give some idea or command to use for the following steps.
I have attached the worksheet newtran.xls.
1. I need to find the last column, the column will be increased or decreases. so that we need to find the last column at time of running the macro. Now the value is there till column DD
2. I need to concatenate the column B, D, F to last column find out at 1 step. Insert a new column and place the concatenated value.
3. Entire row will be Sorted based on concatenated value column.
4. Find the similar row on concatenated column and entire row need to be moved to new worksheet.
I have a table of data (total 142 rows). Column contains dates, in the format dd-mmm-yyyy.
I tried to filter using DATE FILTERS->EQUALS and in the custom filter window, I chose EQUALS then picked a date from the date picker icon. The date I picked was 5/4/2009 (this is May 4, 2009, formatted automaticall by excel as m/d/yyyy).
When I clicked OK, nothing showed up despite the fact that there are 6 occurences of May 4, 2009 (formatted as dd-mmm-yyyy in the data table)
So my questions are:
1. Is this due to the formatting?
2. Is there a way to change the date format supplied by the date picker?
I need to create a formula that will calculate the status of a customer based upon an input code and the relevent date.
The attached workbook is very simplified but should help.
Sheet1 contains the date of input (col A) for the client (col B) plus a code in column C to help ascertain the initial status of the a/c.
In Sheet2 (col B) I need a formula or possibly a VBA solution that will create a commission status for clients with 'N' in column C of Sheet1 which is current for 8 weeks after the input date in Sheet1. Thereafter this status then need to show say an 'S' for standard commission.
This result will be linked to the date shown in B2 on Sheet2. Just to make it clear, I only need a letter as the result to trigger off the actual commission calculations.
I have a large order spreadsheet with 3 date related columns. One for customer requested delivery date, one for factory quoted delivery date and one for actual delivery date.
I want to be able to set something up that if it gets near either the requested or quoted delivery date (by say a week), that the cell flags red so it's a visual reminder to check whether the order has been dispatched etc.
I have a list of ID# (Col A) and dates (Col B). The ID # of reference is Col (F) and today's date (Col G). I need to count how many future dates there are in the list (Col B) that are unique and in the future from today's date.
I'm still fairly new to all these formulas and functions which is why I'm not attempting this in VBA.
I've attached an example work book and I would like the result in H2.
Example Workbook.xlsx‎
Trying to do a linkback from another post located here but not having much luck doing it: [URL]
I'm working with 2 date columns and trying to filter a view to only include projects with dates within 3 months of today's date.
I've attached a current working file of the data and the end result i'm hoping to achieve via a macro of some sort.
I've manually got it to work via formula by inserting 2 additional columns (highlighted yellow) which determine if the dates "YES" fall in this 3 month time frame of "" blank if not.
create a macro which does all of this automatically without modifying any columns if this is possible
I have a spreadsheet with 5000+ rows. On one column there is a user number, the user number repeats few times. Therefore, there is a different number of rows for each user.
Also, the first line for each user has a date on a different column.
I am interested in filtering out the users who do not match a certain date range. The issue is that the code I found hides all of the rows without dates and also the rows that are with the range of dates.
I have a userform which writes a date into a cell within a row within a table. The code is
[Code] .....
Everything works fine, the date gets sent to the cell as stated and is all correct. However, I also have vba which runs an advanced filter, which the following code is included in.
[Code] ....
P7 and O7 are the advanced filter criteria. Basically in the form I have a "completion date" which is posted with the first code I posted. So lets say that the date posted is 25/07/14, and I want to have a filter of all the dates which are coming up in the next 7 days to be shown in the Advanced Filter results area of my spreadsheet.
With the second part of code I posted, this results in nothing when using the advance filter. The original posted date in the cell (from the first code) is shown as "25/07/14". So when I run the advanced filter, nothing happens. HOWEVER, if I go to the cell containing "25/07/14", delete the contents and write "25/07/14" manually and then run the advanced filter (second code posted), the date shows up in the results working fine. But I dont understand how me typing it out again makes it work?
In AA1 i input the date based on the lookup.
I want to filter Column 7 (Col G) to what ever is in AA1.
Say i had the date 02/05/2012. My filtered range should only be dated 02/05/2012 but its not filtering to those dates
Code:
With Sheets("UNPRODUCTIVE")
With .Range("AA1")
.Formula = "=IFERROR(LOOKUP(2,1/(TEMPLATE!S12:S40""""),TEMPLATE!B12:B40),"""")"
.Value = .Value
.NumberFormat = "DD/MM/YYYY"
End With
On Error Resume Next
.Range("A1:T1").AutoFilter Field:=7, Criteria1:=.Range("AA1").Value 'filter with only the dates the macro is run for
On Error GoTo 0
End With
I want to be able to filter on an excel spreadsheet by dates between x and y. The values x and y are each in my userform textboxes, tbStart and tbEnd.
For whatever reason there seems to be a format issue. When I run the script I see that the data is filled correctly in the filtering fields but I don't see any results. I simply need to click ok when reviewing the filter and it works. This makes me think that there is something wrong with the format of my tbStart.Value and tbEnd.Value
Here are the variations I have tried:
The values on their own should always be formatted as date in the text box as I am using spin buttons to edit the date
with this code or similar
Me.tbEnd = Format(CDate(Me.tbEnd) + 1, "dd/mm/yyyy")
Me.tbEnd = Format(CDate(Me.tbEnd) - 1, "dd/mm/yyyy")
Version 1:
wsData.ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:= _
">=" & Me.tbStart.Value, Operator:=xlAnd, Criteria2:="=" &
CDate(Me.tbStart.Value), Operator:=xlAnd, Criteria2:="=" &
CDate(Format(Me.tbStart.Value, "dd/mm/yyyy")), Operator:=xlAnd, Criteria2:="
I have a spreadsheet showing a number of jobs with numerous columns for individual bits of data regarding the jobs. Two of these columns show a strat date and an end date for the jobs. What I require is a seperate worksheet that will show the rows that have the formula =Today() be equal to or in between the start and end dates. I've been trying for ages now and have a mental block.
View 2 Replies View RelatedI have a column formatted as a date. I would like to filter out all rows which have a blank date value. I don't see any blank value to select when I try to select auto-filter for that row. Any suggestions?
I would like to filter out the blank values because I found that if I pivot on the data and there are blank values in the field, I can not use the group option for that field.
I need to get dates from one file to another. They can be matched on ID.
So if the ID for a record from FileA matches the ID for a record in FileB, return the date from the date from the same row in FileA to FileB. I would just sort and copy them over, but FileA has more records than FileB. All distinct IDs share the same date.
I have a spreadsheet titled "PMIX" In Columns A:F Column F has Week Ending date. Each week, I need to update this, i.e. delete the data from two weeks ago and then add the new weeks data. What I am thinking is I enter a Week Ending Date in H1, then I would like VBA to delete all rows where it has the data in H1. I am hoping for a delete function as opposed to a clear so I can shift all the rows up.
View 3 Replies View RelatedWe have multiple work orders per opportunity, and each work order has a date. I have a sheet of these work orders which shows the work order #, work order date, and opportunity date. What I want to do is add a column which reports the work order date if it is the earliest instance of a work order for the opportunity (said differently, the first work order for the opportunity). Is there a way to write a formula to report whether the date of the row is the earliest date for a given opportunity, all in the same table?
View 1 Replies View RelatedI have a table in a sheet and I want a formula that will count the unique records. The simplified database is as follows (in a table):
View 6 Replies View RelatedI have a set of data points with date and time shown in the picture. I need to take out all the data point that are at the 30 min interval. Meaning I only want to have the data points hourly only. What can I do to remove the :30 minute datas.
I have tried the find option typing In :30 and getting all the points but then have to replace, which I do not want to do. Need to remove them so they do not show up on the trends ( graphs) I made for them.
This is for excel 2007.
Seems I can't get picture up on the ipad, so the data points look like this
1/1/2012 0:00
1/1/2012 0:30
1/1/2012 1:00
1/1/2012 1:30
And so on for an entire year. Just need to remove the ones with the :30
I am fairly new to macros and I am currently working on a project where I would like to create a 2 buttons which will filter by date and by owner. The spread sheet I have is fairly large and is added to weekly.
I have two objectives that I am trying to reach:
First I need to filter for all past due task items from the Thursday of the week I am working in back and for a specific person (owner) by the finish date
Second I need to filter for upcoming tasks one week out for a specific person (owner). The objective of this is so that on any given day a user can find any upcoming tasks that are due one week out.
If this is not an option. My thought was to create an input box where the user can input the date and the information on the spread sheet will pull all past due items or upcoming tasks. These two items need to pull seperatly not together.
I know I can create a macro by autofiltering by columns however I am trying to avoid having to go in and change the date each week.
Start date is column E
Finish date is column F
Owner is column L
i have to removed the duplciate records with older dates. The date column is made up of date and time.
IDExpiry Date
1202 Mar 2013 12:57:04
1220 Apr 2013 10:49:24
5020 Apr 2013 10:57:06
5021 Oct 2013 07:20:54
5024 Oct 2013 08:41:56
I am working on a pivot table just like the one on the picture here [URL] .......
Ideally I wanted the sums by date to be displayed horizontally and not vertically as shown in the picture, but was unable to do so. Anyways, I want to be able to filter those dates, so I can display data just from an specific date, and then change this date whenever I want and the new data will show up.
I tried doing by the checkbox that appears under the "data" dropdown on top of the column; However, when I uncheck a specific date, it dissapears and I have to add it again if I want to see it. Is there a way to keep the values on the dropdown even after I uncheck them? Or even another way to filter the information? Below is the pic of the dropdown I get when trying to sort the data, But as I said If I uncheck one of the values, it dissapears from the list.
[URL] ........
I have a range of columns i.e. 23 columns (i.e. B through X). Someone can write records in these columns (starting from B21).
Duplicates are considered the rows with similar data in columns 3 and 11. I know about the removeduplicate method and works really well but i want the duplicates not to be removed. Instead another column shall be checked for date of entry (user will entry date in format dd/mm/yyyy). The newest entry will change the value of the cell in column 4 (islatest column)to TRUE while all other records will be FALSE. This will work with the filtering of data on a pivot table on another worksheet.
Book1 and Book2 are workbooks that I have modified in order to protect private information.
Book1 will have 11,000 records (my example Book1 has only 100). I need to rearrange Book1 such that it looks like Book2. Book2 has 20 complete records from Book1 combined into one single row, and my example Book2 has populated 3 rows only (3 rows x 20 records, making 60 records now appear on 3 rows only).
Macro for getting Book1 to Book2? 11,000 records in Book1 will take a lot of hours to transform into Book2 unless a macro can do the job for me.
Book1.xls
Book2.xls
I have a strange problem I cannot solve in that I'm trying to create an auto filter that returns records based on a date variable.
If I set the code to equal the date variable, it returns information. If I set the code to return records AFTER the date, it returns nothing.
Code to return records on the date:
Dim startdate As Date
startdate = Format((Now), "dd/mm/yyyy")
ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:="=" & startdate
This returns data, however if I slightly amend it to get records on or after the date:
Code:
Dim startdate As Date
startdate = Format((Now), "dd/mm/yyyy")
ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:=">=" & startdate
It returns nothing. Is there a syntax problem in my code.
example of a database user form that will allow me to list records in a sheet as well as search for records in a sheet. I know excel has a built in feature for this but it is menu driven and I need something that is button driven and will allow me to resize the form layout. I was not able to figure out how to do that with the built in form.
View 7 Replies View RelatedIn 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.
I have a task to rank the stocks from the highest price to the lowest price. I've made a template and managed to get the ranking right. I used the LARGE() function.
But now the difficulty is to get the 'Stock name' related to the ranked price. I may do it with the MATCH() function. But the problem is there is an equal number 15%.
Can you have a look at the example attached here?
I've tried LOOKUP, HLOOKUP, VLOOKUP, MATCH, SUMIF, ETC... Nothing working correctly. Here's what I need the function/formula to do: Find a # number value in column C, and then whatever text in another column ( range) I specify (like column A) corresponds with that same ROW. For example: VALUE: $40.00
1. Search column C3:C40 to look for $40.00- let's say it finds the value $40.00 in cell C21.
2. In cell A21 there is text "Gena's Sales".
I want the new cell the return a value of "Gena's Sales" because the $40.00 is in the same ROW as "Gena's Sales."
I think the reason I'm having such a problem is because it's numbers and text combined, and also LOOKUP does this "ascending order" thing that screws up the output. I'm getting values with LOOKUP from column A, just not the right ones. I don't need any ascending or descending orders. I just need to find the value in a range, then find the text in another range in the same ROW.
I have a vehicle intake-form in which users have to enter vehicle data.
I also have a huge list containing make/model information.
I would like to be able to use this database in the following way : I want the user to select the make in cell A1 , and then the model in cell B1. It must be possible to choose for example the make "BMW" in cell A1 and then cell B1 should only display the BMW models, and not all others that are present in the list...
I tried to use Data Validation with a list of choices, this works fine for the make (A1) ; but how do I make the list used for Data Validation on B1 dynamically related to the value in cell A1 !