Search code examples
arraysgoogle-sheetsuniquegoogle-sheets-formulaarray-formulas

non-unique function

Here's the sheet: https://docs.google.com/spreadsheets/d/1NCxpNFIdb-DAn4e259KRfPF8o_rv9O9KY4wOdCG6AM8

I managed to get the unique output. Another question, is there any way to get the non-unique output with a formula? I tried to search online but most said not possible :/

So I have a whole column of random numbers+alphabets (Upper and lower cases) like this: 7hyOUGvag1 in column A and I tried to apply unique(A: A) for the whole 4000+ rows in column A but it didn't give me the unique output of it, but instead it gave me duplicates of the same output still. Why? I searched everywhere but everyone seems to have no problem with the simple unique function but for me, it didn't work properly...

Is it because my rows of values are randomized with upper and lower cases alphabets + random numbers too?

Solution

  • try:

    =INDEX(UNIQUE(LOWER(A2:A)))

    0

    the LOWER can be replaced to UPPER or PROPER

    if you don't want to convert it to either of those modes mentioned above try:

    =INDEX(UNIQUE(IFNA(VLOOKUP(A2:A, A2:A, 1, 0))))

    0