Combine multiple cells & ignoring blanks

[mrbret]

Hi everyone

I have two rows/columns, B14:B24 and C14:C24.

The cells in column B show either TRUE or FALSE.
The cells in column C shows text when the adjacent cell in column B = True.

In cell E26 (merged to be the same amount of rows as the info in B and C) I want to combine the text which populated in column C but don't want it to include any blank cells from C.
I'd like each row of text to be on a different line within E26 also.

Thanks for your help

[alansidman]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[mrbret]

A before sheet would just be the exact info listed above and I'm not sure what you mean by "after" sheet, If I had and "after" anything I would not need the help.
No idea where "Go Advanced" is either

I have attached a visual version of what was in my question though

[alansidman]

I can't put the data in box as you have, but below is some VBA code that will put the data in Column E in the first unused cell.

Please Login or Register  to view this content.

Alternatively, you could filter the range on column B to only show TRUE and then copy and paste to "box"

[mrbret]

VBA and just first empty box wont work for me and the copy/paste option is too messy, I need the main box to update as the true/false boxes change.

Thanks for those ideas though

[JohnTopley]

Please Login or Register  to view this content.

[mrbret]

Sorry, I meant I don't want a VBA option, not that the one provided doesn't work.

[JohnTopley]

The you need to have a string of concatenate IF statements.

[mrbret]

Yep. But nothing I've tried so far works to look like the attachment.

[AlKey]

Yep. But nothing I've tried so far works to look like the attachment.

It would be an easy task if you have access to Excel Office 365 subscription.

[mrbret]

Nope, just Excel 2013, that's what they use at work.


Anyone have any ideas / solutions of a formula that would do this?

[AlKey]

You could use concatenated values with char(10) but you have to pick cells manually.

Please Login or Register  to view this content.

[mrbret]

I just tried that, but if a TRUE changes to a FALSE it leave a Blank line in the main box and if a FALSE change to a TRUE it ignores any words in the related C column

[AlKey]

I just tried that, but if a TRUE changes to a FALSE it leave a Blank line in the main box and if a FALSE change to a TRUE it ignores any words in the related C column

You have received help with VBA solutions. Please understand that those are your only options. There are no formula options unless you use Office 365.

[mrbret]

Ok thanks, but as you can see, at no point until now has anyone said it cant be done.

[JohnTopley]

If you are happy to use a"helper" column

in J2

=IFERROR(INDEX($C$2:$C$12,SMALL(IF($B$2:$B$12=TRUE,ROW($B$2:$B$12)-ROW($B$2)+1,""),ROWS($B$2:B2))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

then copy down

Unmerge E2 then

=J2&CHAR(10) &IF(J3<>"",J3&CHAR(10),"")&IF(J4<>"",J4&CHAR(10),"")&IF(J5<>"",J5&CHAR(10),"")&IF(J6<>"",J6&CHAR(10),"")&IF(J7<>"",J7&CHAR(10),"")&IF(J8<>"",J8&CHAR(10),"")&IF(J9<>"",J9&CHAR(10),"")&IF(J10<>"",J10&CHAR(10),"")&IF(J11<>"",J11&CHAR(10),"")

then select cells and merge to get your box.

[mrbret]

Thanks VERY much John, that works perfectly. I really appreciate your help.

[JohnTopley]

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[mrbret]

Done. Thanks again