Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I find the minium date in column B subject to data in Colu
I want to select the minium date in column B (excluding blanks) and subject
to matching a document type listed in Column A. How can I do this A B ABC 28/10/06 ABC 01/01/07 ABC ABC 25/07/07 ADE 15/05/07 ADE ADE 28/02/07 Desired Result (separate worksheet) A B ABC 28/10/06 ADC 28/02/07 Thanks -- Gwyn |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I find the minium date in column B subject to data in Colu
On Oct 26, 7:16 pm, Gwynneth
wrote: I want to select the minium date in column B (excluding blanks) and subject to matching a document type listed in Column A. How can I do this A B ABC 28/10/06 ABC 01/01/07 ABC ABC 25/07/07 ADE 15/05/07 ADE ADE 28/02/07 Desired Result (separate worksheet) A B ABC 28/10/06 ADC 28/02/07 Thanks -- Gwyn 1. Perform an advanced filter on Column A to select unique values and copy data to an empty column (ie C). then move these data to the new worksheet, in column A. 2. Sort data of first worksheet in ascending order based on Column B 3. Go to second sheet and import this formula into cell B1: =VLOOKUP(A1;Sheet1!A:B;2;0) 4. Copy this formula for every row of the new sheet. Vlookup returns only the first value it finds for a matching value. If your data is already sorted in ascending order, it will always return the minimum date. Best |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I find the minium date in column B subject to data in Colu
Create a Pivot Table. For example:
type date abc 12/3/2005 abc 6/7/2007 abc def 10/26/2007 def 10/25/2007 def ghj ghj 12/12/2005 ghj 9/9/2007 will produce: Min of date type Total abc 12/3/2005 def 10/25/2007 ghj 12/12/2005 -- Gary''s Student - gsnu200751 "Gwynneth" wrote: I want to select the minium date in column B (excluding blanks) and subject to matching a document type listed in Column A. How can I do this A B ABC 28/10/06 ABC 01/01/07 ABC ABC 25/07/07 ADE 15/05/07 ADE ADE 28/02/07 Desired Result (separate worksheet) A B ABC 28/10/06 ADC 28/02/07 Thanks -- Gwyn |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I find the minium date in column B subject to data in Colu
=MIN(IF((Sheet1!$A$1:$A$10=A1*(Sheet1!$B$1:$B$10< ""),Sheet1!$B$1:$B$10))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Gwynneth" wrote in message ... I want to select the minium date in column B (excluding blanks) and subject to matching a document type listed in Column A. How can I do this A B ABC 28/10/06 ABC 01/01/07 ABC ABC 25/07/07 ADE 15/05/07 ADE ADE 28/02/07 Desired Result (separate worksheet) A B ABC 28/10/06 ADC 28/02/07 Thanks -- Gwyn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find the most recent date in a column based on other column | Excel Worksheet Functions | |||
Taking a remainder amount from a column and adding it another colu | Excel Discussion (Misc queries) | |||
Need help to find a date (latest date) from a column | Excel Worksheet Functions | |||
How to find avg, min & max for grading subject | Excel Worksheet Functions | |||
When I change the style in column (a) a date, it also changes colu | Excel Discussion (Misc queries) |