Concatenating cells based on specified criteria in a dynamic range

Oscar2006

New Member
Joined
Jun 23, 2015
Messages
2
I've created an Excel program that automates some customer telephone numbers so I don't have to retype the same sentences over and over. I create a mini paragraph out of the sentences, but use Excel to automate redundant data entry. If a particular telephone number meets a certain criteria in my customer list, it gets associated with a particular sentence. The phrasing of the sentence is not important. What is important is that I want to bring all the individual sentences back together again at the end of my analysis. I cannot use the concatenate function because the list is dynamic. See the example chart below.

RankTelephone NumberSentence
1555-1111The telephone number 555-1111 is a fake number.
1555-1111The fake number starts with 555.
1555-1111This fake number ends with 1111.
2555-2222The telephone number 555-2222 is a fake number.
2555-2222The fake number ends with 2222.
3555-3333The telephone number 555-3333 is a fake number.
3555-3333The fake number starts with 555.
3555-3333This fake number ends with 3333.
3555-3333The fake number 555-3333 is very important.

<tbody>
</tbody>

I want use a formula that can automatically concatenate the sentences based on the telephone number criteria, without spilling over into the next telephone number. The number of sentence could be as high as 10 and as little as 1, and the number of telephone numbers I research could be as high as 200, which makes concatenating by hand impossible. For my table above, the end results would be an individual paragraph with all the sentences combined.

Example: The telephone number 555-1111 is a fake number. The fake number starts with 555. This fake number ends with 1111.

I've looked at the transpose feature, and didn't know if that was the right way to make it work.

My current workaround is to copy the table and paste into Word, then perform a Text to Columns command. This takes is out of a table format, but gives me extra characters. I separate by comma, I have to delete a comma between every sentence, in addition to a carriage return that was carried over from the cell formatting.

Please help!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Here is a very clunky formula. I have set it up for only 4 sentences, it will be easy enough to expand for 10 sentences, but even more inelegant. You could simplify a little by putting a space after the terminating period in each standard sentence.

Excel Workbook
ABC
11555-1111The telephone number 555-1111 is a fake number.
21555-1111The fake number starts with 555.
31555-1111This fake number ends with 1111.
42555-2222The telephone number 555-2222 is a fake number.
52555-2222The fake number ends with 2222.
63555-3333The telephone number 555-3333 is a fake number.
73555-3333The fake number starts with 555.
83555-3333This fake number ends with 3333.
93555-3333The fake number 555-3333 is very important.
10***
11***
1213The telephone number 555-1111 is a fake number. The fake number starts with 555. This fake number ends with 1111.
1322The telephone number 555-2222 is a fake number. The fake number ends with 2222.
1434The telephone number 555-3333 is a fake number. The fake number starts with 555. This fake number ends with 3333. *The fake number 555-3333 is very important.
Sheet3
 
Upvote 0
Thank you for the quick response. I'm going to try it. I found exactly what I was looking for in the MCONCAT function with UNIQUEVALUES; however, I can't download files from the internet. My company restricts executable files. I'm essentially looking for an alternative to MCONCAT, without writing VBA code.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top