Pivot help


How best to create a matrix of the person assigned in a list like this:
I assume a pivot but since the names are together it is not quite so apparent how to best display the info per person.  

Owner                                                               Qstn No
Jenn Jett                                                                     1.   
Martin                                                                     2.   
Jenn Jett, Martin                                                     3.   
Martin, Anne                                                             4.   
Martin, Anne                                                             5.   
Martin, Lars, Chris Isaac, Linda Cuny, Anne   6.   

Please see the attached.  

thank you
pdvsaProject financeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Martin LissOlder than dirtCommented:
Would a macro be acceptable?
Rory ArchibaldCommented:
I don't think a pivot table will be much use with that data. What do you want the output to look like?
pdvsaProject financeAuthor Commented:
Martin, I think  a macro would be OK.  I need something that is quick and that takes into account any mods.  

I need the output to look like a pivot.  Person assigned to each question.  

thank you
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Martin LissOlder than dirtCommented:
Do you want something like this:

Owner          Questions Assigned
Jenn Jet          1, 3, 12
Debbie Urman          17, 19, 20, 21, 22
pdvsaProject financeAuthor Commented:
Martin:  yes that would be OK.  

pdvsaProject financeAuthor Commented:
fyi:  The names will change though.  I have to add that functionality if they do or if I add names.
Martin LissOlder than dirtCommented:
Try this.
Sub Assignments()

Dim colNames As New Collection
Dim strNames() As String
Dim lngRow As Long
Dim lngName As Long
Dim lngNdx As Long
Dim lngNextRow As Long
Dim strQN As String

Range("D2:E" & ActiveSheet.UsedRange.Rows.Count).ClearContents
' Create a unique collection of names
For lngRow = 2 To ActiveSheet.UsedRange.Rows.Count
    strNames = Split(Cells(lngRow, 1), ",")
    For lngName = 0 To UBound(strNames)
        On Error Resume Next
        colNames.Add Trim(strNames(lngName)), Trim(strNames(lngName))
        On Error GoTo 0

Range("D1") = "Name"
Range("E1") = "Questions Assigned"
lngNextRow = 2
' Look at each unique name
For lngName = 1 To colNames.Count
    ' See if the name is in the 'Owner' column
    For lngRow = 2 To ActiveSheet.UsedRange.Rows.Count
        Cells(lngNextRow, 4) = colNames(lngName)
        strNames = Split(Cells(lngRow, 1), ",")
        For lngNdx = 0 To UBound(strNames)
            If colNames(lngName) = Trim(strNames(lngNdx)) Then
                ' The name is in the 'Owner' column so put
                ' the 'Qstn No' in column 5
                strQN = Replace(Cells(lngRow, 2), ".", "")
                Cells(lngNextRow, 5) = Cells(lngNextRow, 5) & " " & Val(strQN) & ","
                Exit For
            End If
    lngNextRow = lngNextRow + 1

' Remove trailing commas
For lngRow = 2 To ActiveSheet.UsedRange.Rows.Count
    If Cells(lngRow, 5) <> "" Then
        Cells(lngRow, 5) = Left$(Cells(lngRow, 5), Len(Cells(lngRow, 5)) - 1)
    End If
End Sub

Open in new window

pdvsaProject financeAuthor Commented:
nice.  Please see attached as I think it might need to be tweaked.  Let me know what you think...

thank you
Martin LissOlder than dirtCommented:
Are you saying that the numbers in E3 are wrong? They look right to me but if you disagree please tell me what you believe they should be. BTW you have both "jennifer DeMarco and "Jen DeMarco" so there are separate rows (8 and 10) for them.
Martin LissOlder than dirtCommented:
Here's a small tweak that will left-justify the data in column E.

Change line 47 above to

Cells(lngRow, 5) = "'" & Left$(Cells(lngRow, 5), Len(Cells(lngRow, 5)) - 1)

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
Martin LissOlder than dirtCommented:
Also you might consider selecting column C and doing Format>Cells>Alignment>Wrap Text. And if you do that you should probably do Format>Cells>Alignment>Vertical Top in column E.
pdvsaProject financeAuthor Commented:
Martin, I see there was a typo in my data with the demarco.  That probably explains it.  Out of office at the moment but I think I have the solution.  Will check and get bk to you in a few min.
pdvsaProject financeAuthor Commented:
that was simply awesome.  Wow what a time saver.  I can go get a coffee and goof off now.  :)
Martin LissOlder than dirtCommented:
I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
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.