Copy row splitting out text string in one column

Hi, had some great help here before so hopefully someone can help with this..

I have a file with potentially 10's of thousands of rows. In each row there is a field called "Country Responsible" and in this there can be one or more countries in the same cell separated by " - ".

What I need is if, for example, there are two countries, say UK - FRANCE, then a new row inserted below with all the same data except in the country responsible field the original cell shows UK and the one below shows FRANCE

I've attached a workbook with two sheets, the first one "Start" shows how the data comes to me and the second one "End" shows how I would like it to end up.

I'd be very grateful for help with a macro that can do this.. note that the resulting data can either be on the original sheet but adjusted for the result i'm after or on a new sheet, I have no preference!

Thanks in advance

Try

Please Login or Register  to view this content.

Originally Posted by jindon

Try

Seems to work perfectly... thanks loads jindon!! Very much appreciated!

Originally Posted by Glenn Kennedy

I just noticed that you want a macro. Blast!!! Here's a formula. Give it a go!!

helper in start F2, copied down:
=LEN(C2)-LEN(SUBSTITUTE(C2,"-",""))+1

End, A2 copied across to E2 and then down:
=IFERROR(INDEX(Start!A$2:A$26,MATCH(0,INDEX(--(COUNTIF(A$1:A1,Start!A$2:A$26)=Start!$F$2:$F$26),0),0)),"")

Overtype this into C2 and copy down:
=IFERROR(TRIM(MID(SUBSTITUTE("-"&INDEX(Start!C$2:C$26,MATCH(0,INDEX(--(COUNTIF($A$1:$A1,Start!$A$2:$A$26)=Start!$F$2:$F$26),0),0)),"-",REPT(" ",125)),125*COUNTIF($A$2:$A2,$A2),125)),"")

Thanks for your help Glenn.. I do prefer it in a macro but if I ever change the process to a formula i'll come back to this!