Hi all,
Really need your help on creating a formula (not a macro) for this which I have tried to solve for ages.
Currently, every row is a unique activity with its own activity ID in column A.
Column D lists one or more categories of issues patients face when accessing a service. If there is more than one category, in each cell, the categories are then separated by a comma within the same cell.
What I need is a formula which does the following:
a. there is only one category in D (with no commas), then it be copied into column E.
b. However if Column D holds more than one category which is separated by a comma, then the formula takes the categories beyond the first category and comma and puts it on new seperate rows which copies all the data in the other rows but copies the other categories seperately into Column E.
c. Each cell can have upto 40 categories and therefore upto 39 more rows for that activity.
I have attached an excel spreadsheet as an example to show how I would like it to work.
Im looking for a life saver here as this I have been trying to work this out for ages.
A massive thank you in advance.
Kind Regards
Really need your help on creating a formula (not a macro) for this which I have tried to solve for ages.
Currently, every row is a unique activity with its own activity ID in column A.
Column D lists one or more categories of issues patients face when accessing a service. If there is more than one category, in each cell, the categories are then separated by a comma within the same cell.
What I need is a formula which does the following:
a. there is only one category in D (with no commas), then it be copied into column E.
b. However if Column D holds more than one category which is separated by a comma, then the formula takes the categories beyond the first category and comma and puts it on new seperate rows which copies all the data in the other rows but copies the other categories seperately into Column E.
c. Each cell can have upto 40 categories and therefore upto 39 more rows for that activity.
I have attached an excel spreadsheet as an example to show how I would like it to work.
| Activity ID | Activity Date | Sentiment | Options | Option Breakdown |
| 3380 | 13/04/2015 | Really good Service | Diagnosis | Diagnosis |
| 3384 | 01/04/2015 | Good but not great | Service Coordination, Quality of Treatment | Service Coordination |
| 3384 | 01/04/2015 | Good but not great | Service Coordination, Quality of Treatment | Quality of Treatment |
| 3385 | 15/04/2015 | Excellent needs work | Admission, Complaints Process, Quality of Treatment | Admission |
| 3385 | 15/04/2015 | Excellent needs work | Admission, Complaints Process, Quality of Treatment | Complaints Process |
| 3385 | 15/04/2015 | Excellent needs work | Admission, Complaints Process, Quality of Treatment | Quality of Treatment |
Im looking for a life saver here as this I have been trying to work this out for ages.
A massive thank you in advance.
Kind Regards
