How do I count unique values within a cell and in a column?-CodePudding

I've tried my best with my Google-fu but cannot find the solution.

I am trying to figure out how to get a list of unique values that occur in a column, including values inside a cell. Cells with multiple values are likely to include carriage returns.

For example,

Cell A1: apple apple

Cell A2: apple grape

Cell A3: apple

Cell A4: grape

Cell A5: orange

The equation should only return apple grape orange

Please let me know if this makes sense. Thanks all!

CodePudding user response：

If you want to extract unique words from cells including multiple words in one cell then may try-

=UNIQUE(TEXTSPLIT(TEXTJOIN("|",TRUE,BYROW(A1:A5,LAMBDA(x,SUBSTITUTE(x," ","|")))),,"|"))

To get total unique words count try-

=COUNTA(UNIQUE(TEXTSPLIT(TEXTJOIN("|",TRUE,BYROW(A1:A5,LAMBDA(x,SUBSTITUTE(x," ","|")))),,"|")))

You can simplify the formula to-

=COUNTA(UNIQUE(TEXTSPLIT(TEXTJOIN("|",1,SUBSTITUTE(A1:A5," ","|")),,"|")))

CodePudding user response：

You can also accomplish this using Power Query, available in Windows Excel 2010 and Excel 365 (Windows or Mac).

To use Power Query, follow the steps:

• Select some cell in your Data Table,

• Data Tab => Get&Transform => From Table/Range,

• When the PQ Editor opens: Home => Advanced Editor,

• Make note of the Table Name (Fruitstbl) in Line 2,

• Paste the M Code below in place of what you see.

let
    Source = Excel.CurrentWorkbook(){[Name="Fruitstbl"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruits", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Fruits", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Fruits"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Fruits", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type1")
in
    #"Removed Duplicates"

• Change the Table name as Outputtbl before importing it back into Excel.

• When importing, you can either select Existing Sheet with the cell reference you want to place the table or you can simply click on NewSheet

Once the above is done, then apply the below formula in a cell to get the counts as well,

=COUNTA(Outputtbl[Fruits])