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.
<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!
Rank | Telephone Number | Sentence |
1 | 555-1111 | The telephone number 555-1111 is a fake number. |
1 | 555-1111 | The fake number starts with 555. |
1 | 555-1111 | This fake number ends with 1111. |
2 | 555-2222 | The telephone number 555-2222 is a fake number. |
2 | 555-2222 | The fake number ends with 2222. |
3 | 555-3333 | The telephone number 555-3333 is a fake number. |
3 | 555-3333 | The fake number starts with 555. |
3 | 555-3333 | This fake number ends with 3333. |
3 | 555-3333 | The 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!