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]

maybe i'm not understanding the direction, control shift enter and copy down correctly. i have a mac if that means anything but i doubt it. i press control shift and enter but how can i copy down after that?

 

[Aladin Akyurek]

I've got the macro in the vba and it's still not working.

Is it Mac what you have?

Here is the file I hope your system can accept it as is:

https://dl.dropboxusercontent.com/u/65698317/aaAconcat%20kylefoley76.xlsm

 

[Aladin Akyurek]

maybe i'm not understanding the direction, control shift enter and copy down correctly. i have a mac if that means anything but i doubt it. i press control shift and enter but how can i copy down after that?

Control+shift+enter seems to be command+Return on Mac.

 

[kylefoley76]

no, that doesn't do it. this formula works


Use only Enter to enter the formula=LOOKUP(99^99,SEARCH("|"&MID(C1,1,FIND(",",C1)-1)&"|","|"&$B$1:$B$6&"|"),$A$1:$A$6)&","&LOOKUP(99^99,SEARCH("|"&MID(C1,FIND(",",C1)+1,99)&"|","|"&$B$1:$B$6&"|"),$A$1:$A$6)we should be able to figure out how to get that to work for 7 or 8 digits. it looks like you just have to expand a little. </pre>

 

[Aladin Akyurek]

no, that doesn't do it...

Does it open the file at all? If it does, what do you see in the formula cell?

Note. There seem to be some incompatibility between Mac VBA and Windows VBA the people into VBA mentions.

 

[kylefoley76]

i think it's working but still a few problems. i tried to make some adjustments and the brackets disappeared. when they disappeared it stopped working. it seems that i need brackets around the formula for it to work. i can't get the brackets to reappear. i've tried command shift return and all those combinations and i can't get the brackets to reappear.

 

[MrExcel]

Edit the formula, then use Command+Return to enter it as an array formula.

 

[Aladin Akyurek]

Edit the formula, then use Command+Return to enter it as an array formula.

Hi Bill,

Is the ACONCAT code not runnable on a Mac?

 

[markmzz]

This formula only works with 2 numbers in column c. Sometimes I might have up to 5 or 6 numbers in column c.

Hi Kylefoley76,

Try this big formula (use only Enter):

=SUBSTITUTE(SMALL(INDEX(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2))*$A$2:$A$7,),1)&
IFERROR(","&SMALL(INDEX(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2))*$A$2:$A$7,),2),"")&
IFERROR(","&SMALL(INDEX(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2))*$A$2:$A$7,),3),"")&
IFERROR(","&SMALL(INDEX(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2))*$A$2:$A$7,),4),"")&
IFERROR(","&SMALL(INDEX(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2))*$A$2:$A$7,),5),"")&
IFERROR(","&SMALL(INDEX(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2))*$A$2:$A$7,),6),"")&
IFERROR(","&SMALL(INDEX(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2))*$A$2:$A$7,),7),"")&
IFERROR(","&SMALL(INDEX(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2))*$A$2:$A$7,),8),""),"0,","")

Markmzz

 

[markmzz]

A small modification:

Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IFERROR(SMALL(IF(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2)),$A$2:$A$7),1),"")&
IFERROR(","&SMALL(IF(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2)),$A$2:$A$7),2),"")&
IFERROR(","&SMALL(IF(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2)),$A$2:$A$7),3),"")&
IFERROR(","&SMALL(IF(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2)),$A$2:$A$7),4),"")&
IFERROR(","&SMALL(IF(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2)),$A$2:$A$7),5),"")&
IFERROR(","&SMALL(IF(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2)),$A$2:$A$7),6),"")&
IFERROR(","&SMALL(IF(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2)),$A$2:$A$7),7),"")&
IFERROR(","&SMALL(IF(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2)),$A$2:$A$7),8),"")

Markmzz