Pigeon-holing rows of information

**Iamtrying** · 01-08-2009, 02:43 AM

Hi excel people,

I am looking for a way to do the following:

I have a risk register which has rows of individual risks with a 2 weightings (numerical values 1 - 5). (example is attached)

I am looking for a way to pigeon hole each risk into its appropriate weighting area. I am not sure of the best way to do this as there will be risks with the same rating and I want all of them shown (so I don't think an IF function or a VLOOKUP will work)

I have considered doing it as an XY scatter chart but as the x and Y values could be the same, the points get hidden behind each other.

Also, the number of rows is likely to increase so I am looking for a way to refresh the data.

I am guessing that a pivot table might work, but they are not my strongest suit.

Any ideas would be appreciated

**e4excel** · 01-08-2009, 04:05 AM

I was going through your file but am not able to understand it too well..

In the first Matrix you have columns such as Risk Number Impact Probability and Severity..

In the IInd Matrix, Template you have Impact Rating & Probability..
How did you get this is there any relation with the Ist Matrix?

Please provide as much information in order for anyone to help...

**e4excel** · 01-08-2009, 05:43 AM

Putting this code in Cell C2
[ {=IF(ISERROR(INDEX($A$3:$C$14,SMALL(IF(($B$3:$B$14=$B29)*($C$3:$C$14=C$28),ROW($A$3:$A$14)-ROW($A$3)+1),ROWS($A$1:$A$1)),1)),"",INDEX($A$3:$C$14,SMALL(IF(($B$3:$B$14=$B29)*($C$3:$C$14=C$28),ROW($A$3:$A$14)-ROW($A$3)+1),ROWS($A$1:$A$1)),1))} ]

But am not sure whether its practically possible to get more than one Match stored in a single cell one below the other..

May be some gurus might have a solution....

If you allott each Impact 12 rows then maybe you still can get the way you want...

Leme know

**e4excel** · 01-08-2009, 06:54 AM

I have tried the way you wanted by allotting 12 rows to each Impact and with a little Help with conditional formatting and Row Height Spacing you can get almost get what you want....

Please respond if this was helpful or you need something else...

**shg** · 01-08-2009, 11:44 AM

If using a small VBA function is acceptable, see attached.

The formula in C29 and copied across and down is an array formula. Array formulas MUST be confirmed with Ctrl+Shift+Enter, not Enter. You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.

**e4excel** · 01-08-2009, 01:14 PM

Clean Swep SHg...
I had mentioned that some Gurus would always be able to manage it..!

Clean Work..

Just a small query Is it possible without VBA and only with Formulas ?

Not you're post so comments removed by Mod

Thanks...

**Iamtrying** · 01-08-2009, 07:27 PM

Thank you both for your assistance. I believe the VBA suggestion will be the more appropriate for my problem. Just for further info, the s'sheet I sent was a mock up of what I have at the moment. In the actual document, the values will be coming from another s'sheet (just a simple =[workbook][Cell ref]). So I am pretty sure what may happen is that if I change any values in the source workbook, then this "summary worksheet" will auto update and therefore the matrix will also auto update. Thank you very much. Sorry I took so long to respond. Cheers