Formula that combines text cells into single cell paragraph

I have a spreadsheet that compiled Employer of Choice data for several companies, with each company on a separate tab. Under category headers, we listed several components for each company that we found, i.e.: Childcare,Telecommuting, Free transportation, etc.

I'm pulling this data into Tableau (an infographic program) and would now like the lists to combine into one paragraph, separated by semicolons, that will show on the tooltip mouse-over. For example:

Childcare
Telecommuting
Free transportation

...would show as:

Childcare; Telecommuting; Free transportation

The problem is, some of these lists have 12 things, some have 3. I've tried to write formulas like IF(COUNTA(B17:B28)=6, B17&"; "&B18.... etc. that count down, but these have too many IF arguments. I don't want things that have only 3 items to end with "; ; ; "

Can someone please help?! I know there is a character limit for cells, so I've tried to write "and more" into the formula if it's greater than 6, but these are still too many if statements.

Attached is an example of one company tab.

Could you please adjust your attached worksheet to include a sheet that mimics what your desired result would be? I believe this should be fairly easy but I want to make sure I have the same outcome you would like.

Sure! I added a tab that shows what I would like.

Keep in mind that the spreadsheet I have has 100 of these company tabs, and a Summary sheet that uses in Indirect to look up the company name in a list in column A and pull its data from its sheet. I want these paragraphs to eventually be pulled into the Summary sheet. I imagine I will have to write the formula to combine the text and copy-paste over to each company tab, then write the indirect to pull from those new paragraph cells, correct? Is there a faster way to do it?

Would you mind a macro?

Nope! As long as you explain it clearly, haha.

Where would you like the information from rows 4 to 14 at?

Nowhere! The only information I want is a paragraph each for:

Development, Recognition, and Feedback
Work-Life Balance
Benefits
Workplace Culture and Perks
Communication and Leadership

I tried doing it using a named formula (and can't think of a way to get it to work with a formula array) but you CAN just brute-force it using something like this:

Pasted into cell A2, and used to concatenate B2 through L2. It produces the desired result, though of course if the cells you're checking are far-flung this will be more difficult. A macro could do it easily.

=CONCATENATE(IF(C2="",B2,B2&"; "),
IF(D2="",C2,C2&"; "),
IF(E2="",D2,D2&"; "),
IF(F2="",E2,E2&"; "),
IF(G2="",F2,F2&"; "),
IF(H2="",G2,G2&"; "),
IF(I2="",H2,H2&"; "),
IF(J2="",I2,I2&"; "),
IF(K2="",J2,J2&"; "),
IF(L2="",K2,K2&"; "),
IF(M2="",L2,L2&"; "))

Can you direct me to the macro that will do this?

Hey there,

I've got one for you just adding the comments to help you understand.

Thanks!

Alrighty,

Attached is your original workbook updated to include a macro that mimics the Desired Results you provided.

To run the Macro
1.Enable macros and then Press Alt+F8 on your keyboard
2. From the macro window select Test
3. Select the run option.

This macro will run and format everything as asked. To run it another time you will need to delete the created worksheet.

To use this macro into your workbook, open your workbook:

1.Press Alt+F8 on your keyboard
2.Clear the macro name box and type LocateCells in the blank box provided
3.Select the Create option
4.In between the Sub LocateCells() and End Sub copy and paste the code:

Please Login or Register  to view this content.

Please note that this macro assumes that all worksheets are company names and there are no worksheets that you don't want included in the formatted worksheet. If this is not true for your worksheet , you might want to move all the worksheets that do not contains data you want included in the summary to the vbery beginning of the workbook and then you will need to change the following line in the code:

Please Login or Register  to view this content.

To the below, replacing 1 with the number that represents the first worksheet you wish to start with index number. Index number refers to the tab position +1. In the worksheet you sent me Accenture would be 1, Desired Results would be two and so on.

Please Login or Register  to view this content.

Also this macro assumes that all your company worksheets start their user entered data for the categories at a row greater than 10.

5.Anything that appears in green is comment I left to hopefully help you understand.
6.Exit out of the Visual Basic Window
7.Press Alt+F8 again and this time select the LocateCells macro
8.Select Run


Let me know if this works for you or if you have any questions.

rvasquez, whoever you are, wherever you are, you just saved my day. THANK YOU!!!!!