kylefoley76
Well-known Member
- Joined
- Mar 1, 2010
- Messages
- 1,553
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.
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)
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:
Rich (BB code):=REPLACE(aconcat(IF(ISNUMBER(SEARCH(","&$B$1:$B$3,","&$C1)), ","&$A$1:$A$3,"")),1,1,"")
Another way:
Code: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
I couldn't get this formula to work.
disregard
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.