Pull Out Earliest And Latest Dates Associated With Text String In Workbook

Feb 7, 2014

I'm trying to pull out the earliest and latest dates associated with a text string in a workbook.

Min Max dates example.xlsx

i.e. in this example, I want one column to display the earliest date associated with DU 145, and the next column to display the latest.

From what I understand, I need an array formula to do this and have written the following, which works:

=MIN(IF($A$2:$A$6=A2,$B$2:$B$6))
(confirmed with CSE) - and the same for MAX.

Where I'm struggling is that DU 145 may be entered in the workbook as DU145, DU 145 or DU-145 and I need to take all of them into account. I tried:

=MIN(IF($A$2:$A$6="du*145",$B$2:$B$6))

As this works in a COUNTIF formula in the same sheet. But this just returns a date of 00/01/1900. I'm new to array formulae & haven't really worked them out yet. Is there a way to do this? I assume it's something to do with the way I'm entering text as removing the wildcard * makes no difference.

View 4 Replies


ADVERTISEMENT

Finding Earliest And Latest Dates

Jan 4, 2009

I have a workbook with two sheets.

Sheet1 contains details of all of the jobs I do.
Sheet2 is my customer list.

Customers appear only once in sheet2, but may appear mutiple times in sheet1.

In sheet1 I have customer name, lead date, job date et al.
In sheet2 I have customer name, first date, last date et al

I am trying to populate first and last date in sheet2 from the lead date and job date in sheet1.

Sheet1 has a range of 500 jobs

View 9 Replies View Related

Returning Earliest And Latest Date For Unique ID?

Jun 25, 2014

I need an excel function to return the earliest and latest date for a particular/unique ID.

For example: In my sample workbook, the earliest date for all the columns with ID = 1 should return 11/1/2001 and the latest date should be 12/2/2011. If all the dates for a particular ID are the same, it should just return that date. For example, for ID = 2, all dates are 5/5/2010 so the earliest and latest dates should equal 5/5/2010. The earliest and latest dates have to be for each unique ID.

View 5 Replies View Related

Deleting Duplicate Rows Based On Earliest Or Latest Time?

Mar 19, 2014

Below is an example of an Excel sheet I'm working with: [URL]

Basically, I'm trying to delete the duplicate rows by matching ID, Date and Type. If ID, Date and Type are the same, then, I want to only keep the record with the earliest Time in case of Type = In and the latest Time in case of Type = Out.

So, for example, in the case of ID = 1, there are 3 records for In, I only want to keep the one where Time is: 8:01 as this is the earliest. The other 2 records should be deleted.

Similarly, in the case of ID 3, I want to keep the record where Time = 18:05 as this is the later time out of the 2.

Can this be achieved by Conditional Formatting or Macro or VBA?

View 1 Replies View Related

Pulling Earliest And Latest Times From A List Using Lookup / Formula?

Jul 25, 2014

I'm trying to pull the earliest and lasted times out of a list of time stamps. Say I have a column full of clock in and clock out times and wanted to find the first clock in time and the last clock out time (just an example). My lookup_value would be a Store number. I can't post it here due to legality reasons, but I have a column a store numbers that will be my lookup values and I need to find the earliest and lastest time in a list of data on another tab.

View 9 Replies View Related

Retrieving Earliest Time And Latest Time?

Apr 26, 2012

I have something like the following that has a name and a time associated with it for each given name:

A23-Mar-122:27:18 AMB24-Mar-123:38:56 AMD23-Mar-123:38:44 AMA23-Mar-1210:52:01 PMB24-Mar-122:43:26 AMD27-Mar-123:05:07 AMA27-Mar-123:05:16 AM

What I am looking to find is for that that name (i.e., A) what the earliest entry is for the given day (and put in Start Time). The same for end time but to have that the latest time for that given day.

3/23/20123/24/2012Start TimeEnd TimeStart TimeEnd TimeA2:27:18 AM10:52:01 PMB2:43:26 AM3:38:56 AM

View 2 Replies View Related

How To Pull Certain Number From Text String

Jul 16, 2012

I'm trying to pull a specific 10 digit number from a text string. The ID # is always 10 digits however sometimes there are dates in the data that need to be excluded as well as other numbers such as "I-9". The 10 digit number will always start with 1 or 8. Is there a macro or Formula to extract this data? Please find the example dataset below.

Edit: I should specify I want the "100118416" number from the first cell example or the "800011320" number from the 10th example.

NDM Abxx, Kexxxxx 100118416 2-29-12.pdf

VPG I9 Aberxxxxx, Dexxx 100113048 1-1-12.pdf

I-9 Abxxx, Rexx 100119966 4-5-12.pdf

NDM I9 Achxxxn, Paxx 100119008 3-15-2012.pdf

VPG I-9 Acxxxxx, Toxx 100112782 01-01-12.pdf

[code].....

View 9 Replies View Related

Pull Out Text Between 2 Characters In A String

Apr 3, 2009

I'm trying to pull text out of a middle of a long string of data located within one cell. The string is not always the same size and the text I need to pull out is not always in the same spot

For example - in this string

source=google&adgroup=Jimmy Bop (Broad) >>/yes/klingon/GetStarted.aspx >>/finace/success.aspx?

I want to pull out the text "Jimmy Bop (Broad)
It will always be between "adgroup=" and ">>/" (although sometimes the character ">>/" will appear multiple times within the string.

View 9 Replies View Related

Pull Out Text Between 2 Symbols In A String Within A Cell

Feb 2, 2009

I have a column of cells that contain a long string of characters....I need to pull out text from this string and put it in subsequent columns. The symbol I am concerned with is >> For example.

If this string is in the first cell in column A:
>>abcd>>defgh>>ijkl

I would want a formula to return abcd in column b, defgh in column C, and ijkl in column d. >>this is a test>>make it work. in this example I would want "this is a test" returned in column B, and "make it work" ruturned in column C

View 3 Replies View Related

Pull Number From Middle Of Text String

Mar 24, 2009

I would like one formula to remove "5206" from this string. I tried a combination of right/left formulas together, but I could not get it to work.

5032.5206.05816.00040501.0000

View 9 Replies View Related

Pull Specific Word From String Of Text In Cell?

May 16, 2004

I need to pull a specific word from a string of text in a cell and have that word shown in an adjacant cell. For example A1 will contain the text "Smith Sun Alliance Pension Fund" I need B2 to show "Pension". I cannot use any filtering or text to columns as the word Pension can be anywhere within the text in A1 and I have thousands of entries. So I need a function.

View 9 Replies View Related

Pull Latest Date For A Product?

Jan 14, 2014

formula that can pull the latest date for a product, if there are multiple dates attached to the product?

For example, in the attached, I have a Customer and Deal Date list, for Customer A, there are 3 dates, but I only want the latest date: 2/14/2014.

View 4 Replies View Related

Earliest Date From A Column Of Dates

Dec 10, 2008

I have a column of dates listed at the bottom of this post. I am looking to have a formula in a cell that gives me the earliest date from the column. I have tried Min() and Small() with no success. Is it perhaps the "AM" and "PM" that is throwing things off? A small example of the data:

11/21/2008 AM11/09/2008 AM11/09/2008 PM11/09/2008 PM11/10/2008 PM11/11/2008 AM11/11/2008 AM
and it should return 11/09/2008. Thanks in advance any and all.
Ed

View 9 Replies View Related

Lookup For Succeeding Dates After Earliest Date

Dec 8, 2013

On the attached sample file, I used this array formula to lookup fo the

"First purchase date" - {=MIN(IF((Sheet2!A:A=Sheet1!A2),Sheet2!B:B))}

However, for the "Second purchase" and "Third purchase", i'm stuck and can't think on how to return the dates.

View 3 Replies View Related

Pick The Earliest Date From A Selection Of Dates

Jan 24, 2007

There is a speadsheet at work with a column which has a range of dates (from 18/05/2003 - date) which information gets drawn from every Friday. The information needed from the column is pretty simple but lengthy to expalin. For example...

[url]

As you can the dates are in no order...and need to stay that way. The information needed every week is shown here...

[url]

I have got the formula for the number of enteries between each date but strugling with the earliest date within the dates. I can do it manually by auto filtering the column and looking for the date closest to the earliest date but this is long.

The dates change on a weekly basis...just to throw a spanner in the works.

View 9 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

Macro: Combine Dates And Text Into One String

Jun 27, 2008

I have a data sheet which has a series of dates within it. So for eaxample the data sheet will have date of "01/07/2007" I then want to take this data and create the following field:
"June 2008 Pre Data" The problem I have is when I try to join my variables togeother the code crashes (Invalid Procedure call or argument).

The code is as follows ...

View 3 Replies View Related

Sort Latest Dates In Lot Of Data?

Mar 4, 2014

I have a table as below, and I would like to keep only the latest date for each EMP_NO. For example:

001VICE PRESIDENT A062004-01-01
031SENIOR VICE PRESIDENTA042004-01-01
...
....
....

=======================================
[EMP_NO JOB_TITLE DEPTCHANGE_DATE
014 ASSISTANT VICE PRESIDENT A01 2004-01-01
001 MANAGER A062000-03-01
001 MANAGER A062001-06-01
001 VICE PRESIDENT A062004-01-01
076 VICE PRESIDENT A022003-04-23
013 DIRECTOR OF MARKETING A01 2000-06-19
031 FIRST VICE PRESIDENT A042002-01-01
031 FIRST VICE PRESIDENT A042003-01-01
031 SENIOR VICE PRESIDENT A042004-01-01
=======================================

View 5 Replies View Related

Formula To Pull Latest Date From One Column Based On Entry In Adjacent Column

Jun 6, 2014

Any way to construct a formula in excel that will look at a reference in one column and find the latest date from the data in an adjacent column for that specific reference?

Below is an exctract from a much larger sheet of the columns in question.

The result in the last column should be 21/05/2014 for anything with D.O.001 in the second column and 15/05/2014 for anything with D.O.002.

Date Decision agreed
Disposal Order
Latest Decision date for D.O.

06/05/2014
D.O.001

[Code] ........

View 6 Replies View Related

Sort Latest Data Descending For Dates

Oct 4, 2011

Based on the table below, I need two tasks.

1. Sort out the data descending for dates
2. Have a way so that I can only retrieve the latest information.

Meaning if I ask for Pen, I should only get a value of X

Similarly for XYZ, I should only get a value of N

Date A B Types
Value 5 Oct Pen X 5 Oct Pencil Y 5 Oct Pencil H 4 Oct Glue
Q 1 Oct
Pen N 27 Sep
Glue T 2 Sep
XYZ N 2 Sep
XYZ X

View 4 Replies View Related

Automatically Select Most Latest File Name From Similar Names Ending With Dates

May 25, 2014

I need to use the VLOOKUP feature from the most latest files, I am using VLOOKUP in VBA code which is working fine, however the problem is that the files from where I do VLOOKUP are added randomly in a week.

Ex:

There are 2 Files, 1st File where the data would be looked up from the 2nd File, Now the 2nd File has names like this "Offline Data as on 10th May 14", "Offline Data as on 13th May 14", "Offline Data as on 23rd May 14", etc.

Now these files are send by another department, and each time the most recent data has to be searched from these files.. All of these files have to be maintained separately for record purposes.

Hence in the VLOOKUP, Lookup file keeps on changing...so is it possible to make a code in such a way that the code picks up the most recent file for reference, from the above example we would need to pull the data from the most latest file which is "Offline Data as on 23rd May 14" , if any other file is added in that folder more recent than the one mentioned than the code should automatically link it to that file.

I have managed to codify the lookup but due to this filenames being changed always I have to do this manually..as this has to be done at several columns in several sheets its extremely time-consuming.

So, logic code to pick the most recent date which is embedded in the filename...

View 12 Replies View Related

Find If Text Cell Contains A String Of Characters From Array In Different Workbook

Aug 12, 2011

I've scoured the internet for this answer and have not found a formula that works.

I have a list of domain names/URLs in column A of workbook 1:

jhjh.com
hajfh.co.nz
123.123.12.12

I have a list of domain name extensions in workbook #2:

.com
.co.nz
.net

I want to determine if the cells in column A workbook one contain any of the strings in workbook 2 - note that I need to look at the string, not the entire cell in workbook one. If there is a match, true, if not, false for that cell within workbook one.

It's sort of a reverse array lookup with text. It seems that cross workbook and text is what causes the challenge.

View 9 Replies View Related

Pull Out Part Of A String?

Oct 18, 2013

formula that will pull out the number preceding the * 60 in the text below? In this particular case I would be looking to get 5 as the result. That number could be any number but in the example below it happens to be a 5.

Channel Tune Duration in Seconds} (ID) >= (5 * 60))

View 4 Replies View Related

Parse XML String - Pull Out Value In Between

Aug 7, 2012

Basically I have xml strings that are pulled out of a database all in the same format as below.

In between the 2 pharses below I need to pull out the value in between

For this example it is N.

A1<?xml version="1.0" encoding="iso-8859-1" ?>
<StudentPersonal_XML><BirthToThreeServices>
</BirthToThreeServices><EarlyInterventionParticipant>N</EarlyInterventionParticipant>
<LateReferralOther></LateReferralOther><LateReferralReason></LateReferralReason>
<Stu2<?xml version="1.0" encoding="iso-8859-1" ?><StudentPersonal_XML><BirthToThreeServices>
</BirthToThreeServices><EarlyInterventionParticipant>Y</EarlyInterventionParticipant>
<LateReferralOther></LateReferralOther><LateReferralReason></LateReferralReason>

View 3 Replies View Related

Pull Data From One Closed Workbook Into Active Workbook With Command Button

Feb 27, 2014

I've been using the following code to bring in individual cell values from one closed workbook to an active one. I would like to modify this is possible to bring in multiple cells at once and also pull them into a different worksheet in the active workbook. Basically, my command button is on Sheet1 but I'd like the data to pull into a cell on Sheet2.

Private Sub CommandButton1_Click()
With Range("Q9")
.Formula = "='C:Users[Workbook Name.xlsm]Worksheet Name'! N27"
.Value = .Value
End With

View 6 Replies View Related

Pull Data From The Middle Of A String

Jan 13, 2009

I have a spreadsheet of location names that look like this:
UT-04560803-DF3
AZ-57611564-S32
etc...

I'm looking for a way to pull the last four digits out of the middle section of these entries, so that I end up with:
0803
1564
etc...

View 9 Replies View Related

Latest Text File For Import

Oct 10, 2007

I'm trying to automate an Excel macro. I need a way to select the latest .txt file from the Import Data dialog box.

I currently have a "CHDIR" at the beginning of the macro that sets the active directory. When I make the "Import Data" pick I am in the proper place. Now if I can only sort the list of .TXT files by date, select the first file in the list and hit ok to continue I'd be off and running.

View 5 Replies View Related

Move Latest Updated Cell In Sheets In A Workbook Based On Time And Date

Mar 1, 2014

I am having rows of data, that i will be updating from time to time. I want excel to move the latest updated rows, in any column if updated, to move to the top, to easiy know that i updated those records. It should be that when i updated more rows than one, then the first updated cell would be in lower, in order, than the latest updated cells. I do not want any cumbersome vba. I want in formula or in conditional formatting. The row no may be total not limited to some rows.

Because, you naturally would have updated the 200 th record and would have saved. It saved as it is, so when you next opens it it is there, but how can i know that that is the last row of data i edited.

View 5 Replies View Related

Extract Latest Date From Wall Of Text?

Mar 27, 2014

Lets say I have a wall of text that looks like this, all of it in 1 cell.

[Code] .....

What formula do I use to extract the latest date from this string? In this example, the ideal result will be 02/05/2014 .

View 5 Replies View Related

Search For Text :: Return The Last (latest) Date

Nov 11, 2009

I have text in column X and dates in column A...

How can I return the last (latest) date (column A) that "hold" was used (in column X)?

View 9 Replies View Related







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