Multiple instances of lookup diplayed in a single cell

[sgentile]

Have 20 categories numbered 1-20 in Column A; have risk factors identified against each 20 categories in Column B. There are 30 potential risk factors that could be assigned that are located in separate sheet, the factors run from -1 to -15 and 1 to 15. Not all will be used. Am trying to lookup all categories with the same risk factor and display the category numbers in single cell. For example in the risk category -1 cell I would like find and display all instances and display 5,14; for risk -2 cell only 1, risk cell -6 would have 13, 17 displayed, etc. These will populating a risk matrix with assigned risk factors that are already assigned. Any ideas, I am pulling my hair out. Thanks.<o></o>
<o></o>
Sample Final Display<o></o>

<o></o>	<o></o>	<o></o>	(RF -2) 1<o></o>	(RF -1) 5,14<o></o>
(RF-6) 9,17<o></o>	<o></o>	<o></o>	<o></o>	<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>

<TBODY>
</TBODY>

<o></o>
Categories Risk Factor <o></o>

1<o></o>​	-2<o></o>​	<o></o>​	<o></o>	<o></o>
2<o></o>​	-10<o></o>​	<o></o>​	<o></o>	<o></o>
3<o></o>​	-7<o></o>​	<o></o>​	<o></o>	<o></o>
4<o></o>​	3<o></o>​	<o></o>​	<o></o>	<o></o>
5<o></o>​	-1<o></o>​	<o></o>​	<o></o>	<o></o>
6<o></o>​	1<o></o>​	<o></o>​	<o></o>	<o></o>
7<o></o>​	6<o></o>​	<o></o>​	<o></o>	<o></o>
8<o></o>​	-4<o></o>​	<o></o>​	<o></o>	<o></o>
9<o></o>​	-6<o></o>​	<o></o>​	<o></o>	<o></o>
10<o></o>​	-3<o></o>​	<o></o>​	<o></o>	<o></o>
11<o></o>​	13<o></o>​	<o></o>​	<o></o>	<o></o>
12<o></o>​	-8<o></o>​	<o></o>​	<o></o>	<o></o>
13<o></o>​	-6<o></o>​	<o></o>​	<o></o>	<o></o>
14<o></o>​	-1<o></o>​	<o></o>​	<o></o>	<o></o>
15<o></o>​	-9<o></o>​	<o></o>​	<o></o>	<o></o>
16<o></o>​	-8<o></o>​	<o></o>	<o></o>	<o></o>
17<o></o>​	-6<o></o>​	<o></o>​	<o></o>	<o></o>
18<o></o>​	-15<o></o>​	<o></o>​	<o></o>	<o></o>
19<o></o>​	-5<o></o>​	<o></o>​	<o></o>	<o></o>
20<o></o>​	-13<o></o>​	<o></o>​	<o></o>	<o></o>
<o></o>​	<o></o>​	<o></o>​	<o></o>	<o></o>

<TBODY>
</TBODY>

 

[Domenic]

First, put the following code in a standard module (Alt+F11 > Insert Module)...

[FONT=Courier New][COLOR=darkblue]Function[/COLOR] AConcat(a [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], [COLOR=darkblue]Optional[/COLOR] Sep [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR] = "") [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String
[/COLOR][COLOR=green]
   ' Harlan Grove, Mar 2002[/COLOR]

    [COLOR=darkblue]Dim[/COLOR] Y [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]

    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]TypeOf[/COLOR] a [COLOR=darkblue]Is[/COLOR] Range [COLOR=darkblue]Then[/COLOR]
    
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] Y [COLOR=darkblue]In[/COLOR] a.Cells
            AConcat = AConcat & Y.Value & Sep
        [COLOR=darkblue]Next[/COLOR] Y
    
    [COLOR=darkblue]ElseIf[/COLOR] IsArray(a) [COLOR=darkblue]Then[/COLOR]
    
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] Y [COLOR=darkblue]In[/COLOR] a
            AConcat = AConcat & Y & Sep
        [COLOR=darkblue]Next[/COLOR] Y
        
    [COLOR=darkblue]Else[/COLOR]
    
        AConcat = AConcat & a & Sep
    
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    AConcat = Left(AConcat, Len(AConcat) - Len(Sep))
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]
[/FONT]

Then, assuming that A2:B21 contains the data, D2 contains -1, D3 contains -2, and D4 contains -6, enter the following formula in E2, confirm with CONTROL+SHIFT+ENTER, and copy down:

=SUBSTITUTE(AConcat(IF($B$2:$B$21=D2,","&$A$2:$A$21,"")),",","",1)

Results...

Categories	Risk Factor
1	-2		-1	5,14
2	-10		-2	1
3	-7		-6	9,13,17
4	3
5	-1
6	1
7	6
8	-4
9	-6
10	-3
11	13
12	-8
13	-6
14	-1
15	-9
16	-8
17	-6
18	-15
19	-5
20	-13

<tbody>
</tbody>

 

[venkyzrocks]

Could you do use this for string as well..What changes do I need to make ?

 

[Domenic]

Could you do use this for string as well..What changes do I need to make ?

Unclear, can you please elaborate?

 

[sgentile]

OMG! Thank you, this works great! Not very knowledgeable with VB but managed to get it right. Many, many thanks, again. This will save me many manual inputs once I complete the build.

 

[venkyzrocks]

I had to do a similar thing.I tried using it with strings as my inputs were string and not numbers. Could it work for that as well because it doesn't .Could you tell me what changes would have to be made ?

 

[MartinL]

Domenic, thanks for this, just found what I needed 2 and half years after you wrote it...

 

[Domenic]

OMG! Thank you, this works great! Not very knowledgeable with VB but managed to get it right. Many, many thanks, again. This will save me many manual inputs once I complete the build.

You're very welcome! Thanks for the feedback!

 

[Domenic]

Domenic, thanks for this...

You're very welcome!

...just found what I needed 2 and half years after you wrote it...

I guess better late than never...

 

[Domenic]

I had to do a similar thing.I tried using it with strings as my inputs were string and not numbers. Could it work for that as well because it doesn't .Could you tell me what changes would have to be made ?

Can you provide a sample of the data, along with the expected results?