Excel Function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dries - 04 Feb 2008 13:02 GMT

Hi Experts,

I have the following problem which I need to solve and have no idea on which
functions I can use:

In sheet one I would like to get a name of a person which has 2 conditions
in sheet two:

Structure of Sheet 2

Name - adress - Condition 1 - tel - condition 2
A - none - A - OK - E
B - None - D - OK - E
C - OK - A OK - C
D - false - D - OK - G
E - n/a - A - OK - E

In the First sheet I would like that in the first line the person who has as:
Condition 1 : A
Condition 2 : E
Result woult be person with name A

Than I would like to insert a line

Find next person with
Condition 1: A
condition 2: E
Result would be person with name E

Any idea if this is possible? and how?

Reply to this Message

CLR - 04 Feb 2008 13:13 GMT

You can obtain the list you want by using the Data > Filter > Autofilter
feature on sheet 2..........then if you need the data moved to sheet 1 just
copy and paste....

Vaya con Dios,
Chuck, CABGx3

> Hi Experts,
>
[quoted text clipped - 26 lines]
>
> Any idea if this is possible? and how?

Reply to this Message

Bob Phillips - 04 Feb 2008 13:13 GMT

Assuming that the values to test for are in M1 and M2, put this in A1 and
copy down

=IF(ISERROR(SMALL(IF((Sheet2!$C$1:$C$20=$M$1)*(Sheet2!$E$1:$E$20=$M$2),ROW($A$1:$A$20),""),ROW($A1))),"",
INDEX(Sheet2!$A$1:$A$20,SMALL(IF((Sheet2!$C$1:$C$20=$M$1)*(Sheet2!$E$1:$E$20=$M$2),ROW($A$1:$A$20),""),ROW($A1:$A20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi Experts,
>
[quoted text clipped - 28 lines]
>
> Any idea if this is possible? and how?

Reply to this Message

Max - 04 Feb 2008 13:26 GMT

One way ..

Data as posted assumed in Sheet2, cols A to E, data from row2 down,
where col C = condition 1, col E = condition 2

In Sheet1,
Put in A2:
=IF(AND(Sheet2!C2="A",Sheet2!E2="E"),ROW(),"")
Leave A1 blank

Put in B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(Sheet2!A:A,SMALL(A:A,ROWS($1:1))))
Select A2:B2, copy down to cover the max expected extent of data in Sheet2.
Minimize/hide away col A. Col B will return the required results all neatly
bunched at the top.

If you want to extract all cols from Sheet2 (not just the names), just copy
B2 across to F2. Then select A2:F2, fill down as far as required.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> In sheet one I would like to get a name of a person which has 2 conditions
> in sheet two:
[quoted text clipped - 21 lines]
>
> Any idea if this is possible? and how?

Reply to this Message

Max - 04 Feb 2008 13:32 GMT

Slight errata to this line:

> Put in B2:
> =IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(Sheet2!A:A,SMALL(A:A,ROWS($1:1))))

It should be

Put in B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(Sheet2!A:A,SMALL($A:$A,ROWS($1:1))))

.. SMALL($A:$A,.. -- col A should be fixed for copying B2 across/down

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Bernard Liengme - 04 Feb 2008 13:28 GMT

On Sheet2 starting in A1, ending in F6 I have
name address t1 phone t2 comb
A none A OK E AE
B None D OK E DE
C OK A OK C AC
D false D OK G DG
E n/a A OK E AE

The formula in F2 is =C2&E2
On Sheet1 starting in A1:ending in I have
test1 test2 comb name1 match name2
A E AE A 2 E

The formula in C2 is =A2&B2
In D2: =INDEX(Sheet2!A2:A6,MATCH(C2,Sheet2!F2:F6,0))
In E2: =MATCH(C2,Sheet2!F1:F6,0)
In F2:
INDEX(INDIRECT("Sheet2!A"&E2+1&":A6"),MATCH(C2,INDIRECT("Sheet2!F"&E2+1&":F6"),0))
But a VBA solution might be neater
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

> Hi Experts,
>
[quoted text clipped - 28 lines]
>
> Any idea if this is possible? and how?

Reply to this Message