Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 90
Default Seeking Improvement on excel function

Dear sir,
I have one question regarding an "index" formular together with "match". I
used to set the following excel function to pick the data from the database:
=INDEX("database",MATCH("column argument","column range",0),"target column
range").

One of key thing of this excel funation is "target column range", which only
allow to cover ONE column only.

My question: Is there any way to improve or change the above excel function
in order to make "target column range" can cover more column ranges. I have
also thought about Vlookup and lookup function, but failed.

Thanks for your advice,
Wilchong

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

  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Seeking Improvement on excel function

Not enough detail.

See if this helps...

...........A..........B..........C
.....................Red......Blue
1........x.........10........12
2........y.........14........18
3........z.........22........30

If you have descriptive column headers like the sample table then you can
use a MATCH function to define the column.

To lookup "y" and "Blue"...

E1 = y
F1 = blue

=INDEX(B2:C4,MATCH(E1,A2:A4,0),MATCH(F1,B1:C1,0))

Or:

=VLOOKUP(E1,A1:C4,MATCH(F1,A1:C1,0),0)

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:9ac23842c2204@uwe...
Dear sir,
I have one question regarding an "index" formular together with "match".
I
used to set the following excel function to pick the data from the
database:
=INDEX("database",MATCH("column argument","column range",0),"target column
range").

One of key thing of this excel funation is "target column range", which
only
allow to cover ONE column only.

My question: Is there any way to improve or change the above excel
function
in order to make "target column range" can cover more column ranges. I
have
also thought about Vlookup and lookup function, but failed.

Thanks for your advice,
Wilchong

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



  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 90
Default Seeking Improvement on excel function

Dear T. Valko,
Thanks for your guidance. Based on your data on the table, I wish I can
instruct excel function to show €œOK€ if it detects 12 within row 1 (the
argument is €œx€) in the cell E2 as below table. I will place the excel
function (with two variables: 12 in the cell E2 and €œx€) from F1 to F10.
E1 = 11 F1 = -
E2 = 12 F2 = €œOK€
E3 = 13 F3 = -
E4 = 14 F4 = -
E5 = 15 F5 = -
E6 = 16 F6 = -
E7 = 17 F7 = €œOK€
E8 = 18 F8 = -
E9 = 19 F9 = -
E10 = 20 F10 = -

Many thanks for your advice,
Wilchong




wilchong wrote:
Dear sir,
I have one question regarding an "index" formular together with "match". I
used to set the following excel function to pick the data from the database:
=INDEX("database",MATCH("column argument","column range",0),"target column
range").

One of key thing of this excel funation is "target column range", which only
allow to cover ONE column only.

My question: Is there any way to improve or change the above excel function
in order to make "target column range" can cover more column ranges. I have
also thought about Vlookup and lookup function, but failed.

Thanks for your advice,
Wilchong


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

  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Seeking Improvement on excel function

Try something like this...

D1 = x

=IF(COUNTIF(INDEX(B$2:C$4,MATCH(D$1,A$2:A$4,0),0), E1),"OK","-")

Copy down as needed.

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:9ac35ae0d0fe3@uwe...
Dear T. Valko,
Thanks for your guidance. Based on your data on the table, I wish I can
instruct excel function to show "OK" if it detects 12 within row 1 (the
argument is "x") in the cell E2 as below table. I will place the excel
function (with two variables: 12 in the cell E2 and "x") from F1 to F10.
E1 = 11 F1 = -
E2 = 12 F2 = "OK"
E3 = 13 F3 = -
E4 = 14 F4 = -
E5 = 15 F5 = -
E6 = 16 F6 = -
E7 = 17 F7 = "OK"
E8 = 18 F8 = -
E9 = 19 F9 = -
E10 = 20 F10 = -

Many thanks for your advice,
Wilchong




wilchong wrote:
Dear sir,
I have one question regarding an "index" formular together with "match".
I
used to set the following excel function to pick the data from the
database:
=INDEX("database",MATCH("column argument","column range",0),"target column
range").

One of key thing of this excel funation is "target column range", which
only
allow to cover ONE column only.

My question: Is there any way to improve or change the above excel
function
in order to make "target column range" can cover more column ranges. I
have
also thought about Vlookup and lookup function, but failed.

Thanks for your advice,
Wilchong


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



  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 90
Default Seeking Improvement on excel function

Dear T.Valko,
The formular working very well.

Many thanks,
Wilson


wilchong wrote:
Dear T. Valko,
Thanks for your guidance. Based on your data on the table, I wish I can
instruct excel function to show €œOK€ if it detects 12 within row 1 (the
argument is €œx€) in the cell E2 as below table. I will place the excel
function (with two variables: 12 in the cell E2 and €œx€) from F1 to F10.
E1 = 11 F1 = -
E2 = 12 F2 = €œOK€
E3 = 13 F3 = -
E4 = 14 F4 = -
E5 = 15 F5 = -
E6 = 16 F6 = -
E7 = 17 F7 = €œOK€
E8 = 18 F8 = -
E9 = 19 F9 = -
E10 = 20 F10 = -

Many thanks for your advice,
Wilchong

Dear sir,
I have one question regarding an "index" formular together with "match". I

[quoted text clipped - 11 lines]
Thanks for your advice,
Wilchong


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



  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Seeking Improvement on excel function

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:9ac3ebe080250@uwe...
Dear T.Valko,
The formular working very well.

Many thanks,
Wilson


wilchong wrote:
Dear T. Valko,
Thanks for your guidance. Based on your data on the table, I wish I can
instruct excel function to show "OK" if it detects 12 within row 1 (the
argument is "x") in the cell E2 as below table. I will place the excel
function (with two variables: 12 in the cell E2 and "x") from F1 to F10.
E1 = 11 F1 = -
E2 = 12 F2 = "OK"
E3 = 13 F3 = -
E4 = 14 F4 = -
E5 = 15 F5 = -
E6 = 16 F6 = -
E7 = 17 F7 = "OK"
E8 = 18 F8 = -
E9 = 19 F9 = -
E10 = 20 F10 = -

Many thanks for your advice,
Wilchong

Dear sir,
I have one question regarding an "index" formular together with "match".
I

[quoted text clipped - 11 lines]
Thanks for your advice,
Wilchong


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



  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 90
Default Seeking Improvement on excel function

Dear T. Valko,
Based on the source database, your suggested function =IF(COUNTIF(INDEX(B$2:C
$4,MATCH(D$1,A$2:A$4,0),0),E1),"OK","-") will show €œOK€ if it detects 12
within row 1 (the argument is €œx€) in the cell E2. Every things work very
perfect.

In order to make the analysis more intensive, I would like, based on the data
(F1 to F10), I need a function to analyse the data based on another database,
see below.

E21 = 11€¦€¦€¦€¦€¦€¦ F21 = T
E22 = 12€¦€¦€¦€¦€¦€¦ F22 = G
E23 = 13€¦€¦€¦€¦€¦€¦ F23 = R
E24 = 14€¦€¦€¦€¦€¦€¦ F24 = E
E25 = 15€¦€¦€¦€¦€¦€¦ F25 = K
E26 = 16€¦€¦€¦€¦€¦€¦ F26 = Q
E27 = 17€¦€¦€¦€¦€¦€¦ F27 = L
E28 = 18€¦€¦€¦€¦€¦€¦ F28 = C
E29 = 19€¦€¦€¦€¦€¦€¦ F29 = Z
E30 = 20€¦€¦€¦€¦€¦€¦ F30 = I

I tried to use function MODE plus IF to construct a formula, based on the
database above, to show €œG€ if the formula detect €œOK€ along €œ12€, but failed.
Can you advice me other option to do this!

Many thanks,
Wilchong







T. Valko wrote:
You're welcome. Thanks for the feedback!

Dear T.Valko,
The formular working very well.

[quoted text clipped - 27 lines]
Thanks for your advice,
Wilchong


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

  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Seeking Improvement on excel function

If I understand what you want then maybe this:

D1 = x

=IF(COUNTIF(INDEX(B$2:C$4,MATCH(D$1,A$2:A$4,0),0), E1),LOOKUP(E1,E$21:F$30),"-")

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:9ac5211fe38b0@uwe...
Dear T. Valko,
Based on the source database, your suggested function
=IF(COUNTIF(INDEX(B$2:C
$4,MATCH(D$1,A$2:A$4,0),0),E1),"OK","-") will show "OK" if it detects 12
within row 1 (the argument is "x") in the cell E2. Every things work very
perfect.

In order to make the analysis more intensive, I would like, based on the
data
(F1 to F10), I need a function to analyse the data based on another
database,
see below.

E21 = 11...... F21 = T
E22 = 12...... F22 = G
E23 = 13...... F23 = R
E24 = 14...... F24 = E
E25 = 15...... F25 = K
E26 = 16...... F26 = Q
E27 = 17...... F27 = L
E28 = 18...... F28 = C
E29 = 19...... F29 = Z
E30 = 20...... F30 = I

I tried to use function MODE plus IF to construct a formula, based on the
database above, to show "G" if the formula detect "OK" along "12", but
failed.
Can you advice me other option to do this!

Many thanks,
Wilchong







T. Valko wrote:
You're welcome. Thanks for the feedback!

Dear T.Valko,
The formular working very well.

[quoted text clipped - 27 lines]
Thanks for your advice,
Wilchong


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



  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 90
Default Seeking Improvement on excel function

Dear T. Valko,
Thanks, your suggested function work very well, it is exactly fit to what I
am think!

I have found out that your suggested function, IF(COUNTIF(INDEX(B$2:C$4,MATCH
(D$1,A$2:A$4,0),0),$E1),LOOKUP($E1,E$21:F$30),"-"), depends on two arguments,
one is the D1, another cell is E1.

Right now, I want to do something little bit complicated.

I want to make your suggested function with additional function, which is
only show the data from F21 to F30 from lowest value to greatest value (Based
on the value from the cell, E21 to E30). This function has to be done without
the argument from the cell, E1.

In order to achieve the objective, I tried to add "IF(ROWS(H21:H$21)<=SUM(--
(COUNTIF(A$2:A$4,$E$21:$F$30)=0)) in front of your suggested function. I also
add " ROWS(H$21:H21))),"") " at the back of your suggested formula.

The new function is located in the cell, H21, then entered by "Shift +
Control + Enter". I also dragged the function from H21 to H23.

If D1 = y, my desire outcome is to see "E" show in the cell H21, "C" show in
the cell H22 and "-" show in the cell H23.

Of course, up to this point, the new function doesn't work!

I am really doubt my revised function can be improved based on my requirement,
I need your advice.

Many thanks for your time.
Wilchong







T. Valko wrote:
If I understand what you want then maybe this:

D1 = x

=IF(COUNTIF(INDEX(B$2:C$4,MATCH(D$1,A$2:A$4,0),0) ,E1),LOOKUP(E1,E$21:F$30),"-")

Dear T. Valko,
Based on the source database, your suggested function

[quoted text clipped - 35 lines]
Thanks for your advice,
Wilchong


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

  #10   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Seeking Improvement on excel function

I'm not following you on this. Post some sample data and explain what result
you expect.

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:9adc3708900ee@uwe...
Dear T. Valko,
Thanks, your suggested function work very well, it is exactly fit to what
I
am think!

I have found out that your suggested function,
IF(COUNTIF(INDEX(B$2:C$4,MATCH
(D$1,A$2:A$4,0),0),$E1),LOOKUP($E1,E$21:F$30),"-"), depends on two
arguments,
one is the D1, another cell is E1.

Right now, I want to do something little bit complicated.

I want to make your suggested function with additional function, which is
only show the data from F21 to F30 from lowest value to greatest value
(Based
on the value from the cell, E21 to E30). This function has to be done
without
the argument from the cell, E1.

In order to achieve the objective, I tried to add
"IF(ROWS(H21:H$21)<=SUM(--
(COUNTIF(A$2:A$4,$E$21:$F$30)=0)) in front of your suggested function. I
also
add " ROWS(H$21:H21))),"") " at the back of your suggested formula.

The new function is located in the cell, H21, then entered by "Shift +
Control + Enter". I also dragged the function from H21 to H23.

If D1 = y, my desire outcome is to see "E" show in the cell H21, "C" show
in
the cell H22 and "-" show in the cell H23.

Of course, up to this point, the new function doesn't work!

I am really doubt my revised function can be improved based on my
requirement,
I need your advice.

Many thanks for your time.
Wilchong







T. Valko wrote:
If I understand what you want then maybe this:

D1 = x

=IF(COUNTIF(INDEX(B$2:C$4,MATCH(D$1,A$2:A$4,0),0 ),E1),LOOKUP(E1,E$21:F$30),"-")

Dear T. Valko,
Based on the source database, your suggested function

[quoted text clipped - 35 lines]
Thanks for your advice,
Wilchong


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





  #11   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 90
Default Seeking Improvement on excel function

Dear T. Valko,
Thank, I know what is my problem. In order to make thing easy, I would like
to break the whole thing into stages and explain step by step.

Just before I talk about the complicate function, first of all, I would like
to ask you one minor question regarding your previous function. Your
previous suggested function as follow:
=IF(COUNTIF(INDEX(B$2:C$4,MATCH(D$1,A$2:A$4,0),0), $E1),LOOKUP($E1,E$21:F$30),
"-")
I placed this formula in the cell of G1 and drag it from G1 to G10. Because
the formula detects E2, so €œG€ will show in the cell of G2. €œG€ is come the
database as follow:

E21 = 11€¦€¦€¦€¦€¦€¦ F21 = T
E22 = 12€¦€¦€¦€¦€¦€¦ F22 = G
E23 = 13€¦€¦€¦€¦€¦€¦ F23 = R
E24 = 14€¦€¦€¦€¦€¦€¦ F24 = E
E25 = 15€¦€¦€¦€¦€¦€¦ F25 = K
E26 = 16€¦€¦€¦€¦€¦€¦ F26 = Q
E27 = 17€¦€¦€¦€¦€¦€¦ F27 = L
E28 = 18€¦€¦€¦€¦€¦€¦ F28 = C
E29 = 19€¦€¦€¦€¦€¦€¦ F29 = Z
E30 = 20€¦€¦€¦€¦€¦€¦ F30 = I

What I want to do the first thing is to revise the above formula slightly.
The above formula will achieve the result based on the variable from E1 to
E10, NOT from F1 to F10 as show you just now. So my first question is that
do you think the argument of above function can be changed to F1 rather than
E1?

Many thanks for your time,
Wilchong




T. Valko wrote:
I'm not following you on this. Post some sample data and explain what result
you expect.

Dear T. Valko,
Thanks, your suggested function work very well, it is exactly fit to what

[quoted text clipped - 49 lines]
Thanks for your advice,
Wilchong


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

  #12   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Seeking Improvement on excel function

The original formula in F1:F10 returns either a dash "-" or "OK".

Are you saying that you want another formula that checks F1:F10 for "OK" and
if found do a lookup on the entries in E1:E10?

If that's what yo want try this:

=IF(F1="OK",LOOKUP(E1,E$21:F$30),"-")

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:9ae88b0fb2b41@uwe...
Dear T. Valko,
Thank, I know what is my problem. In order to make thing easy, I would
like
to break the whole thing into stages and explain step by step.

Just before I talk about the complicate function, first of all, I would
like
to ask you one minor question regarding your previous function. Your
previous suggested function as follow:
=IF(COUNTIF(INDEX(B$2:C$4,MATCH(D$1,A$2:A$4,0),0), $E1),LOOKUP($E1,E$21:F$30),
"-")
I placed this formula in the cell of G1 and drag it from G1 to G10.
Because
the formula detects E2, so "G" will show in the cell of G2. "G" is come
the
database as follow:

E21 = 11...... F21 = T
E22 = 12...... F22 = G
E23 = 13...... F23 = R
E24 = 14...... F24 = E
E25 = 15...... F25 = K
E26 = 16...... F26 = Q
E27 = 17...... F27 = L
E28 = 18...... F28 = C
E29 = 19...... F29 = Z
E30 = 20...... F30 = I

What I want to do the first thing is to revise the above formula slightly.
The above formula will achieve the result based on the variable from E1 to
E10, NOT from F1 to F10 as show you just now. So my first question is
that
do you think the argument of above function can be changed to F1 rather
than
E1?

Many thanks for your time,
Wilchong




T. Valko wrote:
I'm not following you on this. Post some sample data and explain what
result
you expect.

Dear T. Valko,
Thanks, your suggested function work very well, it is exactly fit to
what

[quoted text clipped - 49 lines]
Thanks for your advice,
Wilchong


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



  #13   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 90
Default Seeking Improvement on excel function

Dear T. Valko,
Thanks, your suggested function work very well. With your suggested formula,
the €œG€ shown up in the of G2 when the formula detect €œOK€ in F2. OK, the
first step is ok already.

The second step is how to revise the formula in order to make the €œG€ shown
up in the of G1 when the formula detect €œOK€ from F1 to F10. From my
previous experience you show me, I can add €œ=IF(ROWS(G1:G$10)<=SUM(--(COUNTIF
(€¦€¦€¦€¦€¦..€ and then entered by "Shift + Control + Enter". I also dragged the
function from G1 to G10. Of course, what I tried to do was failed. As a
result, I need your advice.

Many thanks for your time.
Wilchong




T. Valko wrote:
The original formula in F1:F10 returns either a dash "-" or "OK".

Are you saying that you want another formula that checks F1:F10 for "OK" and
if found do a lookup on the entries in E1:E10?

If that's what yo want try this:

=IF(F1="OK",LOOKUP(E1,E$21:F$30),"-")

Dear T. Valko,
Thank, I know what is my problem. In order to make thing easy, I would

[quoted text clipped - 45 lines]
Thanks for your advice,
Wilchong


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

  #14   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Seeking Improvement on excel function

OK, I think I uderstand what you want.

I would put all the table data in a contiguous range like this:

...........E..........F..........G
1.......11.........T..........-
2.......12.........G........OK
3.......13.........R..........-
4.......14.........E..........-

Then:

Formula in H1:

=COUNTIF(G1:G10,"OK")

Then this array formula** in H2 and copied down:

=IF(ROWS(H$2:H2)H$1,"",INDEX(F$1:F$10,SMALL(IF(G$ 1:G$10="OK",ROW(F$1:F$10)),ROWS(H$2:H2))-ROW(F$1)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:9b0fa83429dd4@uwe...
Dear T. Valko,
Thanks, your suggested function work very well. With your suggested
formula,
the "G" shown up in the of G2 when the formula detect "OK" in F2. OK, the
first step is ok already.

The second step is how to revise the formula in order to make the "G"
shown
up in the of G1 when the formula detect "OK" from F1 to F10. From my
previous experience you show me, I can add
"=IF(ROWS(G1:G$10)<=SUM(--(COUNTIF
(......." and then entered by "Shift + Control + Enter". I also dragged
the
function from G1 to G10. Of course, what I tried to do was failed. As a
result, I need your advice.

Many thanks for your time.
Wilchong




T. Valko wrote:
The original formula in F1:F10 returns either a dash "-" or "OK".

Are you saying that you want another formula that checks F1:F10 for "OK"
and
if found do a lookup on the entries in E1:E10?

If that's what yo want try this:

=IF(F1="OK",LOOKUP(E1,E$21:F$30),"-")

Dear T. Valko,
Thank, I know what is my problem. In order to make thing easy, I would

[quoted text clipped - 45 lines]
Thanks for your advice,
Wilchong


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



  #15   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 90
Default Seeking Improvement on excel function

Dear T. Valko,
Yes, you got my idea. However, I want to make the formula even smarter. So,
I tried to modify your suggested formula. After revised your formula, it
should have two functions as follow:
1) Detect any €œOK€ in column F;
2) Once able to identify €œOK€ which refer to €œ12€, the array formula will
base on the below database show €œO€ in the cell G1.

The desire result is show below (of course, I failed to combine both
functions in your array formula).
RESULT:-
..........E..........F..........G
1.......11......... - ......... O
2.......12........OK........ -
3.......13......... - ......... -
4.......14......... - ......... -

The revised formula is placed in G1 and drag it to G4. Please note that the
revised formula is not necessary place in G1 and correspondent to column F,
that is why I want to put an array formula.

DATABASE:-
€œO€ refers to €œ11€; €œE€ refers to €œ12€; etc.
€¦.............E............F
21..€¦€¦...11....€¦€¦O
22€¦€¦€¦.12€¦.€¦€¦E
23€¦€¦€¦.13€¦€¦€¦O
24€¦€¦€¦.14€¦€¦€¦E
25€¦€¦€¦.15€¦€¦€¦O
26€¦€¦€¦.16€¦€¦€¦E
27€¦€¦€¦.17€¦€¦€¦O
28€¦€¦€¦.18€¦€¦€¦E
29€¦€¦€¦.19€¦€¦€¦O
30€¦€¦....20€¦....€¦E

Once again, many thanks for your advice,
Wilchong







T. Valko wrote:
OK, I think I uderstand what you want.

I would put all the table data in a contiguous range like this:

..........E..........F..........G
1.......11.........T..........-
2.......12.........G........OK
3.......13.........R..........-
4.......14.........E..........-

Then:

Formula in H1:

=COUNTIF(G1:G10,"OK")

Then this array formula** in H2 and copied down:

=IF(ROWS(H$2:H2)H$1,"",INDEX(F$1:F$10,SMALL(IF(G $1:G$10="OK",ROW(F$1:F$10)),ROWS(H$2:H2))-ROW(F$1)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Dear T. Valko,
Thanks, your suggested function work very well. With your suggested

[quoted text clipped - 30 lines]
Thanks for your advice,
Wilchong


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



  #16   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Seeking Improvement on excel function

Why would the result in G1 be O when 11 is not found in the table?

This is getting harder and harder to follow!

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:9b195653cb2c7@uwe...
Dear T. Valko,
Yes, you got my idea. However, I want to make the formula even smarter.
So,
I tried to modify your suggested formula. After revised your formula, it
should have two functions as follow:
1) Detect any "OK" in column F;
2) Once able to identify "OK" which refer to "12", the array formula
will
base on the below database show "O" in the cell G1.

The desire result is show below (of course, I failed to combine both
functions in your array formula).
RESULT:-
.........E..........F..........G
1.......11......... - ......... O
2.......12........OK........ -
3.......13......... - ......... -
4.......14......... - ......... -

The revised formula is placed in G1 and drag it to G4. Please note that
the
revised formula is not necessary place in G1 and correspondent to column
F,
that is why I want to put an array formula.

DATABASE:-
"O" refers to "11"; "E" refers to "12"; etc.
..............E............F
21.......11......O
22....12....E
23....13...O
24....14...E
25....15...O
26....16...E
27....17...O
28....18...E
29....19...O
30......20......E

Once again, many thanks for your advice,
Wilchong







T. Valko wrote:
OK, I think I uderstand what you want.

I would put all the table data in a contiguous range like this:

..........E..........F..........G
1.......11.........T..........-
2.......12.........G........OK
3.......13.........R..........-
4.......14.........E..........-

Then:

Formula in H1:

=COUNTIF(G1:G10,"OK")

Then this array formula** in H2 and copied down:

=IF(ROWS(H$2:H2)H$1,"",INDEX(F$1:F$10,SMALL(IF( G$1:G$10="OK",ROW(F$1:F$10)),ROWS(H$2:H2))-ROW(F$1)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Dear T. Valko,
Thanks, your suggested function work very well. With your suggested

[quoted text clipped - 30 lines]
Thanks for your advice,
Wilchong


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



  #17   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 90
Default Seeking Improvement on excel function

Dear T. Valko,
Sorry to make your life so difficult! Really sorry about that! Indeed, the
formula which I wish to have is quite hard and challenging.

The reason why I really need to place the array formula in G1 is because
considering only a few €œOK€ (five out of 200 data) will be shown in column F.
Furthermore, I will put this array formula in other spreadsheet at the end of
the day. Finally, I want to save space as well.

Actually, before asking your advice, I was trying very hard in fitting
different database functions in array formula, of course, it failed. As a
result, under desperate situation, I have to seeking the advice from the
excel €œguru€ like you. Really appreciate your advice and time.

Once again, many thanks for your advice,
Wilchong




T. Valko wrote:
Why would the result in G1 be O when 11 is not found in the table?

This is getting harder and harder to follow!

Dear T. Valko,
Yes, you got my idea. However, I want to make the formula even smarter.

[quoted text clipped - 68 lines]
Thanks for your advice,
Wilchong


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

  #18   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Seeking Improvement on excel function

I don't see how O relates to 12 (OK)?

I thought you wanted the results that correspond to "OK"?

At this point I'm lost!

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:9b1b27e144142@uwe...
Dear T. Valko,
Sorry to make your life so difficult! Really sorry about that! Indeed,
the
formula which I wish to have is quite hard and challenging.

The reason why I really need to place the array formula in G1 is because
considering only a few "OK" (five out of 200 data) will be shown in
column F.
Furthermore, I will put this array formula in other spreadsheet at the end
of
the day. Finally, I want to save space as well.

Actually, before asking your advice, I was trying very hard in fitting
different database functions in array formula, of course, it failed. As
a
result, under desperate situation, I have to seeking the advice from the
excel "guru" like you. Really appreciate your advice and time.

Once again, many thanks for your advice,
Wilchong




T. Valko wrote:
Why would the result in G1 be O when 11 is not found in the table?

This is getting harder and harder to follow!

Dear T. Valko,
Yes, you got my idea. However, I want to make the formula even smarter.

[quoted text clipped - 68 lines]
Thanks for your advice,
Wilchong


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



  #19   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 90
Default Seeking Improvement on excel function

Really sorry Valko, may be thinking too much formulas has made me "hang over"
!!

Yes, you are right. Should be "E", which relate to 12 (OK)! "O" is for 11,
13, 15 and etc!

Many thanks,
Wilson






T. Valko wrote:
I don't see how O relates to 12 (OK)?

I thought you wanted the results that correspond to "OK"?

At this point I'm lost!

Dear T. Valko,
Sorry to make your life so difficult! Really sorry about that! Indeed,

[quoted text clipped - 26 lines]
Thanks for your advice,
Wilchong


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

  #20   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 90
Default Seeking Improvement on excel function

Dear T. Valko,
Really sorry to make thing so complicated!, sorry about that! After
considering your suggestion, I have changed the database in order to make the
thing simple.

One thing need your help, below is the database, I need a formula to extract
all the data from A1 to A10.
€¦...........A
1..€¦€¦...TY
2€¦€¦€¦. -
3€¦€¦€¦.ER
4€¦€¦€¦.SX
5€¦€¦€¦. -
6€¦€¦€¦. -
7€¦€¦€¦.SX
8€¦€¦€¦.TY
9€¦€¦€¦. -
10€¦€¦....ER

The formula I wish to extract the data from A1 to A10 will show the result as
follow: to list the data from B1 to B6:
€¦...........B
1..€¦€¦.. TY
2€¦€¦€¦ ER
3€¦€¦€¦ SX
4€¦€¦€¦ SX
5€¦€¦€¦ TY
6€¦€¦€¦. ER
7€¦€¦€¦. -
8€¦€¦€¦. -
9€¦€¦€¦. -
10€¦€¦... -


And again, from the previous experience you have shown me, I can use €œ=IF
(ROWS(B$1:B10)<=SUM(--(COUNTIF(€¦€¦€¦€¦€¦ € and then entered by "Shift + Control
+ Enter". I also dragged the formula from B1 to B10. Of course, what I tried
to do was failed. As a result, I need your advice.

Many thanks,
Wilchong








T. Valko wrote:
I don't see how O relates to 12 (OK)?

I thought you wanted the results that correspond to "OK"?

At this point I'm lost!

Dear T. Valko,
Sorry to make your life so difficult! Really sorry about that! Indeed,

[quoted text clipped - 26 lines]
Thanks for your advice,
Wilchong


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



  #21   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Seeking Improvement on excel function

Try this array formula** entered in B1:

=IF(ROWS(B$1:B1)COUNTIF(A$1:A$10,"<-"),"",INDEX(A$1:A$10,SMALL(IF(A$1:A$10<"-",ROW(A$1:A$10)),ROWS(B$1:B1))-ROW(A$1)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down to B10.

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:9b64d58adfe9c@uwe...
Dear T. Valko,
Really sorry to make thing so complicated!, sorry about that! After
considering your suggestion, I have changed the database in order to make
the
thing simple.

One thing need your help, below is the database, I need a formula to
extract
all the data from A1 to A10.
............A
1.......TY
2.... -
3....ER
4....SX
5.... -
6.... -
7....SX
8....TY
9.... -
10......ER

The formula I wish to extract the data from A1 to A10 will show the result
as
follow: to list the data from B1 to B6:
............B
1...... TY
2... ER
3... SX
4... SX
5... TY
6.... ER
7.... -
8.... -
9.... -
10..... -


And again, from the previous experience you have shown me, I can use "=IF
(ROWS(B$1:B10)<=SUM(--(COUNTIF(..... " and then entered by "Shift +
Control
+ Enter". I also dragged the formula from B1 to B10. Of course, what I
tried
to do was failed. As a result, I need your advice.

Many thanks,
Wilchong








T. Valko wrote:
I don't see how O relates to 12 (OK)?

I thought you wanted the results that correspond to "OK"?

At this point I'm lost!

Dear T. Valko,
Sorry to make your life so difficult! Really sorry about that! Indeed,

[quoted text clipped - 26 lines]
Thanks for your advice,
Wilchong


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



  #22   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 90
Default Seeking Improvement on excel function

Thank Valko,
That is the array formula I looking for, however, how to re-adjust the array
formula again if I want to have the result as following:

€¦...........B
1..€¦€¦.. TY
2€¦€¦€¦ ER
3€¦€¦€¦ SX
4€¦€¦€¦ SX
5€¦€¦€¦ TY
6€¦€¦€¦. ER
7€¦€¦€¦. -
8€¦€¦€¦. -
9€¦€¦€¦. -
10€¦€¦... -


Many thanks for your time,
Wilchong





T. Valko wrote:
Try this array formula** entered in B1:

=IF(ROWS(B$1:B1)COUNTIF(A$1:A$10,"<-"),"",INDEX(A$1:A$10,SMALL(IF(A$1:A$10<"-",ROW(A$1:A$10)),ROWS(B$1:B1))-ROW(A$1)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down to B10.

Dear T. Valko,
Really sorry to make thing so complicated!, sorry about that! After

[quoted text clipped - 53 lines]
Thanks for your advice,
Wilchong


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

  #23   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Seeking Improvement on excel function

In ther words, you want a dash "-" instead of a blank...

Just change this portion:

=IF(ROWS(B$1:B1)COUNTIF(A$1:A$10,"<-"),"",

To:

=IF(ROWS(B$1:B1)COUNTIF(A$1:A$10,"<-"),"-",

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:9b662404072ff@uwe...
Thank Valko,
That is the array formula I looking for, however, how to re-adjust the
array
formula again if I want to have the result as following:

............B
1...... TY
2... ER
3... SX
4... SX
5... TY
6.... ER
7.... -
8.... -
9.... -
10..... -


Many thanks for your time,
Wilchong





T. Valko wrote:
Try this array formula** entered in B1:

=IF(ROWS(B$1:B1)COUNTIF(A$1:A$10,"<-"),"",INDEX(A$1:A$10,SMALL(IF(A$1:A$10<"-",ROW(A$1:A$10)),ROWS(B$1:B1))-ROW(A$1)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Copy down to B10.

Dear T. Valko,
Really sorry to make thing so complicated!, sorry about that! After

[quoted text clipped - 53 lines]
Thanks for your advice,
Wilchong


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



  #24   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 90
Default Seeking Improvement on excel function

Dear T. Valko,
Thanks for your suggested formula! Your formula is working very perfect.
However, I have a minor problem which I put the same formula in different
cell, the result turn out slightly differently despite how I revise the
formula.

First of all, let me explain the situation, below is the database. You will
find a Greek symbol (alpha) in the cell of F10 as below.
€¦...........F
5€¦€¦€¦ -
6€¦€¦€¦ -
7€¦€¦€¦ -
8€¦€¦€¦ -
9€¦€¦€¦ -
10€¦€¦.. α
11€¦€¦€¦ -
12€¦€¦... -
13€¦€¦€¦. -
14€¦€¦... -

OK, the second thing I did was put (entered by "Shift + Control + Enter")
your suggest formula in the cell of F17 and drag the formula to F26.

Below is the formula I put from the cell of F17 to F26:
=IF(ROWS(F$17:F17)COUNTIF(F$5:F$14,"<-"),"-",INDEX(F$5:F$14,SMALL(IF(F$5:F
$14<"",ROW(F$5:F$14)),ROWS(F$17:F17))-ROW(F$5)+1))

The result turns out not so perfect as before. The first result is ok,
because the Greek symbol shown in the cell of F17, that is what I want, no
problem about that. The second result should be show €œ-€œ in the cell from
F18 to F26. However, for the rest of cell (F18 to F26) shown #NUM!. At the
beginning, I thought it is because it is a Greek symbol, so the formula
cannot recognize it, but in fact it is not the case. I just want the formula
show €œ-" in the cell from F18 to F26. Valko, I have spent a few day study
the relationship between the formula and the data I set in the database, but
still cannot work perfectly. I really cannot figure out the same formula can
work perfectly in last example we discuss, but cannot work so nicely in above
situation. Do you think because in my previous example, the database is
started from the cell A, and the database this time started from F5, so the
formula cannot work so perfectly. I really wish you can help out.

Thanks,
Wilchong
2009.09.04








T. Valko wrote:
In ther words, you want a dash "-" instead of a blank...

Just change this portion:

=IF(ROWS(B$1:B1)COUNTIF(A$1:A$10,"<-"),"",

To:

=IF(ROWS(B$1:B1)COUNTIF(A$1:A$10,"<-"),"-",

Thank Valko,
That is the array formula I looking for, however, how to re-adjust the

[quoted text clipped - 32 lines]
Thanks for your advice,
Wilchong


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

  #25   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Seeking Improvement on excel function

Below is the formula I put from the cell of F17 to F26:
=IF(ROWS(F$17:F17)COUNTIF(F$5:F$14,"<-"),"-",INDEX(F$5:F$14,SMALL(IF(F$5:F

$14<"",ROW(F$5:F$14)),ROWS(F$17:F17))-ROW(F$5)+1))

That formula returns "-" in every cell. You have a slight typo in the

SMALL(IF(F$5:F$14<""

Should be:

SMALL(IF(F$5:F$14<"-"

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:9b971b4fb2c54@uwe...
Dear T. Valko,
Thanks for your suggested formula! Your formula is working very perfect.
However, I have a minor problem which I put the same formula in different
cell, the result turn out slightly differently despite how I revise the
formula.

First of all, let me explain the situation, below is the database. You
will
find a Greek symbol (alpha) in the cell of F10 as below.
............F
5... -
6... -
7... -
8... -
9... -
10.... ?
11... -
12..... -
13.... -
14..... -

OK, the second thing I did was put (entered by "Shift + Control + Enter")
your suggest formula in the cell of F17 and drag the formula to F26.

Below is the formula I put from the cell of F17 to F26:
=IF(ROWS(F$17:F17)COUNTIF(F$5:F$14,"<-"),"-",INDEX(F$5:F$14,SMALL(IF(F$5:F
$14<"",ROW(F$5:F$14)),ROWS(F$17:F17))-ROW(F$5)+1))

The result turns out not so perfect as before. The first result is ok,
because the Greek symbol shown in the cell of F17, that is what I want, no
problem about that. The second result should be show "-" in the cell from
F18 to F26. However, for the rest of cell (F18 to F26) shown #NUM!. At
the
beginning, I thought it is because it is a Greek symbol, so the formula
cannot recognize it, but in fact it is not the case. I just want the
formula
show "-" in the cell from F18 to F26. Valko, I have spent a few day
study
the relationship between the formula and the data I set in the database,
but
still cannot work perfectly. I really cannot figure out the same formula
can
work perfectly in last example we discuss, but cannot work so nicely in
above
situation. Do you think because in my previous example, the database is
started from the cell A, and the database this time started from F5, so
the
formula cannot work so perfectly. I really wish you can help out.

Thanks,
Wilchong
2009.09.04








T. Valko wrote:
In ther words, you want a dash "-" instead of a blank...

Just change this portion:

=IF(ROWS(B$1:B1)COUNTIF(A$1:A$10,"<-"),"",

To:

=IF(ROWS(B$1:B1)COUNTIF(A$1:A$10,"<-"),"-",

Thank Valko,
That is the array formula I looking for, however, how to re-adjust the

[quoted text clipped - 32 lines]
Thanks for your advice,
Wilchong


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





  #26   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 90
Default Seeking Improvement on excel function

Dear Valko,
Yes, you are right, I already tried that. But the result turns out the Greek
Sign shown in the cell of F22.

But I want the Greek Sign show in F17 and the rest of the cell show "-".
Therefore, I revise your formula slightly! So I am thinking how to revise
the formula in order to achieve show the Greek Sign in F17 and the rest of
the cell show "-".

Many thanks.
Wilchong





T. Valko wrote:
Below is the formula I put from the cell of F17 to F26:
=IF(ROWS(F$17:F17)COUNTIF(F$5:F$14,"<-"),"-",INDEX(F$5:F$14,SMALL(IF(F$5:F

$14<"",ROW(F$5:F$14)),ROWS(F$17:F17))-ROW(F$5)+1))

That formula returns "-" in every cell. You have a slight typo in the

SMALL(IF(F$5:F$14<""

Should be:

SMALL(IF(F$5:F$14<"-"

Dear T. Valko,
Thanks for your suggested formula! Your formula is working very perfect.

[quoted text clipped - 64 lines]
Thanks for your advice,
Wilchong


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

  #27   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Seeking Improvement on excel function

The formula works properly when I try it. (after making that change I
mentioned.)

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:9b97eb0f51c34@uwe...
Dear Valko,
Yes, you are right, I already tried that. But the result turns out the
Greek
Sign shown in the cell of F22.

But I want the Greek Sign show in F17 and the rest of the cell show "-".
Therefore, I revise your formula slightly! So I am thinking how to revise
the formula in order to achieve show the Greek Sign in F17 and the rest of
the cell show "-".

Many thanks.
Wilchong





T. Valko wrote:
Below is the formula I put from the cell of F17 to F26:
=IF(ROWS(F$17:F17)COUNTIF(F$5:F$14,"<-"),"-",INDEX(F$5:F$14,SMALL(IF(F$5:F

$14<"",ROW(F$5:F$14)),ROWS(F$17:F17))-ROW(F$5)+1))

That formula returns "-" in every cell. You have a slight typo in the

SMALL(IF(F$5:F$14<""

Should be:

SMALL(IF(F$5:F$14<"-"

Dear T. Valko,
Thanks for your suggested formula! Your formula is working very perfect.

[quoted text clipped - 64 lines]
Thanks for your advice,
Wilchong


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



  #28   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 90
Default Seeking Improvement on excel function

OK, may be I change to another spreadsheet, I wish it will be ok this time!
Thanks,
Wilchong




T. Valko wrote:
The formula works properly when I try it. (after making that change I
mentioned.)

Dear Valko,
Yes, you are right, I already tried that. But the result turns out the

[quoted text clipped - 26 lines]
Thanks for your advice,
Wilchong


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Suggested Improvement to Excel Filter Drop-Down THendr2929 Excel Discussion (Misc queries) 1 November 13th 08 07:38 PM
Excel novice seeking guidance Arch Excel Discussion (Misc queries) 1 October 13th 08 05:07 PM
Seeking an Excel guru in the Boston area - EXC102706 doc_rudolph Excel Discussion (Misc queries) 0 October 27th 06 01:59 PM
Excel Improvement Suggestion Carol Excel Worksheet Functions 1 January 25th 06 10:56 PM
Timer function needs improvement Mike K Excel Worksheet Functions 0 July 17th 05 05:35 AM


All times are GMT +1. The time now is 12:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"