INDEX - Remove duplicates (ISERROR)

[kkimiri]

Please, i have an array formula that is returning duplicates, on the data worksheet in the attached file the name column is returning duplicates from this formula
=IF(ISERROR(COUNTIF(Sector,$B$4)<ROWS($B$4:B14)),"",INDEX(Transaction_Name,SMALL(IF('Transaction Listings'!$C$2:$C$232=DATA!$B$4,ROW('Transaction Listings'!$C$2:$C$232)),ROW('Transaction Listings'!$C$1))))

Kindly assist on how i can remove this duplicates

 

[NARAYANK991]

Hi ,

Can you attach your workbook ?

Narayan

 

[kkimiri]

Hi ,

Can you attach your workbook ?

Narayan

Please see attached the workbook ...in the sheet called dashboard, once i drag the formula down, it brings in duplicates, how can i remove the duplicates?

 

[NARAYANK991]

Hi ,

See the file.

This is taken from here :

https://www.mrexcel.com/forum/excel-questions/460154-formula-extract-unique-values-array.html

Narayan

 

[bosco_yip]

Another option,



In B9, formula copy down :

=IFERROR(INDEX(DATA!C:C,MATCH(0,INDEX(COUNTIF(B$8:B8,DATA!C$2:C$225)+(DATA!$B$2:$B$225<>E$6),0),0)+1),"")

Regards
Bosco

 

[kkimiri]

Another option,

View attachment 43554

In B9, formula copy down :

=IFERROR(INDEX(DATA!C:C,MATCH(0,INDEX(COUNTIF(B$8:B8,DATA!C$2:C$225)+(DATA!$B$2:$B$225<>E$6),0),0)+1),"")

Regards
Bosco

Thank You so Much. It works perfectly!

 

[kkimiri]

Thank You so Much. It works perfectly!

Another option,

View attachment 43554

In B9, formula copy down :

=IFERROR(INDEX(DATA!C:C,MATCH(0,INDEX(COUNTIF(B$8:B8,DATA!C$2:C$225)+(DATA!$B$2:$B$225<>E$6),0),0)+1),"")

Regards
Bosco

Kindly just another one, on a vlookup formula, how do i return a blank instead of a FALSE . The formula is =VLOOKUP($B8,'STAFF LIST'!$B$4:$T$253,3,FALSE)

I want the formula to return a blank (not FALSE) if cell B8 is blank.

 

[bosco_yip]

Kindly just another one, on a vlookup formula, how do i return a blank instead of a FALSE . The formula is =VLOOKUP($B8,'STAFF LIST'!$B$4:$T$253,3,FALSE)

I want the formula to return a blank (not FALSE) if cell B8 is blank.

Try,

=IF($B8="","",VLOOKUP($B8,'STAFF LIST'!$B$4:$T$253,3,FALSE))

Regards
Bosco

 

[kkimiri]

Try,

=IF($B8="","",VLOOKUP($B8,'STAFF LIST'!$B$4:$T$253,3,FALSE))

Regards
Bosco

Thanks a lot..works well