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.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
formula needed
- Thread starter kylefoley76
- Start date
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.
Excel Facts
Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
kylefoley76
Well-known Member
- Joined
- Mar 1, 2010
- Messages
- 1,553
I mean if column C reads x, then find x in column B, say it's in cell B5, then x equals the value in A5.
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
I think it would help if you would explain what all that means... Please try to confine your explanation to the exhibit you posted (there is nothing in A5 or B5 in there).
Upvote
0
kylefoley76
Well-known Member
- Joined
- Mar 1, 2010
- Messages
- 1,553
If cells c2 reads 6,4, then find values 4 and 6 in column B. 4 is in B3 and 6 is in B1. Then for B3 read the value in A3 which is 3, then input 3 in D2. Then for B1 read the value in A1 which is 1, then input 1 after the 3 and a comma in D2. Go to C3. C3 reads 9,4. Find 9 in column B. It's in B2. Then go left to A2 which reads 2. Input 2 in D3. Now find 4 in column B. It's in B3. Then go left to A3 which reads 3. Input 3 after 2 and a comma in D3.
Upvote
0
kylefoley76
Well-known Member
- Joined
- Mar 1, 2010
- Messages
- 1,553
This formula returned the correct value in D1 but #NUM in D2 and the same value in D3 as D1. Thanks for the help though
Upvote
0
Sorry, my error.
To enter the formula you have to press Ctrl+Shift+Enter and not only Enter.
Markmzz
Last edited:
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
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,"")
Upvote
0
