If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Concatenate formula
I'm trying to get a formula using concatenate that will identify numbers in a
row 3 (4&5) and returning the name of the column above into one cell. Big small large larger 1 4 5 2 So in one cell I should get [small, large] I can get this when only looking at one cell but not when I’m trying to look a multiple ones. |
#2
|
|||
|
|||
Concatenate formula
With data arranged as below; try the below user defined formula
Col A Col B Col C Col D bg small large larger 2 3 4 1 Try this UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. =HLOOKUP_CONCAT(A22,A11) Function HLOOKUP_CONCAT(lookup_Range As Range, ret_Range As Range) Dim lngCol As Long For lngCol = 1 To lookup_Range.Columns.Count If lookup_Range(lngCol) = 3 Then HLOOKUP_CONCAT = _ HLOOKUP_CONCAT & "," & ret_Range(lngCol) Next HLOOKUP_CONCAT = Mid(HLOOKUP_CONCAT, 2) End Function If this post helps click Yes --------------- Jacob Skaria "Bricky" wrote: I'm trying to get a formula using concatenate that will identify numbers in a row 3 (4&5) and returning the name of the column above into one cell. Big small large larger 1 4 5 2 So in one cell I should get [small, large] I can get this when only looking at one cell but not when I’m trying to look a multiple ones. |
#3
|
|||
|
|||
Concatenate formula
Thanks Jacob, unfortunately this is picking up all the data in row A11? Not
only values greater than 3. Other than that works fine. cheers Bricky "Jacob Skaria" wrote: With data arranged as below; try the below user defined formula Col A Col B Col C Col D bg small large larger 2 3 4 1 Try this UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. =HLOOKUP_CONCAT(A22,A11) Function HLOOKUP_CONCAT(lookup_Range As Range, ret_Range As Range) Dim lngCol As Long For lngCol = 1 To lookup_Range.Columns.Count If lookup_Range(lngCol) = 3 Then HLOOKUP_CONCAT = _ HLOOKUP_CONCAT & "," & ret_Range(lngCol) Next HLOOKUP_CONCAT = Mid(HLOOKUP_CONCAT, 2) End Function If this post helps click Yes --------------- Jacob Skaria "Bricky" wrote: I'm trying to get a formula using concatenate that will identify numbers in a row 3 (4&5) and returning the name of the column above into one cell. Big small large larger 1 4 5 2 So in one cell I should get [small, large] I can get this when only looking at one cell but not when I’m trying to look a multiple ones. |
Thread Tools | |
Display Modes | |
|
|