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

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

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

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

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

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

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

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

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

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