JOIN only if cell is not blank

JOIN only if cell is not blank

BarneeLBarneeL
edited 12/09/19 in Formulas and Functions

Hi there,

 

Is there a way I can use the JOIN function to merge the contents of an entire row from another sheet, but exclude rows that are blank?

The contents of the other sheet is generated from a form (and being constantly updated), so referencing the entire column is the only option unfortunately.

It seems like it should be easy, but I am drawing a blank on how to do it.

Tagged:

Comments

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi Barnee,

    Yes, but you will get the delimiters between or everything will be without spaces.

    Would that work?

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    edited 11/30/18

    If you are looking to join everything for the row on the sheet itself, you can use

     

    =JOIN(COLLECT([First Column Name]@row:[Last Column Name]@row, [First Column Name]@row:[Last Column Name]@row, NOT(ISBLANK(@cell))), " - ")

     

    You can then use cross sheet references to pull that column to the new sheet. If you are able to provide more details as to what you are doing exactly and how you want things to work and be displayed along with some screenshots, we may be able to help figure out a more useful solution for you.

     

    Edit: Andree and I must have been typing at the same time. With the above formula, you can specify delimiters where you see the " - ". With the COLLECT function, you wouldn't have a bunch of useless delimiters shown in place of all of the blank cells, only in between whatever data is pulled from the populated cells.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    yes Nice solution Paul!yes

    I didn't think about using COLLECT.

    Have a fantastic weekend!

    Best,

    Andrée

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Thank you both! 

    Thanks Paul, I also didn't think of using the COLLECT function. I used your formula, except changed

    ' NOT(ISBLANK(@cell)) '

    to

    ' <> "" '

    Works great. Thank you!

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    I'm always happy to help!

    Have a fantastic weekend!

    Best,

    Andrée

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Thanks. I have a handful of sheets where I have people filling out forms and making selections. To make it easier on them I have it set as checkboxes. I then have a text type column where I use a JOIN(COLLECT( to pull the text value for any columns where they have checked the box to display their entire selection.

     

    I then use place holders and an auto-number system to send an Alert to the email address they entered on the form to give them a sort of ticket number and summary of their form entry. I've posted (a while back) on here a few times about this setup, and I love it.

     

    Needless to say, I use JOIN(COLLECT( quite frequently now as it can make things so much easier and cleaner when displayed on a sheet/dashboard.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. I keep forgetting about <> in place of the NOT function. takes a few less key strokes, so I think I'm going to have to start using it.

Sign In or Register to comment.