google-sheetsgoogle-sheets-apigoogle-sheets-formula

How to get a list of unique unknown words from a google sheet

I am creating a google sheet to analyse a bunch of keywords from a google sheet. https://gyazo.com/4db16f1db553c4d45881fef909340d59 The keywords came in cells of text which I broke up using the split function.

What I want to create is a list of unique keywords from this sheet.

I tried using the Unique function, but it only checks a row for unique values and not a sheet. The screenshot below is of my failed attempt. https://gyazo.com/8aa1a84dec385b6cad635774769f8c23

Any feedback would be greatly appreciated.

Solution

  • Try this: 

    =ArrayFormula(unique(transpose(split(concatenate("|"&A:Z),"|"))))

    Explanation: concatenate("|"&A:Z) (combined with ArrayFormula) creates a list of all the values in there, prefixed with "|" as separator.

    Then the split() function separates all those values again, based on the same delimited, in a single row.

    transpose() ensures you have one row per value, instead of columns, since the unique() function will consider entire rows (now, each row is one column wide).