Extract text from a column ignoring duplicates and blanks

[Rodrigues]

Hi There
Have a column with 7000 + records (text), would like to extract to another column, a list with text strings ignoring duplicates and blanks cells.
I'm using on cell C2 a formula but doesn't return what I need.
File attached.
Thanks in advance.
Regards
R

 

[Rodrigues]

With file

 

[bosco_yip]

In C2 (non-array formula), copied down :

=IFERROR(INDEX($A$2:$A$7272,MATCH(0,INDEX(COUNTIF(C$1:C1,$A$2:$A$7272&""),0),0)),"")

Regards
Bosco

 

[Khalid NGO]

Hi,

PQ solution if interested.

All done via GUI.
Convert your data into Table, Go to Data > Get & Transform > From Table
Remove Duplicate
Remove Blank Rows
Close & Load

let

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Desc", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type"),
    #"Removed Blank Rows" = Table.SelectRows(#"Removed Duplicates", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Removed Blank Rows"

Regards,

 

[Rodrigues]

Thank you all.

 

[rahulshewale1]

hii @Rodrigues,

if have office 365 subscription then use simple formula


=UNIQUE(FILTER($A$2:$A$37,$A$2:$A$37<>""))


Regard
Rahul shewale

 

[Rodrigues]

Thanks, unfortunately don't have office 365, only 2010.