?
Solved

Pivot help

Posted on 2014-11-19
14
Medium Priority
?
66 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 49

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 49

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 49

Assisted Solution

by:Martin Liss
Martin Liss earned 2000 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 49

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 49

Accepted Solution

by:
Martin Liss earned 2000 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 49

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 49

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

718 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