Enhancing a RASCI Model to avoid different input to same assigned Priority

EE Professionals,

I have a very nice workbook that several EE Professionals wrote for use;  I have two problems with it.  One is a design problem that I didn't anticipate and was my fault by not indicating it in the specifications.  The second issue is a design issue for output, which I will post in a new related question once the first issue is resolved.  I have attached the actual code in the file and I believe the modification I am looking at is in Sheet 1 of the VBA Window.

Overview of Problem:

The RASCI Model is a Project Management tool that simply allows you to identify a set of tasks, put them in priority sequence and display who has responsibility for what.  The model attached works fine EXCEPT when you add an additional RESPONSIBILITY to the same task (by adding it to the first blank cell in column A) you can get erroneous results if the Text doesn't exactly match or the user inadvertently adds different text to the same priority number.  So for example, if I were to put in "1.1" in cell A (first open cell in column), and 1.1 was already a Task, the addition of another assignment to the same task, 1.1, should display THE SAME TASK.  This is so you can assign another responsible Functional Area to the same Task.  It allows this now so when you put in 1.1, it will combine the results on the Output beginning in Column H.

What I'm trying to accomplish:

In order to avoid mistakes and bad results, I'd like for the macro to check to see if the assigned number exists, and if so, is auto populate with the previous description IF and only if, a duplicate number appears. This will prevent the user from putting in a different task for the same assigned priority.  So, if I put in 1.1, and 1.1 already exists, it will duplicate the text description in Cell B.  That's it!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arno KosterCommented:
You could use this :

    '-- auto populate existing processes on new entry
    If Target.Column = 1 Then
        If Target.Row > glOldRows Then
            Set result = Range("A4:A" & Target.Row - 1).Find(Target, lookat:=xlWhole)
                If Not result Is Nothing Then
                    Application.EnableEvents = False
                    '-- fill in process name
                    Target.Offset(0, 1) = result.Offset(0, 1)
                    Application.EnableEvents = True
                End If
        End If
    End If

Open in new window

as in  Copy-of-RASCI-Sheetv76-Sample-r6.xlsm

be sure though to thoroughly test all regular use scenario's (does it work as you expect when updating rows, deleting rows etc)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:

I tested the workbook and it works very well.   I believe that any of the slight issues are probably a function of having the table on the same set of rows as the input.  I think if I have the output placed on a different tab/sheet, I think it will ease some of the complexity that surrounds this set of Macros.  I'll be asking a related question to try to move the output to another tab.

I appreciate your insight and help with this.

Arno KosterCommented:
No problem !
glad to have been of service...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.