asked on

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?

byundt

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.

nardeenawadalla

ASKER

Hi!

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.
Practice.xlsx

byundt

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

nardeenawadalla

ASKER

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!!

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.