formula needed

[kylefoley76]

I need a formula that can produce column D. Essentially, column B equals column A. So using column C I should be able to produce D.

 

[kylefoley76]

the formula works but it has a major drawback, every cell needs to be filled for it to work. i'm going to have a lot of blank cells in column b. i got the formula to work with about 8 rows but when i tried to expand it to the real document with 130 rows it failed to work. here's the document

https://docs.google.com/file/d/0B9zzW6-3m2qGU1R5a1d0UkxKN3M/edit?usp=sharing

 

[kylefoley76]

also, is control shift enter supposed to put brackets around a formula? if so it doesn't work on my mac?

 

[markmzz]

Hi Kylefoley76,

Try this big formula (here it Works with your workbook):

Layout (only one part of your sheet - range A13:E39)

11	12a		3,4,5	1,2,3
12	12q
13	12b
14	12c		12a,3,4	11,1,2
15	13		3,12a	1,11
16	14		3,12a	1,11
17	15		3,12a	1,11
18	16
19	17
20	18		prim
21	22a		3,4,5	1,2,3
22	21x
23	18a		3,6,14	1,4,16
24	18c		14,22a,3,6	16,21,1,4
25	18b		18c,3,6,512a	24,1,4
26	22q
27	22		3,4,5	1,2,3
28	25		3,4,5	1,2,3
29	24t
30	24		3,7,18	1,5,20
31	24a		3,8	1,6
32	24b		24,24a	30,31
33	24c		24,24a	30,31
34	24d		12a,5,24,4	11,3,30,2
35	24e		24d,22a,4	34,21,2
36	20		3,4,18b,24b,24a	1,2,25,32,31
37	20b		3,22a,25	1,21,28

<tbody>
</tbody>

E1-> =IFERROR(INDEX($A$1:$A$130,INDEX(MATCH(TRIM(MID(SUBSTITUTE($D1,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN($D1)-LEN(SUBSTITUTE($D1,",",""))+1))*99-98,99)),$B$1:$B$130&"",0),1)),"")&
IFERROR(","&INDEX($A$1:$A$130,INDEX(MATCH(TRIM(MID(SUBSTITUTE($D1,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN($D1)-LEN(SUBSTITUTE($D1,",",""))+1))*99-98,99)),$B$1:$B$130&"",0),2)),"")&
IFERROR(","&INDEX($A$1:$A$130,INDEX(MATCH(TRIM(MID(SUBSTITUTE($D1,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN($D1)-LEN(SUBSTITUTE($D1,",",""))+1))*99-98,99)),$B$1:$B$130&"",0),3)),"")&
IFERROR(","&INDEX($A$1:$A$130,INDEX(MATCH(TRIM(MID(SUBSTITUTE($D1,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN($D1)-LEN(SUBSTITUTE($D1,",",""))+1))*99-98,99)),$B$1:$B$130&"",0),4)),"")&
IFERROR(","&INDEX($A$1:$A$130,INDEX(MATCH(TRIM(MID(SUBSTITUTE($D1,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN($D1)-LEN(SUBSTITUTE($D1,",",""))+1))*99-98,99)),$B$1:$B$130&"",0),5)),"")&
IFERROR(","&INDEX($A$1:$A$130,INDEX(MATCH(TRIM(MID(SUBSTITUTE($D1,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN($D1)-LEN(SUBSTITUTE($D1,",",""))+1))*99-98,99)),$B$1:$B$130&"",0),6)),"")&
IFERROR(","&INDEX($A$1:$A$130,INDEX(MATCH(TRIM(MID(SUBSTITUTE($D1,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN($D1)-LEN(SUBSTITUTE($D1,",",""))+1))*99-98,99)),$B$1:$B$130&"",0),7)),"")&
IFERROR(","&INDEX($A$1:$A$130,INDEX(MATCH(TRIM(MID(SUBSTITUTE($D1,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN($D1)-LEN(SUBSTITUTE($D1,",",""))+1))*99-98,99)),$B$1:$B$130&"",0),8)),"")

Markmzz

 

[kylefoley76]

Thanks that did it. I really appreciate your help. You really rock!

 

[markmzz]

Thanks that did it. I really appreciate your help. You really rock!

You're welcome and I'm glad to help.

Markmzz