CONCATENAT IF. Search Criteria Range and Concatenate Corresponding Range if Found

In cases like this you will need VBA. The User Defined Function below will do this. It is not mine. It states that it was written by tigeravatar.

If you aren’t familiar with how to install one of these copy the VBA code below. Then in Excel press Alt + F11 function key. Click Insert > click Module. A VBA editing window will appear. Paste this code into that. Exit the VBA editor.

Then array-enter this formula in F2 and fill down.

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

And here is the UDF code. You will need to save your file as an xlsm macro-enabled file.

Please Login or Register  to view this content.

Or

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

Please Login or Register  to view this content.

In F2 Cell

=cLookup(E2,$A$2:$A$16,$C$2:$C$16)


Drag it down

or a nice simple UDF (shamelessly copied by me from Jacc), which will also put in the commas, which currently look very out of place in your results column. The formula must be array entered (CTRL-SHIFT-ENTER).

Please Login or Register  to view this content.

=concatY(IF($A$2:$A$16=$E2,$C$2:$C$16,""))

What is the need of Array entry when it is UDF?

Let the UDF to do that array task inside the code

Sixthsense. I can plagiarise the VBA of others, but (to my shame) cannot write my own. So... How do I do that?

Originally Posted by Glenn Kennedy

Sixthsense. I can plagiarise the VBA of others, but (to my shame) cannot write my own. So... How do I do that?

I am aware since you mentioned in post #4.

Accessing the excel cells each time in code is not the better approach when it comes to speed. Loading the range/cells values in array (Variable) and looping through the array will work faster.

Please refer Post #3 code for further info.

Hehe

i do not think that you realse the depth of my ignorance here. Can you translate this into English??

Accessing the excel cells each time in code is not the better approach when it comes to speed. Loading the range/cells values in array (Variable) and looping through the array will work faster.

Originally Posted by Glenn Kennedy

Can you translate this into English??
Accessing the excel cells each time in code is not the better approach when it comes to speed. Loading the range/cells values in array (Variable) and looping through the array will work faster.

In your suggested code

Please Login or Register  to view this content.

If you see your suggested code the c refers to each cell in $C$2:$C$16 range. See how many times the each cells are verifying the value of c cell.

In my code

Please Login or Register  to view this content.

In my suggested code I straight away assigned the selected range of cells value in array variable and accessing the stored (in array) values is much faster than accessing the excel cell each time.

OK. I'll go away and play with this and see if/when I get stuck!!

Please go through the snb website given below which will describes the methods/usage/advantages of using array.

http://www.snb-vba.eu/VBA_Arrays_en.html