Excel 2003: Pivot table - multiple rows associatted with one person.

I'm trying to use pivot tables, however I'm having some difficulty just because of the way the data is set up.

The first column of the spreadsheet is the name of an individual, however, multiple rows are related to one person, I have tried merging the rows in the name column, but the pivot table can't recognize that the name is associated with the row.

Is there a way to associate multiple rows to one name without repeating the name on each line? Or if the name does have to be stated each time, is there a way for excel to recognize that if the name is the same, it should only be counted once if I do a "count name" in the pivot table?
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.

byundtMechanical EngineerCommented:
Your data needs to be normalized before you create a PivotTable. This means either:
1. You have a three column list with the name repeated on each row. The second column lists the data type, and the third column lists the data.
2. You have a multicolumn list with different types of data in different columns. The name is listed only once.

I realize that your data does not look like either of these two alternatives--possibly because you are using a computer report as your data source. If you could post some sample data, I'd be glad to write a macro that normalizes it so you can create a proper PivotTable.
nardeenawadallaAuthor Commented:

Thank you so so much for your response! I have created a mini version of what the file looks like. The real one has approximately 1200 individuals with on average 9 points of contact each.

Attached is a sample! My goal would be to create a pivot table to give me information about how many "types of contact" or "activities" or "categories" have been engaged in in total as well as the number of individuals that have been assisted. So I was going to do a "count date" to know the number of points of contact and "count name" to see how many individuals used the service.

I can't emphasize how much I appreciate your help! Thank you.
byundtMechanical EngineerCommented:
The macro to normalize your data would copy down the values in columns A through F if there is a blank cell. Having done this, you can then prepare a PivotTable using Name as Row item, and Count of Activity, Category and Contact as Value items.

Are you still using Excel 2003? I ask because your sample workbook had .xlsx file format. The version of Excel that you use will make a difference if you want a macro to prepare the PivotTable. I don't believe that the Normalizer routine cares, however.
Sub Normalizer()
Dim rg As Range, rgg As Range
With ActiveSheet
    Set rg = .Range("A2").CurrentRegion
    If Not rg Is Nothing Then
        Set rgg = Intersect(rg, .Range("A:F"))
        If Not rgg Is Nothing Then
            Set rgg = rgg.SpecialCells(xlCellTypeBlanks)
            If Not rgg Is Nothing Then rgg.FormulaR1C1 = "=R[-1]C"
        End If
    End If
End With
'Call PivotTabler
End Sub

Open in new window

Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

nardeenawadallaAuthor Commented:
I'm using Excel 2003 at the project site but the practice was from my home laptop which has the latest excel.
I copied and pasted the code you gave me into VBA. I'm not sure if I have it running properly because so far I'm getting the same results as I did in the beginning. Any advice?
Thanks again!!
byundtMechanical EngineerCommented:
Assuming that you ran the code on worksheet Sheet1, you should have seen the blank cells in rows A12:F13 fill in with values.

Because a .xlsx file cannot contain macros, I renamed it as .xlsm in the attached file. Try running the Normalizer macro on Sheet1.

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:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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

From novice to tech pro — start learning today.