MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Breaking up combined list into single entries ??

Posted by gareth Hall on October 11, 2001 4:06 PM

Hope that title is clear.

I get from a customer an excel sheet containing data like this

panel x y holes
a1,b1,b2 1000 500 3
c1 600 400 8
d1,d2 1254 655 0

I want to paste that info into a worksheet and have it produce a table that looks like this via a formula.

panel x y holes
a1 1000 500 3
b1 1000 500 3
b2 1000 500 3
c1 600 400 8
d1 1254 655 0
d2 1254 655 0

Any ideas. I have tried text to columns but this seems a rather longwinded way to me to get the final result (I gave up half way thru a solution :(. Everytime I do this job I would get 20 panels per paste and 10 pastes per job so I would like to find a easy way to do this, any help greatly appreciated.

Posted by Aladin Akyurek on October 13, 2001 4:50 AM


I'll assume that A1:D4 houses your sample data including column headings/panels.

Your sample is:


You want it to look as:


I'll also assume, and this is important, that the following never occurs in your data:

a1,b1,b2 1000 500 3
b1,c1 ....

that is, a panel is not repeated across records/rows.

In E2 enter: =IF(AND(LEN(F2),ISNUMBER(SEARCH(",",A2))),SUBSTITUTE(A2,F2&",","")&","&A3,A3)

In F2 enter: =IF(AND(LEN(A2),ISNUMBER(SEARCH(",",A2))),IF(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1>1,LEFT($A2,SEARCH("@",SUBSTITUTE($A2,",","@",1))-1),$A2),A2)

In G2 enter: =IF(AND(LEN($F2),SUMPRODUCT((ISNUMBER(SEARCH($F2,$A$2:$A$4))+0))>0),INDEX(B:B,SUMPRODUCT((ISNUMBER(SEARCH($F2,$A$2:$A$4))+0)*(ROW($A$2:$A$4)))),"")

Note. The arg of INDEX is A:A and B:B. This requires that you don't have anything else but the data of interest in A thru D. Otherwise, change A:A to $A$1:$A$4 and B:B to B$1:B$4.

Copy the formula in G2 to H2:I2.

Select E2:I2 then copy down as far as needed.

I didn't test this sytem of formula extensively, but I thrust it will function as intended.



Posted by Aladin Akyurek on October 13, 2001 5:12 AM

Rectifying omitted bits

In E3 enter: =IF(AND(LEN(F3),ISNUMBER(SEARCH(",",E2))),SUBSTITUTE(E2,F3&",","")&IF(LEN(A4),","&A4,""),"")

In F3 enter: =IF(LEN(E2),IF(LEN(E2)-LEN(SUBSTITUTE(E2,",",""))+1>1,LEFT($E2,SEARCH("@",SUBSTITUTE($E2,",","@",1))-1),$E2),IF(LEN(A3),IF(LEN(A3)-LEN(SUBSTITUTE(A3,",",""))+1>1,LEFT($A3,SEARCH("@",SUBSTITUTE($A3,",","@",1))-1),$A3),""))

Select E3:F3 and copy down as far as needed.

Select G2:I2 then copy down as far as needed.

Posted by Aladin Akyurek on October 14, 2001 1:07 PM


I'd suggest to replace the formula in F3 with the one that follows:


Did you know that MJF posted a very similar problem at




PS. If you'd like a copy of the workbook that shows the whole thing, just drop me a line.