Solved

Pivot help

Posted on 2014-11-19
14
61 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

828 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