Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Trying to get a list of distinct values across multiple columns based on multiple criteria

edited 02/17/25 in Formulas and Functions

Hi,

I am trying to list all the distinct values for the Year 1 through Year 4 columns but only for the rows the WBS row value is equal to WBS Target value and skip any blank cells. I have a helper column to index up to 10 distinct values (Screen shot below) Any help is greatly appreciated!

Best Answers

  • Employee
    edited 02/18/25 Answer ✓

    Hi @Jpress,

    I think I’ve got it! The issue with adding the IF statement at the beginning of the formula is as you’ve seen - that results are only provided on rows where the WBS is 123. We therefore need to move our “WBS is equal to 123” criterion inside of the COLLECT function. However, ranges used within the same function must be the same size (check out this link for more information), so it’s not possible to use the range of 4 columns within the Collect function as well as a range of 1 column (the WBS column).

    To get around this, we’ll need to create columns for each year that list the distinct values for those years, but only when the WBS value is 123, and then we can use a formula in another column that indexes those values and lists them. 

    I’ll break down the steps I took to achieve this:

    1. Create 4 columns named “Year 1 distinct” to “Year 4 distinct”.
    2. Use the following formula in the columns, changing every instance of the Year number in each formula (so the formula in the Year 2 distinct will have the same formula but will reference the Year 2 column each time instead of the Year 1 column):
      1. =IFERROR(INDEX(DISTINCT(COLLECT([Year 1]:[Year 1], [Year 1]:[Year 1], @cell <> "", WBS:WBS, @cell = 123)), Helper@row), "")
    3. Convert the formulas to column formulas.
    4. In your List of Distinct years column, use the following formula:
      1. =IFERROR(INDEX(DISTINCT(COLLECT([Year 1 distinct]:[Year 4 distinct], [Year 1 distinct]:[Year 4 distinct], <>"")), Helper@row), "")
      2. Your sheet would then look something like this:
    5. You can then hide the Year 1 distinct to Year 4 distinct columns since they don’t need to be visible for the final formula to work, and your sheet will end up looking like this:


    I hope that’s what you’re looking for! Let me know if we’re still not quite there or you have any more questions!

    Georgie

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Employee
    edited 02/19/25 Answer ✓

    Hey @Jpress,

    We can do that by adding in the CONTAINS function to the formulas in the Year 1 Distinct to Year 4 Distinct columns. However, we’ll need one more helper column to convert the WBS value to text, as CONTAINS does not work with numbers, only text strings (I used Paul’s comment here to help with this one - thanks @Paul Newcome!

    So, next steps are as follows:

    1. Create a helper column named “WBS - Text”.
    2. In the "WBS - Text" column, use the formula =WBS@row + “” and convert to column formula.
    3. Edit the existing formulas in the Year 1 Distinct to Year 4 Distinct columns so that they follow this base formula (I’ve made the section that’s changed bold so you can copy and paste this into the relevant spot in all 4 formulas):
      1. =IFERROR(INDEX(DISTINCT(COLLECT([Year 1]:[Year 1], [Year 1]:[Year 1], @cell <> "", [WBS - Text]:[WBS - Text], CONTAINS("123", @cell))), Helper@row), "")
    4. Hide the “WBS - Text” column and re-hide the Year 1 Distinct to Year 4 Distinct columns.

    That should do it - let me know!

    Georgie

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Employee
    Answer ✓

    Hi @Jpress,

    That’s right, you can’t use absolute references such as $1 in column formulas. You could drag-fill the WBS target value down to all existing rows and then autofill will automatically populate new rows. 

    However, my suggestion would be to create a sheet summary field and have users enter the WBS Target Value there, then reference that field in the formulas.

    Your formula would then be:

    • =IFERROR(INDEX(DISTINCT(COLLECT([Year 1]:[Year 1], [Year 1]:[Year 1], @cell <> "", [WBS - Text]:[WBS - Text], CONTAINS([WBS Target]#, @cell))), Helper@row), "")

    You’ll then be able to delete the WBS Target Value column from the sheet, since we have now moved the value to the sheet summary field. Your sheet should then look something like this:

    Does that work for you?

    Georgie

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Employee

    Hi @Jpress,

    You should be able to achieve this using a combination of the IF, JOIN, and DISTINCT functions. 

    Try this:

    • =IF(WBS@row = [WBS Target Value]@row, JOIN(DISTINCT([Year 1]@row:[Year 4]@row), ", "))

    Does that work for you?

    Georgie

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Hi @Georgie ,

    Thank you for taking a look at this. That is not quite what I am looking for. While your formula will return the list of unique values in the row for each row with WBS = to 123, what I am really looking for is the unique values listed in the whole range but only for the items in that range that have the WBS number of 123.

    I think it will be some variation of INDEX(DISTINCT(COLLECT( with the helper column there to list the first unique value, then the second, third and so on. Maybe there is an if statement I could use in conjunction with the index - distinct - collect? Thanks again!

  • @Georgie =IFERROR(INDEX(DISTINCT(COLLECT([Year 1]:[Year 4], [Year 1]:[Year 4], @cell <> "")), Helper@row), "") does exactly what I want with the exception that it lists all unique values from the range. Do you have any recommendations on how to update it to only consider rows in the range whose WBS number is equal to 123? Thank you!

  • Employee

    Hi @Jpress,

    Ah, well done for getting that far! How about adding the IF statement before the INDEX function, like this?:

    • =IFERROR(IF(WBS@row = 123, INDEX(DISTINCT(COLLECT([Year 1]:[Year 4], [Year 1]:[Year 4], @cell <> "")), Helper@row)), "")

    Hope that works for you!

    Georgie

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • @Georgie we are so close! Adding this if statement helps but as you will see below, there are 4 unique values in the range but only 2 are getting listed. It seems the If statement is limiting the index to only the rows that the WBS element is equal to 123.

  • Employee
    edited 02/18/25 Answer ✓

    Hi @Jpress,

    I think I’ve got it! The issue with adding the IF statement at the beginning of the formula is as you’ve seen - that results are only provided on rows where the WBS is 123. We therefore need to move our “WBS is equal to 123” criterion inside of the COLLECT function. However, ranges used within the same function must be the same size (check out this link for more information), so it’s not possible to use the range of 4 columns within the Collect function as well as a range of 1 column (the WBS column).

    To get around this, we’ll need to create columns for each year that list the distinct values for those years, but only when the WBS value is 123, and then we can use a formula in another column that indexes those values and lists them. 

    I’ll break down the steps I took to achieve this:

    1. Create 4 columns named “Year 1 distinct” to “Year 4 distinct”.
    2. Use the following formula in the columns, changing every instance of the Year number in each formula (so the formula in the Year 2 distinct will have the same formula but will reference the Year 2 column each time instead of the Year 1 column):
      1. =IFERROR(INDEX(DISTINCT(COLLECT([Year 1]:[Year 1], [Year 1]:[Year 1], @cell <> "", WBS:WBS, @cell = 123)), Helper@row), "")
    3. Convert the formulas to column formulas.
    4. In your List of Distinct years column, use the following formula:
      1. =IFERROR(INDEX(DISTINCT(COLLECT([Year 1 distinct]:[Year 4 distinct], [Year 1 distinct]:[Year 4 distinct], <>"")), Helper@row), "")
      2. Your sheet would then look something like this:
    5. You can then hide the Year 1 distinct to Year 4 distinct columns since they don’t need to be visible for the final formula to work, and your sheet will end up looking like this:


    I hope that’s what you’re looking for! Let me know if we’re still not quite there or you have any more questions!

    Georgie

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Hi @Georgie

    After testing this formula yesterday, I am pleased to report, you nailed it! Thank you for the assistance, very helpful. I am now working on implementing the learnings from the test sheet into my live system. I'll let you know if any additional related questions come up. Thanks again!

  • Employee

    Hi @Jpress,

    Glad to hear it, and glad we finally reached the right solution! Thanks for confirming it worked for you 😃

    Georgie

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Hi @Georgie,

    I am adding another layer of complexity to the formula and running into some trouble. In the 2nd collect criteria, I would like to make the WBS criteria equal to an input value in row 1 of the WBS Target Value Column AND collect anything that contains that target value. See below:

    Any thoughts? Thanks again!

  • Employee
    edited 02/19/25 Answer ✓

    Hey @Jpress,

    We can do that by adding in the CONTAINS function to the formulas in the Year 1 Distinct to Year 4 Distinct columns. However, we’ll need one more helper column to convert the WBS value to text, as CONTAINS does not work with numbers, only text strings (I used Paul’s comment here to help with this one - thanks @Paul Newcome!

    So, next steps are as follows:

    1. Create a helper column named “WBS - Text”.
    2. In the "WBS - Text" column, use the formula =WBS@row + “” and convert to column formula.
    3. Edit the existing formulas in the Year 1 Distinct to Year 4 Distinct columns so that they follow this base formula (I’ve made the section that’s changed bold so you can copy and paste this into the relevant spot in all 4 formulas):
      1. =IFERROR(INDEX(DISTINCT(COLLECT([Year 1]:[Year 1], [Year 1]:[Year 1], @cell <> "", [WBS - Text]:[WBS - Text], CONTAINS("123", @cell))), Helper@row), "")
    4. Hide the “WBS - Text” column and re-hide the Year 1 Distinct to Year 4 Distinct columns.

    That should do it - let me know!

    Georgie

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Employee

    Hi @Jpress,

    Actually, we can go one step further, which I think is what you’re really looking for. If the WBS Target Value will be present in every row, you can reference that column in the formula, so your base formula would be:

    • =IFERROR(INDEX(DISTINCT(COLLECT([Year 1]:[Year 1], [Year 1]:[Year 1], @cell <> "", [WBS - Text]:[WBS - Text], CONTAINS([WBS Target Value]@row, @cell))), Helper@row), "")

    Use this to update the formulas in Year 1 Distinct to Year 4 Distinct columns, and then you should end up with the same results for all rows containing “123” in the WBS column.

    Cheers,

    Georgie

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Hi @Georgie ,

    Ah ha, I did not realize that contains only works with text, that is helpful to know and was definitely part of my problem. Thank you for pointing that out. We are so close again, the last part is that the WBS Target Value won't be on every row. The idea is that the user would enter it in 1 cell on 1 row. The formula should stay fixed on that 1 cell for the evaluation.

    I tried replacing @row with $1 but I get a syntax error when converting it to the column formula.

  • Employee
    Answer ✓

    Hi @Jpress,

    That’s right, you can’t use absolute references such as $1 in column formulas. You could drag-fill the WBS target value down to all existing rows and then autofill will automatically populate new rows. 

    However, my suggestion would be to create a sheet summary field and have users enter the WBS Target Value there, then reference that field in the formulas.

    Your formula would then be:

    • =IFERROR(INDEX(DISTINCT(COLLECT([Year 1]:[Year 1], [Year 1]:[Year 1], @cell <> "", [WBS - Text]:[WBS - Text], CONTAINS([WBS Target]#, @cell))), Helper@row), "")

    You’ll then be able to delete the WBS Target Value column from the sheet, since we have now moved the value to the sheet summary field. Your sheet should then look something like this:

    Does that work for you?

    Georgie

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • @Georgie Yes. Looks great. Thank you again! This has been fantastic!

  • Employee

    @Jpress Brilliant! So glad we got this working for you ☺️

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

  • I have a pretty basic sheet that I am using to develop a formula, and am encountering an issue I haven't seen before. The formula isn't including my second row for some reason. It isn't a huge issue f…
    User: "jjg279"
    Answered ✓
    9
    2
  • I'm sure that this is an easy fix but I've tried a bunch of different ways and can't get this to work. I need to get the max date from a sheet to feed it into my meta data sheet (dates are stored in d…
    User: "susanmgfin"
    Answered ✓
    8
    2
  • I need help to come up with the risk formula. I tried many different formulas and tried to modify it but just can't seem to have desired results. So these are the conditions I MUST meet: Program is a …
    User: "Ronak"
    Answered ✓
    29
    6