Formula to create new rows when a cell has specific text beyond a comma

Hi all,

I have attached a sample excel spreadsheet to show how I would like the formula to work.

Hi Glenn,

Thanks for the response.

No i did mean new rows.

So essentially what I wanted was that rather than have a formula to break down each category after the comma into a new column that I wanted it to be put on a new row underneath it.

The example I provided was how I wanted it to look after I would have a formula working.

The new excel file I have attached shows two sheets what the spreadsheet looks like now and what I want it to look like after I have a formula that works to do what I want.

Really appreciate the help.

Hi Glenn,

Thanks for this but theres a problem.

Because you provided the solution on the same sheet, I tried to replicate the formulas by putting the original data on one sheet and the formulas you created on another sheet (I ammended the formula to reflect that some of data was on different sheets).

I needed to do this as I need all the data on one sheet because I am using that data for a pivot table.

Anyway when I applied your formula on a different sheet which I pressed ctrl, shift and enter because they were array formulas, all it showed was 0s. I don't know if its a formatting problem, even though Ive tried to check if it is but can't get my head around why it isn't showing the data.

I have attached the example I am working on - I have put the formulas in the 'After' sheet, which pulls the data from 'Sheet1'.

I would really appreciate it if you could tell me whats gone wrong as I really need this to work.

A Massive thank you in advance.

Try
=IFERROR(INDEX(Sheet1!A$2:A$5,MATCH(0,--(COUNTIF($A$1:$A1,Sheet1!$A$2:$A$5)=Sheet1!$F$2:$F$5),0)), "")

This is brilliant - thanks glenn - one last favour - is there a formula which automatically counts the amount of phrases to automate the Helper column instead of doing it manuallly.

For example, if there there are 10 categories the helper column will automatically recognise that it contains 10 categories.

A BIG THANK YOU IN ADVANCE.

Hi Glenn,

That is a very neat formula. Some of the formula used "REPT", is something i didnt used before.

Anyway I've similar issues with @hionman but instead of writing a formula, is there a way i can automate it with VBA or etc?
Would you suggest using VBA or the formula used in this example.

I've less than a million of records and this task is probably done every quarter.

Cheers
Henry

@Ohhaa

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

@John,

So sorry about that. Let me create a new thread on this

Hi Glenn,

Would you be so kind to look at a similar question I've at this thread.
https://www.excelforum.com/excel-for...y-a-comma.html

Appreciate your help