I need help building a formula

Options

I have a sheet where I have different alpha numerical items listed such as F556 L, and F557 G, as these are completed I want the completed alpha numerical placed in a second column and then a third column that subtracts the second column from the first leaving only the items that are not in the completed column.

My current formula is (=[Outstanding Tags]@row, "Text"-[Cleared tags]@row, "Text"). which is unparseable. This is because it is subtracting mixed numerical and alpha text but I want it to show only F551 A when it works correctly. Do I use a collect formula for this or is there another way?

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options
  • Christopher Flemings
    Options

    Hi Leibel,

    =(COLLECT({All Tags A-L Range 1}, {All Tags A-L Range 1}, AND(HAS([Outstanding Tags]@row, @cell), NOT(HAS([Cleared tags]@row, @cell)))))

    =JOIN(COLLECT({All Tags A-L Range 1}, {All Tags A-L Range 1}, AND(HAS([Outstanding Tags]@row, @cell), NOT(HAS([Cleared tags]@row, @cell))))), ?)

    Something like what I have above with Join but what would my delimiter be?

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    @Christopher Flemings

    CHAR(10) is the delimiter 

  • Christopher Flemings
    Options

    @Leibel Shuchat =JOIN(COLLECT({All Tags A-L Range 1}, {All Tags A-L Range 1}, AND(HAS([Outstanding Tags]@row, @cell), NOT(HAS([Cleared tags]@row, @cell)))), " ")

    This formula works however, when the There is a duplicated alpha number Collected in the Outstanding tags (Which is built by adding multiple entries in the sheet from other columns) and one of the alpha numerical tags is removed in the Cleared Tags. Then neither of these duplicated tags show up in the column this formula is in under "remaining tags"??

    Outstanding tags - Cleared Tags = Remaining Tags?

    Example: F680 D is entered in two separate entries and added together in Outstanding Tags and shows up twice, however when one of the tags clears in the Cleared Tags column it should remove the tag from Remaining tags but leave one of them in that column, but what happens is they both show up in the Outstanding tags and Cleared tags and the one remaining in Outstanding tags does not show up in Remaining Tags but should?? How can I fix this formula so that if there are duplicated entries in the columns collected in Outstanding tags but one or more of the duplicates are removed but still show up in outstanding and should also show up in Remaining tags?

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    @Christopher Flemings

    I don't really understand, can you share screenshots?

    Also, how do you have duplicates when this is a Multi Select (It should automatically remove the duplicates)

  • Christopher Flemings
    Options

    First column is named "Citations that were cited during surveys"=[Tags Cited]@row + [Tags Cited (2)]@row + [Tags Cited (3)]@row + [Tags Cited (4)]@row + [Tags Cited (5)]@row + [Tags Cited (6)]@row + [Tags Cited (7)]@row + [Tags Cited (8)]@row + [Tags Cited (9)]@row + [Tags Cited (10)]@row + [Tags Cited (11)]@row + [Tags Cited (12)]@row + [Tags Cited (13)]@row + [Tags Cited (14)]@row + [Tags Cited (15)]@row + [Tags Cited (16)]@row + [Tags Cited (17)]@row + [Tags Cited (18)]@row + [Tags Cited (19)]@row + [Tags Cited (20)]@row + [Tags Cited (21)]@row + [Tags Cited (22)]@row + [Tags Cited (23)]@row + [Tags Cited (24)]@row + [Tags Cited (25)]@row + [Tags Cited (26)]@row + [Tags Cited (27)]@row + [Tags Cited (28)]@row + [Tags Cited (29)]@row + [Tags Cited (30)]

    Second column is named "Cleared tags" Manually entered tags that are removed from any of the surveys above.

    Third column is named "Tags remaining." =JOIN(COLLECT({All Tags A-L Range 1}, {All Tags A-L Range 1}, AND(HAS([Citations that were cited during surveys]@row, @cell), NOT(HAS([Cleared tags]@row, @cell)))), " ")

    When subtracting tags from column 1 that were manually entered into it, it will remove it from in Column 3 unless it is in column one more than once?


    The first two are multi select columns and the third is a text column because if I make it a multi select the formatting that occurs with that type of column is hard to read..

  • Christopher Flemings
    Options

    @Leibel Shuchat

    First column is named "Citations that were cited during surveys"=[Tags Cited]@row + [Tags Cited (2)]@row + [Tags Cited (3)]@row + [Tags Cited (4)]@row + [Tags Cited (5)]@row + [Tags Cited (6)]@row + [Tags Cited (7)]@row + [Tags Cited (8)]@row + [Tags Cited (9)]@row + [Tags Cited (10)]@row + [Tags Cited (11)]@row + [Tags Cited (12)]@row + [Tags Cited (13)]@row + [Tags Cited (14)]@row + [Tags Cited (15)]@row + [Tags Cited (16)]@row + [Tags Cited (17)]@row + [Tags Cited (18)]@row + [Tags Cited (19)]@row + [Tags Cited (20)]@row + [Tags Cited (21)]@row + [Tags Cited (22)]@row + [Tags Cited (23)]@row + [Tags Cited (24)]@row + [Tags Cited (25)]@row + [Tags Cited (26)]@row + [Tags Cited (27)]@row + [Tags Cited (28)]@row + [Tags Cited (29)]@row + [Tags Cited (30)]

    Second column is named "Cleared tags" Manually entered tags that are removed from any of the surveys above.

    Third column is named "Tags remaining." =JOIN(COLLECT({All Tags A-L Range 1}, {All Tags A-L Range 1}, AND(HAS([Citations that were cited during surveys]@row, @cell), NOT(HAS([Cleared tags]@row, @cell)))), " ")

    When subtracting tags from column 1 that were manually entered into it, it will remove it from in Column 3 unless it is in column one more than once?


    The first two are multi select columns and the third is a text column because if I make it a multi select the formatting that occurs with that type of column is hard to read..

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    @Christopher Flemings

    So you are saying the same TAG can be entered in eg [Tags Cited (5)] and [Tags Cited (19)].

    then you want this o show remaining, unless it has been 'cleared' twice.

    2 Problems:

    1. Your [Cleared tags] column is a Multi Select and therefore will never be able to have 2 of the same TAG.
    2. Even if you switch it to a Text (which would probably be very inconvenient, you would probably need a separate [Complete] Colum for every [Tags Cited] column to check if it is 'Cleared'

    My suggestion is to review your process and why you need these duplicates and come up with a different process so that this would not be an issue.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!