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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200805/1

To list entire rows match criteria, consider using AutoFilter
--
Gary''s Student - gsnu2007


"wilchong via OfficeKB.com" wrote:

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200805/1

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

Gary''s Student wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200805/1

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

http://groups.google.com/group/micro...786a33cccf991e
--
Gary''s Student - gsnu2007h


"wilchong via OfficeKB.com" wrote:

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

Gary''s Student wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200805/1

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$1 00,SMALL($H$6:$H$100,ROWS($1:1))))

In G12:
=IF(ROWS($1:1)COUNT($H$6:$H$100),"",INDEX(C$6:C$1 00,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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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



Gary''s Student wrote:
Get can get the same result as AutoFilter by using formulas. See:

http://groups.google.com/group/micro...786a33cccf991e
Many thanks for your advice! I know AutoFilter is one option.

[quoted text clipped - 13 lines]
Many thanks,
Wilchong

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200805/1

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/micro...nctions/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




Gary''s Student wrote:
Get can get the same result as AutoFilter by using formulas. See:

http://groups.google.com/group/micro...786a33cccf991e
Many thanks for your advice! I know AutoFilter is one option.

[quoted text clipped - 13 lines]
Many thanks,
Wilchong

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200805/1

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

Many thanks for your efforts!
Wilchong




Max wrote:
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,B 6=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.

--
Message posted via http://www.officekb.com

It's based on your original post, & I added my assumptions made, as per
lines:
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:83b9fa8cb2eb3@uwe...
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

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





Max wrote:
It's based on your original post, & I added my assumptions made, as per
lines:
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
[quoted text clipped - 4 lines]
Many thanks for your efforts!
Wilchong

--
Message posted via http://www.officekb.com

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:83c5e2bc9125b@uwe...
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

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



Max wrote:
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

--
Message posted via http://www.officekb.com

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:83c89fe53aad1@uwe...
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

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



Max wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200805/1

.. 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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:83d3b1ea0fe72@uwe...
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

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

Max wrote:
.. 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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200805/1

arb = arbitrary
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:83de04ec46bf2@uwe...
Good morning Max,
I am sorry about one question, what is "arb" stand for?
Many thanks,
Wilchong