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

Seb Philp

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

ker9

@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

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

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

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

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

@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

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

https://community.smartsheet.com/discussion/comment/401558#Comment_401558

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)