Multiple instances of lookup diplayed in a single cell

sgentile

New Member
Joined
Jun 25, 2012
Messages
2
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:p></o:p>
<o:p></o:p>
Sample Final Display<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
(RF -2) 1<o:p></o:p>
(RF -1) 5,14<o:p></o:p>
(RF-6) 9,17<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

<TBODY>
</TBODY>
<o:p></o:p>
Categories Risk Factor <o:p></o:p>

1<o:p></o:p>

-2<o:p></o:p>

<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

2<o:p></o:p>

-10<o:p></o:p>

<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

3<o:p></o:p>

-7<o:p></o:p>

<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

4<o:p></o:p>

3<o:p></o:p>

<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

5<o:p></o:p>

-1<o:p></o:p>

<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

6<o:p></o:p>

1<o:p></o:p>

<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

7<o:p></o:p>

6<o:p></o:p>

<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

8<o:p></o:p>

-4<o:p></o:p>

<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

9<o:p></o:p>

-6<o:p></o:p>

<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

10<o:p></o:p>

-3<o:p></o:p>

<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

11<o:p></o:p>

13<o:p></o:p>

<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

12<o:p></o:p>

-8<o:p></o:p>

<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

13<o:p></o:p>

-6<o:p></o:p>

<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

14<o:p></o:p>

-1<o:p></o:p>

<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

15<o:p></o:p>

-9<o:p></o:p>

<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

16<o:p></o:p>

-8<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

17<o:p></o:p>

-6<o:p></o:p>

<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

18<o:p></o:p>

-15<o:p></o:p>

<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

19<o:p></o:p>

-5<o:p></o:p>

<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

20<o:p></o:p>

-13<o:p></o:p>

<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

<o:p></o:p>

<o:p></o:p>

<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

<TBODY>
</TBODY>
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
First, put the following code in a standard module (Alt+F11 > Insert Module)...

Code:
[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...

CategoriesRisk Factor
1
-2-15,14
2 -10-21
3 -7-69,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>
 

sgentile

New Member
Joined
Jun 25, 2012
Messages
2

ADVERTISEMENT

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

New Member
Joined
Oct 4, 2009
Messages
36
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

Well-known Member
Joined
Oct 16, 2008
Messages
1,110
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,719
Messages
5,597,741
Members
414,171
Latest member
12Rev79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top