Split row into multiple row based on column text

**chicagoland8** · 03-10-2014, 10:04 AM

The title may be confusing, but this is what I'm looking for:

For example, I have row 1, 2, 3. Column B in these rows contain comma separated zone codes

A1 = Apple
A2 = Orange
A3 = Banana

Ex. B1 = 152, 156, 989
B2 = 346, 123, 123, 934, 123
B3 = 342

I want to separate the rows out so that each has it's own unique zone code (only one code per line)

Final result looks like this:

A B
Apple 152
Apple 156
Apple 989
Orange 346

etc.

Any ideas?

**XOR LX** · 03-10-2014, 10:21 AM

Hi,

Assuming the original table as you give it is in A1:B3, enter this array formula** in D1:

=IFERROR(INDEX($A$1:$A$3,MATCH(TRUE,MMULT(--(ROW($A$1:$A$3)>=TRANSPOSE(ROW($A$1:$A$3))),1+LEN($B$1:$B$3)-LEN(SUBSTITUTE($B$1:$B$3,",","")))>=ROWS($1:1),0)),"")

Copy down until you start to get blanks for the results.

Then, in E1 (non-array):

=IF(D1="","",TRIM(MID(SUBSTITUTE(INDEX($B$1:$B$3,MATCH(D1,$A$1:$A$3,0)),",",REPT(" ",197)),197*(COUNTIF(D$1:D1,D1)-1)+1,197)))

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

**chicagoland8** · 03-10-2014, 10:29 AM

Thanks for the response---I can't seem to get the array formula right, however. Do I put it in D1, then ctrl/shift/enter? Or do I have to do them all at once from the beginning?

Thanks!

**XOR LX** · 03-10-2014, 10:30 AM

Hi again,

Just the first. Once you've committed it correctly as per my instructions, only then should you drag it down.

Regards

**chicagoland8** · 03-10-2014, 10:35 AM

Hmm seem to get blanks for everything :/

**XOR LX** · 03-10-2014, 10:43 AM

I might suggest you upload your attempt in an actual workbook then.

Obviously replace any confidential/sensitive information with dummy data if necessary.

Regards

**chicagoland8** · 03-10-2014, 10:51 AM

I appreciate the patience!

In this I'm looking at column R: there are multiple 3 digit codes in this and I want each row to have only one, therefore is there are 3 codes, the row would need to be duplicated 3 times, each with one of the three codes.

Book1.xlsx