Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retrieve all the dates of XXX without macro
Well you could create a helper column and enter the formula
=IF(A2="XXX_1",B2,"") and then filter out the blank rows on that third column? Dave -- Brevity is the soul of wit. "Rodrigo Ferreira" wrote: I'll try to explain what I want: Sheet1 Name Date XXX_1 01/01/2007 XXX_2 02/01/2007 XXX_3 03/01/2007 XXX_1 04/01/2007 .... Sheet2: I would like to retrieve all the dates of XXX_1 without macro. Is it possible? -- Rodrigo Ferreira |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retrieve all the dates of XXX without macro
Where do you want the results to show? If you want them in another column
of the same row, then try this: =IF(LEFT(A1,3)="XXX",B14) Copy the formula down as needed. I'm assuming the Date field is column B. HTH, Paul "Rodrigo Ferreira" wrote in message ... I'll try to explain what I want: Sheet1 Name Date XXX_1 01/01/2007 XXX_2 02/01/2007 XXX_3 03/01/2007 XXX_1 04/01/2007 ... Sheet2: I would like to retrieve all the dates of XXX_1 without macro. Is it possible? -- Rodrigo Ferreira |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retrieve all the dates of XXX without macro
Oops! I missed part of your question. See Dave F's post for a valid
formula. "PCLIVE" wrote in message ... Where do you want the results to show? If you want them in another column of the same row, then try this: =IF(LEFT(A1,3)="XXX",B14) Copy the formula down as needed. I'm assuming the Date field is column B. HTH, Paul "Rodrigo Ferreira" wrote in message ... I'll try to explain what I want: Sheet1 Name Date XXX_1 01/01/2007 XXX_2 02/01/2007 XXX_3 03/01/2007 XXX_1 04/01/2007 ... Sheet2: I would like to retrieve all the dates of XXX_1 without macro. Is it possible? -- Rodrigo Ferreira |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retrieve all the dates of XXX without macro
I'll try to explain what I want:
Sheet1 Name Date XXX_1 01/01/2007 XXX_2 02/01/2007 XXX_3 03/01/2007 XXX_1 04/01/2007 .... Sheet2: I would like to retrieve all the dates of XXX_1 without macro. Is it possible? -- Rodrigo Ferreira |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retrieve all the dates of XXX without macro
Try this:
Names = A2:A5 Dates = B2:B5 D2 = XXX_1 Enter this formula in E2 as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =IF(ROWS($1:1)<=COUNTIF(A$2:A$5,D$2),SMALL(IF(A$2: A$5=D$2,B$2:B$5),ROWS($1:1)),"") Copy down until you get blanks. Format as DATE. This will return the dates in ascending order which is how they are listed in your example. If the dates are random and you want them returned in the order that they are listed use this formula (array entered): =IF(ROWS($1:1)<=COUNTIF(A$2:A$5,D$2),INDEX(B$2:B$5 ,SMALL(IF(A$2:A$5=D$2,ROW(B$2:B$5)-MIN(ROW(B$2:B$5))+1),ROWS($1:1))),"") Biff "Rodrigo Ferreira" wrote in message ... I'll try to explain what I want: Sheet1 Name Date XXX_1 01/01/2007 XXX_2 02/01/2007 XXX_3 03/01/2007 XXX_1 04/01/2007 ... Sheet2: I would like to retrieve all the dates of XXX_1 without macro. Is it possible? -- Rodrigo Ferreira |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
link to combobox | Excel Discussion (Misc queries) | |||
Macro question | Excel Worksheet Functions | |||
need to retrieve dates from lookup | Excel Discussion (Misc queries) | |||
too many dates macro | Excel Discussion (Misc queries) | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions |