search for "1" in text cell which may include "10, "11", & "12"

Options

The text in a cell could be "1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12" or just "1" or "11, 12" or "2, 3, 4, 5"... depending on when we started and finished tracking Bank / CC statements for a particular store.

There are cells with the months that we have statements for and the months we should have statements for, I am trying to compare them and return the months we don't have statements for.

However, my formula, see below, which is duplicated for each month of the year will have issues eventually when Oct, Nov, & Dec 2023 comes around or if we were to go back to Jan & Feb in 2022 since the contains will not tell the difference between a "1" and a "10", "11", "12" or a "2" and a "12". It would be nice if I could somehow compare arrays and return only the unique text, perhaps using Count / Join / Distinct / Collect

=IF(CONTAINS(9, [Months we should have for 2022]@row), IF(CONTAINS(9, [Months we have for 2022]@row), "", 9), "")

This is currently how I join them together in the Months we Don't have for the year 2022...

=JOIN(COLLECT([9/22]@row:[12/22]@row, [9/22]@row:[12/22]@row, @cell <> ""), ", ")

Also Ideas:

Across Smartsheet Variables like the summary variables in a smartsheet for workspaces & global.

Summary Reports / Reports contain great information from multiple smartsheets, but often I have to copy this report to a smartsheet in order to access it / better manipulate it with conditional formatting or export to excel and reimport where I want it. I would like it if I could link multiple smartsheet summary fields to a smartsheet, for a summary smartsheet, but there is no option to do so.

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    If you switch over to multi-select dropdown type columns and replace comma delimiters with line break delimiters CHAR(10) then you should be able to swap out your CONTAINS function for a HAS function which will be able to tell the difference between "1" and "10".

  • Ben Taylor
    Ben Taylor ✭✭
    Answer ✓
    Options

    Never mind figured it out.


    =IF(HAS([Column57]@row, 1), IF(HAS([Column59]@row, 1), "", 1 + CHAR(10)), "") + IF(HAS([Column57]@row, 2), IF(HAS([Column59]@row, 2), "", 2 + CHAR(10)), "") + IF(HAS([Column57]@row, 3), IF(HAS([Column59]@row, 3), "", 3 + CHAR(10)), "") + IF(HAS([Column57]@row, 4), IF(HAS([Column59]@row, 4), "", 4 + CHAR(10)), "") + IF(HAS([Column57]@row, 5), IF(HAS([Column59]@row, 5), "", 5 + CHAR(10)), "") + IF(HAS([Column57]@row, 6), IF(HAS([Column59]@row, 6), "", 6 + CHAR(10)), "") + IF(HAS([Column57]@row, 7), IF(HAS([Column59]@row, 7), "", 7 + CHAR(10)), "") + IF(HAS([Column57]@row, 8), IF(HAS([Column59]@row, 8), "", 8 + CHAR(10)), "") + IF(HAS([Column57]@row, 9), IF(HAS([Column59]@row, 9), "", 9 + CHAR(10)), "") + IF(HAS([Column57]@row, 10), IF(HAS([Column59]@row, 10), "", 10 + CHAR(10)), "") + IF(HAS([Column57]@row, 11), IF(HAS([Column59]@row, 11), "", 11 + CHAR(10)), "") + IF(HAS([Column57]@row, 12), IF(HAS([Column59]@row, 12), "", 12 + CHAR(10)), "")

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    If you switch over to multi-select dropdown type columns and replace comma delimiters with line break delimiters CHAR(10) then you should be able to swap out your CONTAINS function for a HAS function which will be able to tell the difference between "1" and "10".

  • Ben Taylor
    Options

    Using the Char(10) and multiselect dropdown 1-12... I came up with a formula to compare 2 cells for 1-12, but sometimes it returns 03 or 06 instead of 3 or 6, not sure why...

    =IF(HAS([Column57]@row, 1), IF(HAS([Column59]@row, 1), "", 1 + CHAR(10))) + IF(HAS([Column57]@row, 2), IF(HAS([Column59]@row, 2), "", 2 + CHAR(10))) + IF(HAS([Column57]@row, 3), IF(HAS([Column59]@row, 3), "", 3 + CHAR(10))) + IF(HAS([Column57]@row, 4), IF(HAS([Column59]@row, 4), "", 4 + CHAR(10))) + IF(HAS([Column57]@row, 5), IF(HAS([Column59]@row, 5), "", 5 + CHAR(10))) + IF(HAS([Column57]@row, 6), IF(HAS([Column59]@row, 6), "", 6 + CHAR(10))) + IF(HAS([Column57]@row, 7), IF(HAS([Column59]@row, 7), "", 7 + CHAR(10))) + IF(HAS([Column57]@row, 8), IF(HAS([Column59]@row, 8), "", 8 + CHAR(10))) + IF(HAS([Column57]@row, 9), IF(HAS([Column59]@row, 9), "", 9 + CHAR(10))) + IF(HAS([Column57]@row, 10), IF(HAS([Column59]@row, 10), "", 10 + CHAR(10))) + IF(HAS([Column57]@row, 11), IF(HAS([Column59]@row, 11), "", 11 + CHAR(10))) + IF(HAS([Column57]@row, 12), IF(HAS([Column59]@row, 12), "", 12 + CHAR(10)))


  • Ben Taylor
    Ben Taylor ✭✭
    edited 03/29/23
    Options

    Adding Char(10) and Multiselect Dropdown, but not sure why it is returning 0, 03, 06, 011, 012, 010, 09 in other areas... Perhaps it's picking up "" as a 0? Seems the last number of a multiselect often has the issue.

    =IF(HAS([Column57]@row, 1), IF(HAS([Column59]@row, 1), "", 1 + CHAR(10))) + IF(HAS([Column57]@row, 2), IF(HAS([Column59]@row, 2), "", 2 + CHAR(10))) + IF(HAS([Column57]@row, 3), IF(HAS([Column59]@row, 3), "", 3 + CHAR(10))) + IF(HAS([Column57]@row, 4), IF(HAS([Column59]@row, 4), "", 4 + CHAR(10))) + IF(HAS([Column57]@row, 5), IF(HAS([Column59]@row, 5), "", 5 + CHAR(10))) + IF(HAS([Column57]@row, 6), IF(HAS([Column59]@row, 6), "", 6 + CHAR(10))) + IF(HAS([Column57]@row, 7), IF(HAS([Column59]@row, 7), "", 7 + CHAR(10))) + IF(HAS([Column57]@row, 8), IF(HAS([Column59]@row, 8), "", 8 + CHAR(10))) + IF(HAS([Column57]@row, 9), IF(HAS([Column59]@row, 9), "", 9 + CHAR(10))) + IF(HAS([Column57]@row, 10), IF(HAS([Column59]@row, 10), "", 10 + CHAR(10))) + IF(HAS([Column57]@row, 11), IF(HAS([Column59]@row, 11), "", 11 + CHAR(10))) + IF(HAS([Column57]@row, 12), IF(HAS([Column59]@row, 12), "", 12 + CHAR(10)))

  • Ben Taylor
    Ben Taylor ✭✭
    Answer ✓
    Options

    Never mind figured it out.


    =IF(HAS([Column57]@row, 1), IF(HAS([Column59]@row, 1), "", 1 + CHAR(10)), "") + IF(HAS([Column57]@row, 2), IF(HAS([Column59]@row, 2), "", 2 + CHAR(10)), "") + IF(HAS([Column57]@row, 3), IF(HAS([Column59]@row, 3), "", 3 + CHAR(10)), "") + IF(HAS([Column57]@row, 4), IF(HAS([Column59]@row, 4), "", 4 + CHAR(10)), "") + IF(HAS([Column57]@row, 5), IF(HAS([Column59]@row, 5), "", 5 + CHAR(10)), "") + IF(HAS([Column57]@row, 6), IF(HAS([Column59]@row, 6), "", 6 + CHAR(10)), "") + IF(HAS([Column57]@row, 7), IF(HAS([Column59]@row, 7), "", 7 + CHAR(10)), "") + IF(HAS([Column57]@row, 8), IF(HAS([Column59]@row, 8), "", 8 + CHAR(10)), "") + IF(HAS([Column57]@row, 9), IF(HAS([Column59]@row, 9), "", 9 + CHAR(10)), "") + IF(HAS([Column57]@row, 10), IF(HAS([Column59]@row, 10), "", 10 + CHAR(10)), "") + IF(HAS([Column57]@row, 11), IF(HAS([Column59]@row, 11), "", 11 + CHAR(10)), "") + IF(HAS([Column57]@row, 12), IF(HAS([Column59]@row, 12), "", 12 + CHAR(10)), "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!