Using DISTINCT function

2»

Answers

  • Hi guys,


    Super frusterated right now. this formula we came up with worked fine until today. I don't believe I have done anything diufferent, but somehow it is coming up blank when I use it now. There is definitey matching information in the source sheet, but now I get nothing when I try to pull it through. I was revising other formulas in the sheet, but I don't know why that would have affected things. Any ideas about this? Did I break the formula by trying to revise other index/match formulas in other columns in page?


    SUGGESTED. =index(distinct({Range you want to pull from}), Number@row)

    ACTUAL =IFERROR(INDEX(DISTINCT({BARCODE ID}), NUMBER@row), "")


    Screenshot of page that I'm trying to pull data into. This was populating before and now gives me blank in column.


    Source page I am pulling from has plenty of data in the "Barcode" column to pull from, but is not pulling.

    Thanks!

  • Is it because the Barcode column in the Primary column?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Sarah_lee123 Double check the range you are pulling from to see if there are any errors present.


    If there is, then those need to be fixed first. If not, then try removing the IFERROR portion from the formula to see what kind of error is present to make trouble shooting a little easier.

    =INDEX(DISTINCT({BARCODE ID}), NUMBER@row)

  • INVALID DATA TYPE


    I am pulling from the correct sheet with data in it. Sometimes when I come back to the formula, I have to re-reference as if the formula forgets which sheet it is referencing.

  • Seems to work fine when I use a different column for reference. The distinct formula won't accept the barcode, but it will accept a SKU. I might just use the sku instead, but it's not my preferred set up.



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Sarah_lee123 Did you check the entire barcode column to see if that error was present even in a single cell?

  • I think it was just a glitch. After shutting down my computer and coming back to it a couple days later, it worked on its own.


    Thx

  • I'm trying to use the "count(distinct(" formula combination to count the number of unique numbers in a column, but I can't get it to validate the whole column. Even my dropping the column range only returns a value of "1". I need it to look in the whole column.


  • Genevieve P.
    Genevieve P. Employee Admin

    Hey @rhenry11

    The values in your column look to be appearing on the left side of the cell, which indicates that all the values are currently being seen as text, versus numerical. Can you post what formula you're using in that column? We can likely wrap it in a VALUE function to return them as numerical.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!