Solved

Pivot help

Posted on 2014-11-19
14
55 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now