How to switch the categories in a RASI spreadsheet with the data

Topic

New Reply

Hi,
I’d like to rearrange the RASI assignations in my chart to see the listing BY RASI category for EACH stakeholder, rather than the list of tasks crosshatched to all stakeholders. For example, instead of the emphasis on a listing of tasks with the RASIs assignments going across stakeholders, I’d like to take this same information, break it up by stakeholder, and list all the R tasks first, then the A’s, S’s, and I’s so the stakeholder can get a quick understanding of how they are responsible (etc.) for each task. I’d use a pivot but my understanding of pivots is numerical, and this is text re-ordering and parsing. I’ve attached an example.
Thanks! Help make my temp job pay off! :^_^:

Reply | Quote

Replies

I don’t quite understand what you want. If you are only interested in Mgmt you create a custom sort list (R,A,S,I) and then sort by Mgmt item to get that order. I don’t understand ultimately what you want the entire output to look like in the example.

I agree that a pivot does not seem appropriate since I don’t think it would make sense to average or sum the task numbers. Are you looking for a macro? if so, how different may the lists be?

Steve

Reply | Quote

Hi Steve,

I don’t want just Management; I want a list for each stakeholder group. Taking a breather and looking at this again, I think I just have to manually pull it apart, make separate charts for each group and sort as you’ve said.

thanks!
Naomi

Reply | Quote

Naomi,

Here is some code that will list the way you had shown. You can add as many records to your table as you wish.

HTH,
Maud

36895-RASI1

Code:

Public Sub RASI()
[COLOR=”#008000″]’——————————————-
‘DECALRE AND SET VARIABLES[/COLOR]
Dim s As Variant
s = Array(“R”, “A”, “S”, “I”)
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Row = 4:: Count = 0
[COLOR=”#008000″]’——————————————-
‘SORT DATA[/COLOR]
For I = 0 To UBound(s)
    Cells(Row, 7) = s(I)
    Row = Row + 1
    Count = Row
    For J = 4 To LastRow
        If Cells(J, 2) = s(I) Then
            Cells(Row, 8) = Cells(J, 1)
            Row = Row + 1
        End If
    Next J
[COLOR=”#008000″]’——————————————-
‘NO MATCHING STAKEHOLDER GROUP[/COLOR]
    If Count = Row Then
        Cells(Row, 8) = “-”
        Row = Row + 1
    End If
Next I
End Sub

Reply | Quote

Wow Maudible, this is awesome! Exactly what I wanted! I don’t know code well enough to write it, but I’m pretty sure I can figure out what to change for my other tables and pull that out too. Thanks so much!

Reply | Quote

Naomi,

If you need help adapting it, feel free to Private message me.

Maud

P.S. Thanks for the thanks

Reply | Quote