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.

 

[markmzz]

Another way:

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

=INDEX($A$1:$A$6,MATCH(99^99,SEARCH("|"&MID(C1,1,FIND(",",C1)-1)&"|","|"&$B$1:$B$6&"|")))&","&
INDEX($A$1:$A$6,MATCH(99^99,SEARCH("|"&MID(C1,FIND(",",C1)+1,99)&"|","|"&$B$1:$B$6&"|")))

Or

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)

Markmzz

 

[kylefoley76]

A more generic approach, using ACONCAT, a workalike of Longre's MCONCAT...

Add the following code in <acronym title="visual basic for applications">VBA</acronym> to your workbook using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Now invoke...

D1, control+shift+enter and copy down:

=REPLACE(aconcat(IF(ISNUMBER(SEARCH(","&$B$1:$B$3,","&$C1)),
   ","&$A$1:$A$3,"")),1,1,"")

I couldn't get this formula to work.

 

[kylefoley76]

disregard

 

[kylefoley76]

Another way:

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

=INDEX($A$1:$A$6,MATCH(99^99,SEARCH("|"&MID(C1,1,FIND(",",C1)-1)&"|","|"&$B$1:$B$6&"|")))&","&
INDEX($A$1:$A$6,MATCH(99^99,SEARCH("|"&MID(C1,FIND(",",C1)+1,99)&"|","|"&$B$1:$B$6&"|")))

Or

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)

Markmzz

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

 

[Aladin Akyurek]

I couldn't get this formula to work.

disregard

Succeeded to get it run?

 

[kylefoley76]

no, I couldn't get it to run.

the error message i'm getting is #name

 

[Aladin Akyurek]

no, I couldn't get it to run.

the error message i'm getting is #name

I think it's ACONCAT. You need to add the VBA code for this function first to your workbook as a module using Alt+F11. When done so, the REPLACE formula will not give #NAME? error. It will just work as intended.

 

[prince141987]

I think it's ACONCAT. You need to add the VBA code for this function first to your workbook as a module using Alt+F11. When done so, the REPLACE formula will not give #NAME? error. It will just work as intended.

Only VBA Cod e Would be the fine for this


Regards
Prince

 

[kylefoley76]

 

[kylefoley76]

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