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
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
=============
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
Plus...
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
Cheers.
Aladin
PS. If you'd like a copy of the workbook that shows the whole thing, just drop me a line.
=============
