Check if a cell has data or null and move data from populated cell to another column

Options

Hi all,

I have 15 columns on a sheet that will only have one of them populated with data on the row. I want to add a column that will check the row for the data and copy it to another column in the same sheet. This should create a column that its full from top to bottom with the details from the row it's on.

My head is telling me i need a formula that checks the row for data and ignores the null cells copying the data to another column. Happy to have a helper column to drive this, but unsure of the actual formula??

If this even makes sense, does any one have any pointers?

Many thanks in advance,

Seb

Best Answer

  • ker9
    ker9 ✭✭✭✭✭✭
    Answer ✓
    Options

    @Seb Philp

    Another option - if all your columns are next to each other and only one of the columns contains data, try this:

    =JOIN(COLLECT([Column2]@row:[Column5]@row, [Column2]@row:[Column5]@row, <>""))

    Change Column2 to the name of the first column and Column5 to the name of the last column.

Answers

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Options

    Hi @Seb Philp

    I would do this like this:

    =if(NOT(ISBLANK([Column1]@row)),[Column1]@row,if(NOT(ISBLANK([Column2]@row)),[Column2]@row,ADD ADDITIONAL IF STATEMENTS HERE AS NEEDED FOR THE REMAINING 13 COLUMNS,"Error"))

    You just need to add the additional IF statement in where highlighted and update it for your column names

    this is the basic IF statement - if(NOT(ISBLANK([Column3]@row)),[Column3@row

    Hope that helps

    Thanks

    Paul

  • Seb Philp
    Options

    Hi Paul,

    Thanks for your help with this. I have tried as you said. The formula seems to add a ton of "))))'s" at the end of the code. Looks like its trying to close something off that hasn't been before? Either way, i'm getting it showing with the dreaded #UNPARSEABLE error!

    I think i got what you were saying but i've attached my code to see if i'm on the right lines?

    Here's my code:

    =IF(NOT(ISBLANK([FLOOR BEAM REINFORCEMENTS W/O]@row)), [FLOOR BEAM REINFORCEMENTS W/O]@row, =IF(NOT(ISBLANK([CREASE BEAMS W/O]@row)), [CREASE BEAMS W/O]@row, =IF(NOT(ISBLANK([SEAT TRACKS W/O]@row)), [SEAT TRACKS W/O]@row, =IF(NOT(ISBLANK([SHEAR PANEL W/O]@row)), [SHEAR PANEL W/O]@row, =IF(NOT(ISBLANK([ATTENDANT SEAT W/O]@row)), [ATTENDANT SEAT W/O]@row, =IF(NOT(ISBLANK([RCB W/O]@row)), [RCB W/O]@row), =IF(NOT(ISBLANK([CARGO LININGS SUPPORTS/LINERS/DADOS W/O]@row)), [CARGO LININGS SUPPORTS/LINERS/DADOS W/O]@row, =IF(NOT(ISBLANK([LWR Cargo (Liners/Rail/Tie Downs) W/O]@row)), [LWR Cargo (Liners/Rail/Tie Downs) W/O]@row, =IF(NOT(ISBLANK([Pulley Brackets W/O]@row)), [Pulley Brackets W/O]@row, =IF(NOT(ISBLANK([Reinforcements (STR/Shear Tie/Frame) W/O]@row)), [Reinforcements (STR/Shear Tie/Frame) W/O]@row, =IF(NOT(ISBLANK([Floor Panels W/O]@row)), [Floor Panels W/O]@row, =IF(NOT(ISBLANK([AD-HOC W/O]@row)), [AD-HOC W/O]@row, =IF(NOT(ISBLANK([Additional Workorders]@row)), [Additional Workorders]@row, "Error")))))))))))))
    

    Can you see anything i'm doing wrong? Sorry to bother you about this... :0)

  • Seb Philp
    Options

    Hi Paul,

    Thanks for your help with this. I have tried as you said. The formula seems to add a ton of "))))'s" at the end of the code. Looks like its trying to close something off that hasn't been before? Either way, i'm getting it showing with the dreaded #UNPARSEABLE error!

    I think i got what you were saying but i've attached my code to see if i'm on the right lines?

    Here's my code:

    =IF(NOT(ISBLANK([FLOOR BEAM REINFORCEMENTS W/O]@row)), [FLOOR BEAM REINFORCEMENTS W/O]@row, =IF(NOT(ISBLANK([CREASE BEAMS W/O]@row)), [CREASE BEAMS W/O]@row, =IF(NOT(ISBLANK([SEAT TRACKS W/O]@row)), [SEAT TRACKS W/O]@row, =IF(NOT(ISBLANK([SHEAR PANEL W/O]@row)), [SHEAR PANEL W/O]@row, =IF(NOT(ISBLANK([ATTENDANT SEAT W/O]@row)), [ATTENDANT SEAT W/O]@row, =IF(NOT(ISBLANK([RCB W/O]@row)), [RCB W/O]@row), =IF(NOT(ISBLANK([CARGO LININGS SUPPORTS/LINERS/DADOS W/O]@row)), [CARGO LININGS SUPPORTS/LINERS/DADOS W/O]@row, =IF(NOT(ISBLANK([LWR Cargo (Liners/Rail/Tie Downs) W/O]@row)), [LWR Cargo (Liners/Rail/Tie Downs) W/O]@row, =IF(NOT(ISBLANK([Pulley Brackets W/O]@row)), [Pulley Brackets W/O]@row, =IF(NOT(ISBLANK([Reinforcements (STR/Shear Tie/Frame) W/O]@row)), [Reinforcements (STR/Shear Tie/Frame) W/O]@row, =IF(NOT(ISBLANK([Floor Panels W/O]@row)), [Floor Panels W/O]@row, =IF(NOT(ISBLANK([AD-HOC W/O]@row)), [AD-HOC W/O]@row, =IF(NOT(ISBLANK([Additional Workorders]@row)), [Additional Workorders]@row, "Error")))))))))))))

    Can you see anything i'm doing wrong? Sorry to bother you about this... :0)

  • ker9
    ker9 ✭✭✭✭✭✭
    edited 10/25/23
    Options

    Hi @Seb Philp & @Paul McGuinness

    FYI - using the @name, as above, sends a notification to the person named.

    Remove all but the first equal "=" sign. @row, =IF(NO

    There may be other things wrong, but that is the most obvious. It will add a parenthesis at the end for each additional IF statement.

    Sometimes it's easier to build a long formula one statement at a time to make it easier to catch errors.

    Hope this helps!

  • ker9
    ker9 ✭✭✭✭✭✭
    Answer ✓
    Options

    @Seb Philp

    Another option - if all your columns are next to each other and only one of the columns contains data, try this:

    =JOIN(COLLECT([Column2]@row:[Column5]@row, [Column2]@row:[Column5]@row, <>""))

    Change Column2 to the name of the first column and Column5 to the name of the last column.

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Options

    Thanks @ker9

    @Seb Philp as Ker9 said removing all = after the first one should fix that and it correctly adds all the parenthesis on the end as its closing all the if statements

    Thanks

    Paul

  • Seb Philp
    Options

    Thanks so much for this! It worked perfectly. Does exactly what i wanted and easily scalable too... :)

    I really appreciate everyone's help/comments with this.

    Hope you all have a good one,

    Seb :0)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!