Get unique values using for particular status in a single column

[Asheesh]

Hi Guys,

I am unable to attach the file so pasting data below

need your help to extract unique values basis 2 or more criteria in the same column..

Formula used in Column G to extract Unique of concat if status is b : IFERROR(INDEX(concatrng,MATCH(0,COUNTIF($F$1:F1,concatrng)+(statusrng<>"b"),0)),"")

Array Entered

Formula in Column G : IFERROR(INDEX(concatrng,MATCH(0,COUNTIF($G$1:G1,concatrng)+(statusrng<>"b")+(statusrng<>"d"),0)),"") this doesnt fetch any result..



A
number

B
status
C
date
D
name
E
concat
F
Unique of concat if status is b
G
unique of concat if status is b or d
1 a 8/27/2014 ash ash41878a1 nash41877b2
2 b 8/26/2014 nash nash41877b2 sunil41874b4
2 c 8/25/2014 vijay vijay41876c2 bala41872b5
3 a 8/24/2014 kamal kamal41875a3 peter41868b8
4 b 8/23/2014 sunil sunil41874b4 david41865b11
5 c 8/22/2014 sam sam41873c5 sachin41862b13
5 b 8/21/2014 bala bala41872b5
6 c 8/20/2014 rony rony41871c6
7 d 8/19/2014 renu renu41870d7
7 c 8/18/2014 shailu shailu41869c7
8 b 8/17/2014 peter peter41868b8
9 d 8/16/2014 michelle michelle41867d9
10 a 8/15/2014 kevin kevin41866a10
11 b 8/14/2014 david david41865b11
12 c 8/13/2014 abhay abhay41864c12
13 a 8/12/2014 ramani ramani41863a13
13 b 8/11/2014 sachin sachin41862b13
13 c 8/10/2014 vikas vikas41861c13
14 d 8/9/2014 khush khush41860d14
15 c 8/8/2014 dev dev41859c15
16 a 8/7/2014 danny danny41858a16

Appreciate your help in advance...

 

[srinidhi]

Hi,

Please find attached the excel. Hope this is what you were looking for.

 

[Asheesh]

Hi,

Please find attached the excel. Hope this is what you were looking for.

thanks Srinidhi..for your quick response...but unfortunately..this is not exactly what i am looking for...

Please see the attached for your reference...

 

[Somendra Misra]

Hi Asheesh,

Try below array formula with Ctrl+Shift+Enter:

=INDEX(concatrng,SMALL(IF(FREQUENCY(IFERROR(MATCH(IF((statusrng="b")+(statusrng="d"),concatrng),concatrng,0),"e"),ROW(concatrng)-ROW($E$2)+1),ROW(concatrng)-ROW($E$2)+1),ROWS(H$2:H2)))
in H2 and copy down.

Regards,

 

[Asheesh]

@Somendra Misra

Thanks a lot....superb..

Regards
Asheesh

 

[Somendra Misra]

You are welcome @Asheesh

Regards,