Formula HELP!

LeoCicio

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!

Paul Newcome

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), "")

Paul Newcome

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

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

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

LeoCicio

=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

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

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

LeoCicio

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

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

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

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

LeoCicio

Paul Newcome

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

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

Paul Newcome

Happy to help. Sorry for the initial mix-up.

LeoCicio

That's ok.. Thanks again