Unique list from a list of duplicates based on a condition

[Shweta]

Hi All,

I have two columns, one with advisors name and other with their batch number. Say, column A and column B.

ColA	ColB
Shweta	batch 21
Shweta	batch 21
Sweety	batch 20
Sweety	batch 20
Bodhika	batch 21
Rahul	batch 21
Bodhika	batch 21
Rahul	batch 21
Bodhika	batch 21

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

In cell C1, I have a data validation list having batch numbers

In column D, I want the unique names from col A based on the selection in Cell C1. For example if I select batch 21 in validation list, I want all the unique name from col A in column D...and same for all batches.

Note: - list is dynamic.

Please help me with a excel formula for the same.

Thanks in advance!

Regards,
Shweta

 

[Weazel]

something like........

Excel 2010

	A	B	C	D
1	Name	Batch	batch 21	Extract
2	weazel	batch 21		weazel
3	weazel	batch 21		Bodhika
4	Sweety	batch 20		Rahul
5	Sweety	batch 20		joe
6	Bodhika	batch 20		test 2
7	Rahul	batch 21
8	Bodhika	batch 21
9	Rahul	batch 21
10	Bodhika	batch 21
11	joe	batch 21
12	test	batch 20
13	test 2	batch 21
14
15
16
17
18
19
20

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula D2 {=IFERROR(INDEX($A$2:$A$20,SMALL(IF(FREQUENCY(IF($B$2:$B$20=$C$1,MATCH($A$2:$A$20,$A$2:$A$20,0)),ROW($A$2:$A$20)-ROW($A$2)+1),ROW($A$2:$A$20)-ROW($A$2)+1),ROWS($D$2:D2))),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[OTOTO]

Nice reply Weazel. This is a great solution.

something like........

Excel 2010

	A	B	C	D
1	Name	Batch	batch 21	Extract
2	weazel	batch 21		weazel
3	weazel	batch 21		Bodhika
4	Sweety	batch 20		Rahul
5	Sweety	batch 20		joe
6	Bodhika	batch 20		test 2
7	Rahul	batch 21
8	Bodhika	batch 21
9	Rahul	batch 21
10	Bodhika	batch 21
11	joe	batch 21
12	test	batch 20
13	test 2	batch 21
14
15
16
17
18
19
20

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula D2 {=IFERROR(INDEX($A$2:$A$20,SMALL(IF(FREQUENCY(IF($B$2:$B$20=$C$1,MATCH($A$2:$A$20,$A$2:$A$20,0)),ROW($A$2:$A$20)-ROW($A$2)+1),ROW($A$2:$A$20)-ROW($A$2)+1),ROWS($D$2:D2))),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[AlKey]

Or you can use this regular (non-array formula)

in D2

=IFERROR(INDEX($A$2:$A$20,MATCH(0,INDEX(COUNTIF($D$1:D1,$A$2:$A$20),,),)),"")