Excel Function

  • Thread starter Thread starter Dries
  • Start date Start date
D

Dries

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?
 
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
 
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.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
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.
 
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
 
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))))
Click to expand...

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

---
 
Back
Top