Mail merge those without licence

[Carrol]

Dear all,

I have to extract the centre names of 6 types of items with problems indicated by "0" in a database to be mail merged and send reminders for updating the items.

Starting with one of the item, 3rd party insurance (already used 4 worksheets) However, I got the problem in worksheet 4 where I have to write formula to extract the combined centre names so that I can insert "," and a space between the centre names (4 characters). I would like to know if there is a faster way to write the formula instead of now that I need to add one at a time til N=30 in worksheet 4. Also I found it hard to adjust the formula in column B where there is no autofill.

Also the whole process seems so time-consuming (the formula now just cater for one type of license) - as attached creating the pivot table and then making it to the relevant format (with "," between the centres). Is there is any other faster way to identify the problematic centres for 6 types of items?

Appreciate any help! Thank you so much!

[tim201110]

1 upgade to 2016there is function TEXTJOIN there
2

Please Login or Register  to view this content.

[sandy666]

TEXTJOIN function
Applies To: Excel 2016 Excel 2016 for Mac Excel Online Excel for iPad Excel for iPhone Excel for Android tablets Excel Mobile Excel for Android phones

Important: This function is available if you are an Office 365 subscriber and have the latest version of Office installed. It's also available with Excel Online, Excel Mobile, Excel for iOS, and Excel for Android phones and tablets.

If you are not subscriber - forget about TEXTJOIN

TEXTJOIN - read here

[avk]

As per your earlier formula, i have done some modification in formula.
Change all , to & sign & between each word enter "&","&".
Refer attach file. (Without VBA micro)

[Carrol]

Dear Avator,

Thank you so much for your help! I would like to see for the concatenate formula, is that I need to do it for 100 times if I have 100 rows of data as it could not be autofilled for this type of array formula?

I am so grateful for your help!

[Carrol]

Dear Avator,

I just found out that the concatenate formula would not update when the centre names from previous worksheet changes since it concatenate the exact text instead of the cell content. Is there any solution for this? Thanks!

[Carrol]

Dear all,

I found this should be an feasible alternative by using offset.

Thank you all for your help!