formula needed?

[mahaveer]

Problem: if usr ID 1 is reapeated Twice and degree of both are "yes" then output of degree also "yes",if usr ID 2 repeat twice and correspondence degree one is "yes" another is "no" then output degree is "yes".in the cause both are "no" then o/p is "no" same thing applicable for N/A.


file link:

http://www.2shared.com/file/H_POqXTt/sample1.html

 

[Debraj]

Hi Mahaveer,


Just a normal Confirmation..


So you dont need any special requirement for "Ses_ID_Res",


Below is the data for CASFML user.

[pre]

usr ID	Ses ID	Degree
1	11	Yes
1	12	No
2	13	N/A
2	14	No
3	15	Yes
4	16	No
5	17	No
6	18	Yes
6	19	Yes
7	20	N/A
Expected Output:

Usr_	Ses_	Degree
1	11	Yes
2	14	No
3	15	Yes
4	16	No
5	17	No
6	18	Yes
7	20	N/A

[/pre]
Regards,

Deb

 

[NARAYANK991]

Hi Mahaveer ,


If I assume the following named ranges : USR_IDs , SES_IDs and DEGREES , referring to the ranges $A$2:$A$11 , $B$2:$B$11 and $C$2:$C$11 , then the following formulae will give the result degrees and ses_ids :


=INDEX({"Yes","No","N/A"},MIN(IFERROR(MATCH(IF(USR_IDs=D2,DEGREEs),{"Yes","No","N/A"},0),999)))


=INDEX(Ses_IDs,MATCH(G2&D2,DEGREEs&USR_IDs,0))


Both are array formulae to be entered using CTRL SHIFT ENTER.


Narayan

 

[mahaveer]

thanks narayank for the formula but in which cell i have to paste the formula?

 

[NARAYANK991]

Hi Mahaveer ,


The first formula is to be entered in F2 and copied down ; the second formula is to be entered in E2 and copied down.


I am assuming that the USR_IDs , 1 through 7 , are entered manually ; do you want formulae for this also ?


Narayan


P.S. For the USR_IDs , put in the following formula in D2 , and copy down ; this is also an array formula , to be entered using CTRL SHIFT ENTER :


=INDEX(USR_IDs,MATCH(0,COUNTIF($D$1:D1,USR_IDs),0))

 

[mahaveer]

sorry narayank its not working


can you upload a workbook


Thanks


Regards

CA Mahaveer Somani

 

[Debraj]

Hi Mahaveer..


You need to create NAMES for each range .


https://dl.dropbox.com/u/78831150/Excel/Formula%20Needed%20%28Mahavir%29.xlsx


Regards,

Deb

 

[mahaveer]

Thank you very much narayank and deb

Regard

CA Mahaveer Somani