Solved

Pivot help

Posted on 2014-11-19
14
60 Views
Last Modified: 2014-11-19
Experts,

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
EE-Questions-Assigned.xlsx
0
Comment
Question by:pdvsa
  • 7
  • 6
14 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40452539
Would a macro be acceptable?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40452548
I don't think a pivot table will be much use with that data. What do you want the output to look like?
0
 

Author Comment

by:pdvsa
ID: 40452582
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
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 46

Expert Comment

by:Martin Liss
ID: 40452683
Do you want something like this:

Owner          Questions Assigned
Jenn Jet          1, 3, 12
Debbie Urman          17, 19, 20, 21, 22
0
 

Author Comment

by:pdvsa
ID: 40452729
Martin:  yes that would be OK.  

thanks
0
 

Author Comment

by:pdvsa
ID: 40452732
fyi:  The names will change though.  I have to add that functionality if they do or if I add names.
0
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 40452799
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
    Next
Next

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
        Next
    Next
    lngNextRow = lngNextRow + 1
Next

' 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
Next
End Sub

Open in new window

0
 

Author Comment

by:pdvsa
ID: 40452913
nice.  Please see attached as I think it might need to be tweaked.  Let me know what you think...

thank you
EE-Questions-Assigned-macro-.xlsm
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40452952
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.
0
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40452970
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)
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40452986
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.
0
 

Author Comment

by:pdvsa
ID: 40453007
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.
0
 

Author Comment

by:pdvsa
ID: 40453326
that was simply awesome.  Wow what a time saver.  I can go get a coffee and goof off now.  :)
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40453356
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
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question