Extracting data without using database functions

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

Just to confirm.....

You DO NOT wish to use Autofilter or Advanced Filter.

Is that true?

Ron

OK...For now, I'll assume you really do want to use ONLY Excel functions.

1) I copied your data into Shee1 of a new workbook, beginning in cell B1.
(Note: I changed the date formats to US)

2) I built a pseudo criteria range, beginning in cell I1:

Col_I Col_J Col_K
--------- ------- ---------
StartDate EndDate COUNTRY
04/01/05 04/30/05 Syria
Yemen

3) Col_A is a helper column with the following formula entered in A2 and
copied down:
A2: =IF(OR(B2<$I$2,B2>$J$2,ISERROR(MATCH(C2,$K$2:$K$3,0))),0,MAX($A$1:A1)+1)

The data table looks like this:

SHEET1
Col_A Col_B Col_C Col_D
------- -------- ------- ----------
TEST DATE COUNTRY DATA
1 04/10/05 Syria xxxx
0 05/15/05 Kuwait xxxx
2 04/21/05 Yemen xxxx
0 05/12/05 Oman xxxx
3 04/04/05 Syria xxxx


4) On Sheet2, I copied the column headings from Sheet1
5) Down Col_A, I entered sequential numbers beginning with 1.
6) I entered the following formulas in cells B2, C2, and D2...and copied
them down:

B2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,2,0)
C2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,3,0)
D2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,4,0)

The table in Sheet2, only picks up valid items from Sheet1:

SHEET2
Col_A Col_B Col_C Col_D
------- -------- ------- ----------
TEST DATE COUNTRY DATA
1 04/10/05 Syria xxxx
2 04/21/05 Yemen xxxx
3 04/04/05 Syria xxxx
4 #N/A #N/A #N/A
5 #N/A #N/A #N/A
6 #N/A #N/A #N/A

Note: I left the #N/A items to demonstrate that there is no match for those
items.

Am I on the right track here?

Ron
--
Regards,
Ron

Dear Ron,

You were right...I don't want to use Autofilter or Advanced Filter.

Thank you so much for your suggestion. I really appreciate your effort. I'm going to plug it all in now and see how it goes.

Thank you very much again.

Regards

Tim

School work?

--
Regards,

Peo Sjoblom


"MelbTim" <[email protected]> wrote in
message news:[email protected]...
>
> Dear Ron,
>
> You were right...I don't want to use Autofilter or Advanced Filter.
>
> Thank you so much for your suggestion. I really appreciate your
> effort. I'm going to plug it all in now and see how it goes.
>
> Thank you very much again.
>
> 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
>

School work?

--
Regards,

Peo Sjoblom


"MelbTim" <[email protected]> wrote in
message news:[email protected]...
>
> Dear Ron,
>
> You were right...I don't want to use Autofilter or Advanced Filter.
>
> Thank you so much for your suggestion. I really appreciate your
> effort. I'm going to plug it all in now and see how it goes.
>
> Thank you very much again.
>
> 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
>

School work?

--
Regards,

Peo Sjoblom


"MelbTim" <[email protected]> wrote in
message news:[email protected]...
>
> Dear Ron,
>
> You were right...I don't want to use Autofilter or Advanced Filter.
>
> Thank you so much for your suggestion. I really appreciate your
> effort. I'm going to plug it all in now and see how it goes.
>
> Thank you very much again.
>
> 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
>

School work?

--
Regards,

Peo Sjoblom


"MelbTim" <[email protected]> wrote in
message news:[email protected]...
>
> Dear Ron,
>
> You were right...I don't want to use Autofilter or Advanced Filter.
>
> Thank you so much for your suggestion. I really appreciate your
> effort. I'm going to plug it all in now and see how it goes.
>
> Thank you very much again.
>
> 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
>

School work?

--
Regards,

Peo Sjoblom


"MelbTim" <[email protected]> wrote in
message news:[email protected]...
>
> Dear Ron,
>
> You were right...I don't want to use Autofilter or Advanced Filter.
>
> Thank you so much for your suggestion. I really appreciate your
> effort. I'm going to plug it all in now and see how it goes.
>
> Thank you very much again.
>
> 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
>

School work?

--
Regards,

Peo Sjoblom


"MelbTim" <[email protected]> wrote in
message news:[email protected]...
>
> Dear Ron,
>
> You were right...I don't want to use Autofilter or Advanced Filter.
>
> Thank you so much for your suggestion. I really appreciate your
> effort. I'm going to plug it all in now and see how it goes.
>
> Thank you very much again.
>
> 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
>

School work?

--
Regards,

Peo Sjoblom


"MelbTim" <[email protected]> wrote in
message news:[email protected]...
>
> Dear Ron,
>
> You were right...I don't want to use Autofilter or Advanced Filter.
>
> Thank you so much for your suggestion. I really appreciate your
> effort. I'm going to plug it all in now and see how it goes.
>
> Thank you very much again.
>
> 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
>

OK...For now, I'll assume you really do want to use ONLY Excel functions.

1) I copied your data into Shee1 of a new workbook, beginning in cell B1.
(Note: I changed the date formats to US)

2) I built a pseudo criteria range, beginning in cell I1:

Col_I Col_J Col_K
--------- ------- ---------
StartDate EndDate COUNTRY
04/01/05 04/30/05 Syria
Yemen

3) Col_A is a helper column with the following formula entered in A2 and
copied down:
A2: =IF(OR(B2<$I$2,B2>$J$2,ISERROR(MATCH(C2,$K$2:$K$3,0))),0,MAX($A$1:A1)+1)

The data table looks like this:

SHEET1
Col_A Col_B Col_C Col_D
------- -------- ------- ----------
TEST DATE COUNTRY DATA
1 04/10/05 Syria xxxx
0 05/15/05 Kuwait xxxx
2 04/21/05 Yemen xxxx
0 05/12/05 Oman xxxx
3 04/04/05 Syria xxxx


4) On Sheet2, I copied the column headings from Sheet1
5) Down Col_A, I entered sequential numbers beginning with 1.
6) I entered the following formulas in cells B2, C2, and D2...and copied
them down:

B2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,2,0)
C2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,3,0)
D2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,4,0)

The table in Sheet2, only picks up valid items from Sheet1:

SHEET2
Col_A Col_B Col_C Col_D
------- -------- ------- ----------
TEST DATE COUNTRY DATA
1 04/10/05 Syria xxxx
2 04/21/05 Yemen xxxx
3 04/04/05 Syria xxxx
4 #N/A #N/A #N/A
5 #N/A #N/A #N/A
6 #N/A #N/A #N/A

Note: I left the #N/A items to demonstrate that there is no match for those
items.

Am I on the right track here?

Ron
--
Regards,
Ron

OK...For now, I'll assume you really do want to use ONLY Excel functions.

1) I copied your data into Shee1 of a new workbook, beginning in cell B1.
(Note: I changed the date formats to US)

2) I built a pseudo criteria range, beginning in cell I1:

Col_I Col_J Col_K
--------- ------- ---------
StartDate EndDate COUNTRY
04/01/05 04/30/05 Syria
Yemen

3) Col_A is a helper column with the following formula entered in A2 and
copied down:
A2: =IF(OR(B2<$I$2,B2>$J$2,ISERROR(MATCH(C2,$K$2:$K$3,0))),0,MAX($A$1:A1)+1)

The data table looks like this:

SHEET1
Col_A Col_B Col_C Col_D
------- -------- ------- ----------
TEST DATE COUNTRY DATA
1 04/10/05 Syria xxxx
0 05/15/05 Kuwait xxxx
2 04/21/05 Yemen xxxx
0 05/12/05 Oman xxxx
3 04/04/05 Syria xxxx


4) On Sheet2, I copied the column headings from Sheet1
5) Down Col_A, I entered sequential numbers beginning with 1.
6) I entered the following formulas in cells B2, C2, and D2...and copied
them down:

B2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,2,0)
C2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,3,0)
D2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,4,0)

The table in Sheet2, only picks up valid items from Sheet1:

SHEET2
Col_A Col_B Col_C Col_D
------- -------- ------- ----------
TEST DATE COUNTRY DATA
1 04/10/05 Syria xxxx
2 04/21/05 Yemen xxxx
3 04/04/05 Syria xxxx
4 #N/A #N/A #N/A
5 #N/A #N/A #N/A
6 #N/A #N/A #N/A

Note: I left the #N/A items to demonstrate that there is no match for those
items.

Am I on the right track here?

Ron
--
Regards,
Ron

OK...For now, I'll assume you really do want to use ONLY Excel functions.

1) I copied your data into Shee1 of a new workbook, beginning in cell B1.
(Note: I changed the date formats to US)

2) I built a pseudo criteria range, beginning in cell I1:

Col_I Col_J Col_K
--------- ------- ---------
StartDate EndDate COUNTRY
04/01/05 04/30/05 Syria
Yemen

3) Col_A is a helper column with the following formula entered in A2 and
copied down:
A2: =IF(OR(B2<$I$2,B2>$J$2,ISERROR(MATCH(C2,$K$2:$K$3,0))),0,MAX($A$1:A1)+1)

The data table looks like this:

SHEET1
Col_A Col_B Col_C Col_D
------- -------- ------- ----------
TEST DATE COUNTRY DATA
1 04/10/05 Syria xxxx
0 05/15/05 Kuwait xxxx
2 04/21/05 Yemen xxxx
0 05/12/05 Oman xxxx
3 04/04/05 Syria xxxx


4) On Sheet2, I copied the column headings from Sheet1
5) Down Col_A, I entered sequential numbers beginning with 1.
6) I entered the following formulas in cells B2, C2, and D2...and copied
them down:

B2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,2,0)
C2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,3,0)
D2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,4,0)

The table in Sheet2, only picks up valid items from Sheet1:

SHEET2
Col_A Col_B Col_C Col_D
------- -------- ------- ----------
TEST DATE COUNTRY DATA
1 04/10/05 Syria xxxx
2 04/21/05 Yemen xxxx
3 04/04/05 Syria xxxx
4 #N/A #N/A #N/A
5 #N/A #N/A #N/A
6 #N/A #N/A #N/A

Note: I left the #N/A items to demonstrate that there is no match for those
items.

Am I on the right track here?

Ron
--
Regards,
Ron

OK...For now, I'll assume you really do want to use ONLY Excel functions.

1) I copied your data into Shee1 of a new workbook, beginning in cell B1.
(Note: I changed the date formats to US)

2) I built a pseudo criteria range, beginning in cell I1:

Col_I Col_J Col_K
--------- ------- ---------
StartDate EndDate COUNTRY
04/01/05 04/30/05 Syria
Yemen

3) Col_A is a helper column with the following formula entered in A2 and
copied down:
A2: =IF(OR(B2<$I$2,B2>$J$2,ISERROR(MATCH(C2,$K$2:$K$3,0))),0,MAX($A$1:A1)+1)

The data table looks like this:

SHEET1
Col_A Col_B Col_C Col_D
------- -------- ------- ----------
TEST DATE COUNTRY DATA
1 04/10/05 Syria xxxx
0 05/15/05 Kuwait xxxx
2 04/21/05 Yemen xxxx
0 05/12/05 Oman xxxx
3 04/04/05 Syria xxxx


4) On Sheet2, I copied the column headings from Sheet1
5) Down Col_A, I entered sequential numbers beginning with 1.
6) I entered the following formulas in cells B2, C2, and D2...and copied
them down:

B2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,2,0)
C2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,3,0)
D2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,4,0)

The table in Sheet2, only picks up valid items from Sheet1:

SHEET2
Col_A Col_B Col_C Col_D
------- -------- ------- ----------
TEST DATE COUNTRY DATA
1 04/10/05 Syria xxxx
2 04/21/05 Yemen xxxx
3 04/04/05 Syria xxxx
4 #N/A #N/A #N/A
5 #N/A #N/A #N/A
6 #N/A #N/A #N/A

Note: I left the #N/A items to demonstrate that there is no match for those
items.

Am I on the right track here?

Ron
--
Regards,
Ron

OK...For now, I'll assume you really do want to use ONLY Excel functions.

1) I copied your data into Shee1 of a new workbook, beginning in cell B1.
(Note: I changed the date formats to US)

2) I built a pseudo criteria range, beginning in cell I1:

Col_I Col_J Col_K
--------- ------- ---------
StartDate EndDate COUNTRY
04/01/05 04/30/05 Syria
Yemen

3) Col_A is a helper column with the following formula entered in A2 and
copied down:
A2: =IF(OR(B2<$I$2,B2>$J$2,ISERROR(MATCH(C2,$K$2:$K$3,0))),0,MAX($A$1:A1)+1)

The data table looks like this:

SHEET1
Col_A Col_B Col_C Col_D
------- -------- ------- ----------
TEST DATE COUNTRY DATA
1 04/10/05 Syria xxxx
0 05/15/05 Kuwait xxxx
2 04/21/05 Yemen xxxx
0 05/12/05 Oman xxxx
3 04/04/05 Syria xxxx


4) On Sheet2, I copied the column headings from Sheet1
5) Down Col_A, I entered sequential numbers beginning with 1.
6) I entered the following formulas in cells B2, C2, and D2...and copied
them down:

B2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,2,0)
C2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,3,0)
D2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,4,0)

The table in Sheet2, only picks up valid items from Sheet1:

SHEET2
Col_A Col_B Col_C Col_D
------- -------- ------- ----------
TEST DATE COUNTRY DATA
1 04/10/05 Syria xxxx
2 04/21/05 Yemen xxxx
3 04/04/05 Syria xxxx
4 #N/A #N/A #N/A
5 #N/A #N/A #N/A
6 #N/A #N/A #N/A

Note: I left the #N/A items to demonstrate that there is no match for those
items.

Am I on the right track here?

Ron
--
Regards,
Ron

OK...For now, I'll assume you really do want to use ONLY Excel functions.

1) I copied your data into Shee1 of a new workbook, beginning in cell B1.
(Note: I changed the date formats to US)

2) I built a pseudo criteria range, beginning in cell I1:

Col_I Col_J Col_K
--------- ------- ---------
StartDate EndDate COUNTRY
04/01/05 04/30/05 Syria
Yemen

3) Col_A is a helper column with the following formula entered in A2 and
copied down:
A2: =IF(OR(B2<$I$2,B2>$J$2,ISERROR(MATCH(C2,$K$2:$K$3,0))),0,MAX($A$1:A1)+1)

The data table looks like this:

SHEET1
Col_A Col_B Col_C Col_D
------- -------- ------- ----------
TEST DATE COUNTRY DATA
1 04/10/05 Syria xxxx
0 05/15/05 Kuwait xxxx
2 04/21/05 Yemen xxxx
0 05/12/05 Oman xxxx
3 04/04/05 Syria xxxx


4) On Sheet2, I copied the column headings from Sheet1
5) Down Col_A, I entered sequential numbers beginning with 1.
6) I entered the following formulas in cells B2, C2, and D2...and copied
them down:

B2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,2,0)
C2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,3,0)
D2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,4,0)

The table in Sheet2, only picks up valid items from Sheet1:

SHEET2
Col_A Col_B Col_C Col_D
------- -------- ------- ----------
TEST DATE COUNTRY DATA
1 04/10/05 Syria xxxx
2 04/21/05 Yemen xxxx
3 04/04/05 Syria xxxx
4 #N/A #N/A #N/A
5 #N/A #N/A #N/A
6 #N/A #N/A #N/A

Note: I left the #N/A items to demonstrate that there is no match for those
items.

Am I on the right track here?

Ron
--
Regards,
Ron

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>