Gareth,

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

Your sample is:

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

You want it to look as:

{"a1",1000,500,3;"b1",1000,500,3;"b2",1000,500,3;"c1",600,400,8;"d1",1254,655,0;"d2",1254,655,0}

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.

Aladin

=============

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.

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

=IF(LEN(E2),IF(LEN(E2)-LEN(SUBSTITUTE(E2,",",""))+1>1,LEFT(E2,SEARCH("@",SUBSTITUTE(E2,",","@",1))-1),E2),"")

Did you know that MJF posted a very similar problem at

951.html?

Cheers.

Aladin

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

=============