LIST out all the data according to the "Criteria"

[wilchong via OfficeKB.com]

There are 3 data sets are assumed running in A6, in B6 and in C6 down, viz.:
In A6 down is data of "date": 1-Jan-07, 1-Jul-07, 1-Nov-07, 1-Apr-08 and 1-
Oct-08
In B6 down is data of "category": Revenue, Revenue, Revenue, Cost and Cost.
In C6 down is data of "US$": US$1,200, US$1,250, US$3,000, US$450 and US$550.

Another set of data is the "criteria": In cell G7 is the "1-Jan-08" ã€it is
Starting Date】; in cell G8 is the "1-Dec-08"ã€it is Ending Date】and in cell G9
is the "Cost" ã€it is Category】.

I know using the function "SUMPRODUCT" to compute the total amount within the
specific date and under specific category. However, what I want is to "LIST"
out all the data under specific date and specific category. In my example, I
need a formula which can list "1-Apr-08" and "1-Oct-08" in the cell F12 and
F13 and "US$450" and "US$550" in the cell G12 and G13. I need an Excel
formula to have this result!

Many thanks,
Wilchong

 

[Gary''s Student]

To list entire rows match criteria, consider using AutoFilter

 

[wilchong via OfficeKB.com]

Many thanks for your advice! I know AutoFilter is one option.

In fact, I am looking for alternative way to do it and also the database is
located in one spreadsheet and the data which I want to list under criteria
is located in another spreadsheet! As a result, I feel Excel function is the
best solution my case!

Many thanks for your help!

Wilchong

To list entire rows match criteria, consider using AutoFilter

There are 3 data sets are assumed running in A6, in B6 and in C6 down, viz.:
In A6 down is data of "date": 1-Jan-07, 1-Jul-07, 1-Nov-07, 1-Apr-08 and 1-

[quoted text clipped - 15 lines]

Many thanks,
Wilchong

 

[Gary''s Student]

Get can get the same result as AutoFilter by using formulas. See:

http://groups.google.com/group/micr...c786a33cccf991e?lnk=st&q=dog#1c786a33cccf991e
--
Gary''s Student - gsnu2007h

Many thanks for your advice! I know AutoFilter is one option.

In fact, I am looking for alternative way to do it and also the database is
located in one spreadsheet and the data which I want to list under criteria
is located in another spreadsheet! As a result, I feel Excel function is the
best solution my case!

Many thanks for your help!

Wilchong

To list entire rows match criteria, consider using AutoFilter

There are 3 data sets are assumed running in A6, in B6 and in C6 down, viz.:
In A6 down is data of "date": 1-Jan-07, 1-Jul-07, 1-Nov-07, 1-Apr-08 and 1-

[quoted text clipped - 15 lines]

Many thanks,
Wilchong

 

[Max]

This play will deliver it for your original posting ..

Let's assume the source data will be within row 6 to 100 in cols A to C
In H6:
=IF(COUNTA(G$6:G$8)<3,"",IF(AND(A6>=G$6,A6<=G$7,B6=G$8),ROWS($1:1),""))
Copy down to H100

Then place
In F12:
=IF(ROWS($1:1)>COUNT($H$6:$H$100),"",INDEX(A$6:A$100,SMALL($H$6:$H$100,ROWS($1:1))))

In G12:
=IF(ROWS($1:1)>COUNT($H$6:$H$100),"",INDEX(C$6:C$100,SMALL($H$6:$H$100,ROWS($1:1))))
Format F12 as date, G12 as currency to taste, then copy F12:G12 down as far
as required to cover the max expected lines for any particular criteria that
would be set in G6:G8. You'd get the exact results that you seek, with all
lines neatly bunched at the top.

 

[wilchong via OfficeKB.com]

Hello Gary''s Student - gsnu200781
I just tried your example and the Excel function you provided and I found out
it is working OK!

However, I have one question which need your further help. Because the size
of your database is too big for me, so I made it to 20 sample size (A1 to A20,
B1 to B20 etc.) In your example, the cell of D1 is search the data of value
of 7 in col A, my question is how the existing Excel function can be changed
if I ADD extra crietria e.g. the time frame e.g. between ã€1 Jun 08】and ã€30
Jun 08】? Therefore, I want to list out all the information which has value
of 7 between ã€1 Jun 08】and ã€30 Jun 08】. Many thanks for your help!

Many thanks,
Wilchong

Get can get the same result as AutoFilter by using formulas. See:

http://groups.google.com/group/micr...c786a33cccf991e?lnk=st&q=dog#1c786a33cccf991e

Many thanks for your advice! I know AutoFilter is one option.

[quoted text clipped - 13 lines]

 

[wilchong via OfficeKB.com]

Good morning Gary''s Student - gsnu200781
I just tried your example and the Excel function you provided and I found out
it is working OK!
Thank for your example:
http://groups.google.com/group/microsoft.public.excel.worksheet.functions/browse

_thread/thread/b8a1a3dfa8ca813f/1c786a33cccf991e?
lnk=st&q=dog#1c786a33cccf991e


However, I have one question which need your further help. Because the size
of your database is too big for me, so I made it to 20 sample size (A1 to A20,
B1 to B20 etc.)

From your example, instead of JUST searching the data which has value of 7 in
col A, I want to list out all the information which has value of 7 between ã€1
Jun 08】and ã€30 Jun 08】. My new question has add an EXTRA crietria e.g. the
time frame e.g. between ã€1 Jun 08】and ã€30 Jun 08】? In the example of 20
sample data, the result will be actomactic appear "27-Jun-08" in cell F1 and
"gnu" in cell G1. I need an Excel formula to have this result! I know this
question is extremely challenging! Many thanks for your help!

Wilchong

Get can get the same result as AutoFilter by using formulas. See:

http://groups.google.com/group/micr...c786a33cccf991e?lnk=st&q=dog#1c786a33cccf991e

Many thanks for your advice! I know AutoFilter is one option.

[quoted text clipped - 13 lines]

 

[wilchong via OfficeKB.com]

Dear Max,
Is your Excel formula applying on Gary''s Student’s database or my early
database which only from A6 to C10? Please advice!

Many thanks for your efforts!
Wilchong

 

[Max]

It's based on your original post, & I added my assumptions made, as per
lines:

 

[wilchong via OfficeKB.com]

Good Morning Max,
I have study your Excel formular and need your confirmation. Really sorry for
my insufficient Excel knowledge!

My example in the original posting: The 3 data sets (5 data sample size) are
assumed running in A6, in B6 and in C6 down, viz.:
In A6 down is data of "date": 1-Jan-07, 1-Jul-07, 1-Nov-07, 1-Apr-08 and 1-
Oct-08
In B6 down is data of "category": Revenue, Revenue, Revenue, Cost and Cost.
In C6 down is data of "US$": US$1,200, US$1,250, US$3,000, US$450 and US$550.

Another set of data is the "criteria": In cell G6 is the "1-Jan-08" ã€it is
the Starting Date】; in cell G7 is the "1-Dec-08"ã€it is the Ending Date】and in
cell G8 is the "Cost" ã€it is Category】.

In your Excel formular which you suggested to place in cell H6, what does
ROWS($1:1) actually mean?

Many thanks,
Wilchong

It's based on your original post, & I added my assumptions made, as per
lines:[quoted text clipped - 4 lines]

Many thanks for your efforts!
Wilchong

 

[Max]

All should be clear in this implemented, working sample:
http://www.freefilehosting.net/download/3gm22
AutoList Data by Multiple Criteria.xls

ROWS($1:1) ..

The above is used as an incrementer in the criteria col H.
As used in the criteria formula in H6 down, ROWS($1:1)
will simply return arb row numbers (1,5,9, etc)
where the criteria is satisfied within the source data.
These arb row numbers are then used by
the extract formulas placed in F12:G12 down
to "float up" the results nicely to the top.

 

[wilchong via OfficeKB.com]

Many thanks Max,
The Excel formulars are working extremely well. In your suggestion, the cell
in F12 and G12 are receiving the indicators from col H. I have one question:
Do you think your suggested Excel formula in F12 and G12 can be modified in
order to have the same result, but WITHOUT depending on the indicator
information on col H? What I mean is that without any Excel formula in col
H, F12 and G12 still can have the same result!

Many thanks for your time and effort,
Wilchong

All should be clear in this implemented, working sample:
http://www.freefilehosting.net/download/3gm22
AutoList Data by Multiple Criteria.xls

ROWS($1:1) ..

The above is used as an incrementer in the criteria col H.
As used in the criteria formula in H6 down, ROWS($1:1)
will simply return arb row numbers (1,5,9, etc)
where the criteria is satisfied within the source data.
These arb row numbers are then used by
the extract formulas placed in F12:G12 down
to "float up" the results nicely to the top.

Good Morning Max,
I have study your Excel formular and need your confirmation. Really sorry

[quoted text clipped - 23 lines]

Many thanks,
Wilchong

 

[Max]

The Excel formulas are working extremely well.
But of course. You're welcome.

What I mean is that without any Excel formula in col
H, F12 and G12 still can have the same result!

You can't spare even 1 helper col to specify the complex criteria out of 256
cols in the sheet <g>? I like to keep things simple, easy to understand
what's happening / debug / cross apply. It's also more efficient / much
faster to use non-array formulas, especially when you have to deal with
large ranges.

It's might be possible using complex array formulas, but I'll have to leave
it to other responders out there to venture their thoughts on this for you.

 

[wilchong via OfficeKB.com]

Dear Max,
I am really appreciate your help and advice these days! Thanks a lot! From
your valuable suggestion, I have learnt array formulas.

Please allow me to ask one more question regarding your suggested Excel
formula which place in G12. From the formulas: =IF(ROWS($1:1)>COUNT($H$6:$H
$100) .................., one thing I still don't understand the purpose of
ROWS($1:1) and why ROWS($1:1) needs greater than COUNT($H$6:$H$100). I think
the whole morning, and still cannot get the answer.

Many thanks for help!
Wilchong

The Excel formulas are working extremely well.

But of course. You're welcome.

What I mean is that without any Excel formula in col
H, F12 and G12 still can have the same result!

You can't spare even 1 helper col to specify the complex criteria out of 256
cols in the sheet <g>? I like to keep things simple, easy to understand
what's happening / debug / cross apply. It's also more efficient / much
faster to use non-array formulas, especially when you have to deal with
large ranges.

It's might be possible using complex array formulas, but I'll have to leave
it to other responders out there to venture their thoughts on this for you.

Many thanks Max,
The Excel formulars are working extremely well. In your suggestion, the

[quoted text clipped - 10 lines]

Many thanks for your time and effort,
Wilchong

 

[Max]

.. From your valuable suggestion, I have learnt array formulas.

Non-array formulas, you meant
All earlier expressions given are normal, non-array formulas

=IF(ROWS($1:1)>COUNT($H$6:$H$100),"", ...

The above is basically a simple "trap" to return neat looking blanks: ""
instead of "ugly" error values once all the results are "floated up"

COUNT($H$6:$H$100)
will return the number of arb row numbers within the criteria col H
(the number of arb row numbers = number of result lines which satisfy the
criteria)

ROWS($1:1) simply returns the number series: 1,2,3,... when copied down
So when this number exceeds the COUNT, the "trap" will evaluate to TRUE, and
return neat looking blanks: "" thereafter

Trust the above clarifies it ..

 

[wilchong via OfficeKB.com]

Good morning Max,
I am sorry about one question, what is "arb" stand for?
Many thanks,
Wilchong

.. From your valuable suggestion, I have learnt array formulas.

Non-array formulas, you meant
All earlier expressions given are normal, non-array formulas

=IF(ROWS($1:1)>COUNT($H$6:$H$100),"", ...

The above is basically a simple "trap" to return neat looking blanks: ""
instead of "ugly" error values once all the results are "floated up"

COUNT($H$6:$H$100)
will return the number of arb row numbers within the criteria col H
(the number of arb row numbers = number of result lines which satisfy the
criteria)

ROWS($1:1) simply returns the number series: 1,2,3,... when copied down
So when this number exceeds the COUNT, the "trap" will evaluate to TRUE, and
return neat looking blanks: "" thereafter

Trust the above clarifies it ..

Dear Max,
I am really appreciate your help and advice these days! Thanks a lot! From

[quoted text clipped - 11 lines]

Many thanks for help!
Wilchong

 

[Max]

arb = arbitrary