Hi!

If you want to extract data that falls within a date range .....

DATE COUNTRY DATA
10/04/05 Syria xxxx
15/05/05 Kuwait xxxx
21/04/05 Yemen xxxx
12/05/05 Oman xxxx
04/04/05 Syria xxxx

Assume that table is in the range A1:C6

Use cells D1:E1 to hold your date range:

D1 = 4/1/2005 (using date format of mm/dd/yyyy)
E1 = 4/30/2005

Formula entered as an array with the key combo of CTRL,SHIFT,ENTER

=INDEX(A$2:A$6,SMALL(IF(($A$2:$A$6=$D$1)*($A$2:$A $6<=$E$1),ROW($1:$5)),ROW(1:1)))

Copy across then down:

10/04/05 Syria xxxx
21/04/05 Yemen xxxx
04/04/05 Syria xxxx
#NUM! #NUM! #NUM!

#NUM! means no more matching data is available.

If you want to extract data that falls within a date range and is specific
to one or more other criteria such as country:

With the same basic setup as above with the addition of the country names in
F1 and F2....

D1 = 4/1/2005 (using date format of mm/dd/yyyy)
E1 = 4/30/2005
F1 = Syria
F2 = Yemen

Array entered:

=INDEX(A$2:A$6,SMALL(IF(($A$2:$A$6=$D$1)*($A$2:$A $6<=$E$1)*($B$2:$B$6=$F$1)+($B$2:$B$6=$F$2),ROW($1 :$5)),ROW(1:1)))

Returns the same table as above.

Biff

"MelbTim" wrote in
message ...

Hello,

I need to extract data from a spreadsheet (eg below) to another
spreadsheet without using the database capabilities in Excel...which
probably means using functions/formulas. Whilst it's easy to get the
data into the second spreadsheet I end up with lots of blank lines
where the unwanted records were located in the first spreadsheet. How
do I get around this?

Eg of data in first spreadsheet:

DATE COUNTRY DATA
10/04/05 Syria xxxx
15/05/05 Kuwait xxxx
21/04/05 Yemen xxxx
12/05/05 Oman xxxx
04/04/05 Syria xxxx

I need to be able to specify a date range and extract particular
countries within those dates. For example between 01/04/05-30/04/05 I
want the data with Yemen and Syria so that the output in the second
spreadsheet looks like:

10/04/05 Syria xxxx
21/04/05 Yemen xxxx
04/04/05 Syria xxxx

Any suggestions would be hugely appreciated.

Regards

Tim


--
MelbTim
------------------------------------------------------------------------
MelbTim's Profile:
http://www.excelforum.com/member.php...o&userid=23847
View this thread: http://www.excelforum.com/showthread...hreadid=374966