+ Reply to Thread
Results 1 to 7 of 7

Auto plotting risk ID's in a matrix table. THANK YOU.

  1. #1
    Registered User
    Join Date
    04-08-2021
    Location
    Swindon, UK
    MS-Off Ver
    MS365
    Posts
    23

    Auto plotting risk ID's in a matrix table. THANK YOU.

    Hi everyone,

    You've always been so knowledgeable and expert and I'd love to draw on the communities wisdom again.

    I'm not really even sure how to title what I am hoping to make Excel do or how to search for help.

    Imagine a Risk Assessment - each risk ID has an impact and a likelihood.
    So each row has 3 numbers - an ID, an Impact and a Likelihood.

    What I want to do is plot the ID's (rows) on a table.
    So in a simple example, the table is 3x3.

    So if ID 1 has an Impact number of 1 and a likelihood number of 1, it appears in the bottom left box of the table - plotted 1 - 1.

    But, if 3 ID's all also have 1 and 1, then they would also need to be displayed in that box, separated by a comma.

    I've demonstrated this in the attached sheet, but what I can't work out is whether this could be automated in some way?
    To be honest, I've no idea where even to start with trying to address it.

    Thanks so much everyone for any ideas you've got.

    Appreciate it all.

    Chris
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,419

    Re: Auto plotting risk ID's in a matrix table. THANK YOU.

    You've always been so knowledgeable and expert and I'd love to draw on the communities wisdom again.
    Why have you created a new account???

    I will have a look at your workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    04-08-2021
    Location
    Swindon, UK
    MS-Off Ver
    MS365
    Posts
    23

    Re: Auto plotting risk ID's in a matrix table. THANK YOU.

    Hi AliGW,
    Thanks for the reply.

    My old account was linked to my old workplace email address. When I left the job I forgot the password (my work laptop had remembered it), so I couldn't log in and I couldn't do password reset as I no longer had access to my work emails.
    Hence the new account.
    I think the old one's user name was MagicRountaboutCJP (but again, this was auto saved into the laptop)

    THANK YOU

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,419

    Re: Auto plotting risk ID's in a matrix table. THANK YOU.

    OK - thanks. I understand.

    Try this in F12 copied across and down:

    =TEXTJOIN(", ",1,FILTER($A$4:$A$9,($B$4:$B$9=$E12)*($C$4:$C$9=F$15),""))

  5. #5
    Registered User
    Join Date
    04-08-2021
    Location
    Swindon, UK
    MS-Off Ver
    MS365
    Posts
    23

    Re: Auto plotting risk ID's in a matrix table. THANK YOU.

    Oh my life, you've just blown my mind
    That's incredible.
    THANK YOU SO MUCH. So neat, clever and quick. Thank you.
    Have a wonderful afternoon.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,419

    Re: Auto plotting risk ID's in a matrix table. THANK YOU.

    No worries. Lucky that you have MS365 - I was able to use the new dynamic function FILTER

  7. #7
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: Auto plotting risk ID's in a matrix table. THANK YOU.

    Try Cell F12 formula , Drag down and across

    HTML Code: 

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Risk Assessment Matrix
    By MattKoleczko in forum Excel General
    Replies: 6
    Last Post: 06-03-2020, 09:56 AM
  2. Risk matrix table with multiple returns in single cell
    By GreatGizmo in forum Excel General
    Replies: 6
    Last Post: 11-19-2018, 12:57 AM
  3. Risk Matrix to auto-fill color based on choices in two other cells.
    By EvalynT in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-26-2014, 06:28 AM
  4. Trying to create a Risk Matrix
    By jmcnair in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-01-2014, 08:29 PM
  5. Risk Matrix
    By BAW331 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-03-2012, 04:30 PM
  6. [SOLVED] Risk Matrix/Decision table
    By mikmak in forum Excel General
    Replies: 4
    Last Post: 04-23-2012, 11:31 PM
  7. Risk Matrix
    By Stoffer in forum Excel General
    Replies: 4
    Last Post: 01-24-2012, 05:53 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1