Check if a cell has data or null and move data from populated cell to another column
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
-
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
-
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
-
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)
-
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)
-
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!
-
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.
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
