Concatenate formula

B

Bricky

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.
 
J

Jacob Skaria

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(A2:D2,A1:D1)

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
 
B

Bricky

Thanks Jacob, unfortunately this is picking up all the data in row A1:D1? Not
only values greater than 3.
Other than that works fine.

cheers Bricky

Jacob Skaria said:
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(A2:D2,A1:D1)

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 said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top