Min Date

Patcheen

Active Member
Joined
Sep 28, 2015
Messages
302
Office Version
365
Platform
Windows
Row Column A Column D
EmployeeDate
1 Carol10/11/2015
2 James11/11/2015
3 Susan12/11/2015
4 Terry13/11/2015
5 Gary14/11/2015
6 Susan15/11/2015
7 Gary16/11/2015
8 Sara17/11/2015
9 Terry18/11/2015
10 Helen19/11/2015

<colgroup><col><col></colgroup><tbody>
</tbody>


As Susan is in twice her minimum date is what i want returned which is number (3) 12/11/15 as it comes before number (8)

As Terry is in twice his minimum date is what i want returned which is number (4) 13/11/15 as it comes before number (9)

As Gary is in twice his minimum date is what i want returned which is number (5) 14/11/15 as it comes before number (7)

A & E Represent the Columns

hope this makes since
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

admiral100

Well-known Member
Joined
Jan 17, 2015
Messages
873
Hi,

Let say:

A1 is the first name (no headers)
E1 is the first date

Ctrl+Shift+Enter NOT just Enter

=MIN(IF(A1:A10=A1,E1:E10))
 

Patcheen

Active Member
Joined
Sep 28, 2015
Messages
302
Office Version
365
Platform
Windows
But they are on a seperat e sheet called Employee sheet and the info is to me given in a different sheet

example =MIN(IF('Employee sheet'!$A$2:$A$10000=B2,'Employee sheet'!$D$2:$D$10000)) as shown in an earlier post (Jeffery Brown posted that formula)
 

admiral100

Well-known Member
Joined
Jan 17, 2015
Messages
873
Sorry don't read the all story ..... Where is B2 ?
If let say B2 on Sheet1 , this formula should work .

=MIN(IF('Employee sheet'!A1:A10=Sheet1!B2,'Employee sheet'!E1:E10))
 

MARZIOTULLIO

Well-known Member
Joined
Aug 22, 2015
Messages
767
Hi

Try

G2=INDEX(E2:E11,MATCH(1,IF(A2:A11=A5,1),0)) Control +shift+enter

Where E2:E11 range date
A2:A11 range name a5=Terry
 

Patcheen

Active Member
Joined
Sep 28, 2015
Messages
302
Office Version
365
Platform
Windows
No joy im pasting the formula on a separate sheet and getting the answer based on my criteria from the employee sheet
 

Patcheen

Active Member
Joined
Sep 28, 2015
Messages
302
Office Version
365
Platform
Windows
Dont forget it has to get the info from employee sheet to be pasted in a separate sheet
 

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,145
The formula posted in post #2 does exactly what you ask.

With your sample data and ranges

=MIN(IF('Employee Sheet'!$A$2:$A$11=B2,'Employee Sheet'!$D$2:$D$11))

Remember, this is a CSE formula.

You getting not joy doesn't help me much. What is happening? What is the formula returning? Are you sure B2 actually matches a value in the Employee sheet range A2:A11?

It may look like Gary in B2 matches Gary in the Employee sheet A6 or A8, but could there be spaces before on after the name?

Have you evaluated the formula to see what is happening?

If Gary is in B2 and the match for the Min value is in A6, in cell C2 (or any free cell) put =B2='Employee Sheet'!A6

What does it return? TRUE or FALSE

If TRUE then they match exactly, if FALSE then the name don't match and you now have to explore why. Maybe extra spaces...
 

admiral100

Well-known Member
Joined
Jan 17, 2015
Messages
873
I still could not understand.


Ther is a sheet called "Employee sheet" where column A contains the employee names and column E contains the dates,
Is that correct?

Regarding B2 , What is the name of the sheet that B2 is there?

On which sheet formuls should be on ?

"... based on my criteria from the employee sheet" - which criteria ?
 

Patcheen

Active Member
Joined
Sep 28, 2015
Messages
302
Office Version
365
Platform
Windows
Employee Sheet
Row Column A Column D
-- Employee Date
1 Carol10/11/2015
2 James11/11/2015
3 Susan12/11/2015
4 Terry13/11/2015
5 Gary14/11/2015
6 Susan15/11/2015
7 Gary16/11/2015
8 Sara17/11/2015
9 Terry18/11/2015
10 Helen19/11/2015


<tbody>
</tbody>
Attendance Sheet

Column B Column D (Example - carol is in B2, James B3) etc etc
EmployeeDate
Carol
James
Susan
Terry
Gary
Susan
Gary
Sara
Terry
Helen

<colgroup><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col></colgroup><tbody>
</tbody>

I want it to return the relevant date associated with the name i only want the date returned not the name


Carol would =10/11/2015
James would =11/11/2015
Susan would =12/11/2015
Terry would =13/11/2015
Gary would = 14/11/2015
Susan would =15/11/2015
Gary would =16/11/2015
Sara would =17/11/2015
Terry would =18/11/2015
Helen would =19/11/2015 The dates will show up in column D of the Attendance sheet
 

Watch MrExcel Video

Forum statistics

Threads
1,102,662
Messages
5,488,169
Members
407,628
Latest member
Faceless Judge

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top