Extracting data without using database functions

[MelbTim]

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

 

[Ron Coderre]

Just to confirm.....

You DO NOT wish to use Autofilter or Advanced Filter.

Is that true?

Ron

 

[Guest]

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

 

[MelbTim]

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

 

[Peo Sjoblom]

School work?

 

[Biff]

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