Formula HELP!

Options
LeoCicio
LeoCicio ✭✭
edited 11/17/23 in Formulas and Functions

I know this is example is from excel but I'm wanting to use this in smartsheet.

I need formula help! In the chart below I need a formula that will look at the information and display them in numerical order and ignore the blanks. So example would be if B2=1 then F2=1 and if C2 is blank but D2 is 1 than G2=1. I want them to line up in order with FGH but be able to shift if one of the BCD cells is blank. Ideally this list would be long consisting of at least 12 different accounts types and say out of the 12 types only 3 are listed they would be resulted in order ignoring any blanks in between. also I'm hoping the formula can ignore columns in between like the second example. I hope I explained that well enough and any help is appreciated!



Best Answer

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

    I made a mistake. My apologies. I was using the 1, 2, 3, in the third portion of the INDEX function because we are referencing a series of columns within a single row, but...

    Using the COLLECT function creates an array that we are actually pulling from which is a vertical listing. That means the 1, 2, 3, should actually be in the second portion of the INDEX function and the third portion of the INDEX function goes unused.


    Try this:

    =IFERROR(INDEX(COLLECT([Account 1 Type]@row:[Account 12 Password]@row, [Account 1 Type]@row:[Account 12 Password]@row, @cell <> ""), 1), "")

    =IFERROR(INDEX(COLLECT([Account 1 Type]@row:[Account 12 Password]@row, [Account 1 Type]@row:[Account 12 Password]@row, @cell <> ""), 2), "")

    =IFERROR(INDEX(COLLECT([Account 1 Type]@row:[Account 12 Password]@row, [Account 1 Type]@row:[Account 12 Password]@row, @cell <> ""), 3), "")

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    I would suggest grouping all of the type columns together if it is only the type columns you want to pull. Then you would use something along the lines of

    =IFERROR(INDEX(COLLECT([Type 1]@row:[Type 3]@row, [Type 1]@row:[Type 3]@row, @cell <> ""), 1, 1), "")


    The above would pull the first non-blank. You would change the second 1 to a 2 to pull the second non-blank, and the second 1 to a 3 to pull the third non-blank.

  • LeoCicio
    Options

    Paul is there a way to do it without grouping? the format I'm trying to go with is ACCOUNT/USER/PASSWORD-ACCOUNT/USER/PASSWORD-ACCOUNT/USER/PASSWORD and at the end the formula would be able to still do what is listed above. If I have to group them I will but trying to avoid it for ease of viewing. Thanks

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    If your results section is also going to be in the acct/user/password format, then you can leave it as is.

  • LeoCicio
    LeoCicio ✭✭
    edited 11/20/23
    Options

    =IFERROR(INDEX(COLLECT([Account 1 Type]@row:[Account 2 Type]@row:[Account 3 Type]@row:[Account 4 Type]@row:[Account 5 Type]@row:[Account 6 Type]@row:[Account 7 Type]@row:[Account 8 Type]@row:[Account 9 Type]@row:[Account 10 Type]@row:[Account 11 Type]@row:[Account 12 Type]@row, [1T]@row:[2T]@row:[3T]@row:[4T]@row:[5T]@row:[6T]@row:[7T]@row:[8T]@row:[9T]@row:[10T]@row:[11T]@row:[12T]@row, @cell <> ""), 1, 1), "")

    This is the formula after adding all the information and I get #UNPARSEABLE error. The rows that are spelled out are where information is gets added and the 1T, 2T, 3T ones are where I wanted the information to be automatically inserted.

    Not sure what I did wrong.

  • LeoCicio
    LeoCicio ✭✭
    edited 11/20/23
    Options

    After reviewing your original formula I believe what I did in my previous comment was incorrect so I tried this and getting another error.

    #CIRCULAR REFERENCE

    =IFERROR(INDEX(COLLECT([Account 1]@row:[Account 12 Password]@row, [1]@row:[12P]@row, @cell <> ""), 1, 1), "")

    to help my sheet actually goes Account 1|Account 1 Type|Account 1 User| Account 1 Password and it follows this all the way through 12. After a few columns that have nothing to do with the formula I have them labeled as 1|1T|1U|1P all the way through 12. I hope that helps.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    The first range and the second range should be the same and it should not reference any cell that contains the formula itself.

  • LeoCicio
    Options

    Paul thanks so much sorry I'm having so many issues. I fixed what you told me and the first column works fine but moving into the second third and so on I just get an empty cell.

    1st Formula =IFERROR(INDEX(COLLECT([Account 1 Type]@row:[Account 12 Password]@row, [Account 1 Type]@row:[Account 12 Password]@row, @cell <> ""), 1, 1), "")

    2nd Formula =IFERROR(INDEX(COLLECT([Account 1 Type]@row:[Account 12 Password]@row, [Account 1 Type]@row:[Account 12 Password]@row, @cell <> ""), 1, 2), "")

    and so on.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are those cells empty in the reference data? Are you able to provide a screenshot that shows the referenced cells as well as the formula cells (with sample data)?

  • LeoCicio
    LeoCicio ✭✭
    edited 11/20/23
    Options

    So this would be the sample data

    and this would be the formula cells.

    1st Formula =IFERROR(INDEX(COLLECT([Account 1 Type]@row:[Account 12 Password]@row, [Account 1 Type]@row:[Account 12 Password]@row, @cell <> ""), 1, 1), "")

    2nd Formula =IFERROR(INDEX(COLLECT([Account 1 Type]@row:[Account 12 Password]@row, [Account 1 Type]@row:[Account 12 Password]@row, @cell <> ""), 1, 2), "")

    3rd Formula =IFERROR(INDEX(COLLECT([Account 1 Type]@row:[Account 12 Password]@row, [Account 1 Type]@row:[Account 12 Password]@row, @cell <> ""), 1, 3), "")

    This repeats over and over from 1 through 12. the formula section is 1T,1U,1P, and it repeats 1 through 12.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you able to provide a screenshot of the second formula open in the sheet as if you are about to edit it?

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

    I made a mistake. My apologies. I was using the 1, 2, 3, in the third portion of the INDEX function because we are referencing a series of columns within a single row, but...

    Using the COLLECT function creates an array that we are actually pulling from which is a vertical listing. That means the 1, 2, 3, should actually be in the second portion of the INDEX function and the third portion of the INDEX function goes unused.


    Try this:

    =IFERROR(INDEX(COLLECT([Account 1 Type]@row:[Account 12 Password]@row, [Account 1 Type]@row:[Account 12 Password]@row, @cell <> ""), 1), "")

    =IFERROR(INDEX(COLLECT([Account 1 Type]@row:[Account 12 Password]@row, [Account 1 Type]@row:[Account 12 Password]@row, @cell <> ""), 2), "")

    =IFERROR(INDEX(COLLECT([Account 1 Type]@row:[Account 12 Password]@row, [Account 1 Type]@row:[Account 12 Password]@row, @cell <> ""), 3), "")

  • LeoCicio
    Options

    Thanks so much this did exactly what I needed! I really appreciate it!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options
  • LeoCicio
    Options

    That's ok.. Thanks again

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!