Retrieve all the dates of XXX without macro

P

PCLIVE

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
 
R

Rodrigo Ferreira

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?
 
T

T. Valko

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
 
Top