LIST out all the data according to the "Criteria"

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