Logic assistance, please.

[allison]

I need help working through the logical next steps please.

Each month, we'll receive data that is split out by state, employee, status (there are 3) and month (Jan - Dec). Those are the columns. Each row has a different combination of state, employee and status. The states that appear are only ones that have had activity.

The rollup that needs to be done is by all states, status and month. Thanks to xld, my pivot table is working now - and it's done with that state and the status as rows and each of the months as data fields.

The pivot table looks like this:

SC open sum of jan 1
sum of feb 2
closed sum of jan 0
sum of feb 8


I now need to move those state/status totals to the rollup report.

Because there are more states on the rollup than the actual activity, I can't just move the summed fields over. How is the best way to get them moved over?

I guess, though, the bigger question is - am I doing this with the correct steps? My thought process was
1. Total the data (I thought that a pivot table would be easier)
2. Put into the rollup report. If there are states that aren't on the data, they'd be set to zero on the rollup.

Thanks.

[mikerickson]

I'm not sure that this would work starting with a Pivot Table, but...
If I understand the situation.
You have months as headers and states/status as rows. Not all state/status are present and you want to add blank rows for the missing ones.

Get the data and move it (as values) to a worksheet.
Below that, list all possible state/status (including duplicates of the ones listed above) with blanks in the rest of the columns
Use Advanced Filter, Unique Only to eliminate the duplicated state/status's.
Sort the result and the blank state/status have been inserted.

Or have I misinterpreted the problem?

[allison]

The only part that is missing in that approach is the employee part. The rollup report is done by state and status, but the data is done by state, status and employee. I need to total the state/status for all employees in the rollup - the pivot table was how I was doing that. Should I be doing what you suggested and then do subtotals by state/status?

[mikerickson]

I was thinking you would start with the sub-totals from the pivot table. My "Get the data" kind of meant "Copy PasteValues the Pivot table to a new worksheet".

Does the Rollup include employee data or just totals?

If you could post a sample of what you have, including a sample Rollup, I'd get a better idea of your needs.

[allison]

I'm attaching a workbook that has a rollup, the data and a pivot table that was copy/pasted.

thanks!

[Bob Phillips]

Do the values in the Data section need to get added into the equivalent rollup slots? Either way, a simple VBA move would seem to do the trick.

[allison]

Yes....that's the idea.

[Bob Phillips]

Add a button to the Rollup sheet and assign this macro to it



Public Sub ImportData()
Dim LastRow As Long
Dim LastCol As Long
Dim RowNum As Long
Dim i As Long, j As Long

With Worksheets("Data")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

For i = 2 To LastRow

RowNum = Evaluate("Match(1,(Rollup!A1:A1000=""" & .Cells(i, "A").Value & """)*" & _
"(Rollup!B1:B200=""" & .Cells(i, "C").Value & """),0)")
If RowNum = 0 Then

RowNum = Worksheets("Rollup").Cells(.Rows.Count, "A").End(xlUp).Row + 1
Worksheets("Rollup").Cells(RowNum, "A").Value = .Cells(i, "A").Value
Worksheets("Rollup").Cells(RowNum, "B").Value = .Cells(i, "C").Value
End If

For j = 4 To LastCol + 3

Worksheets("Rollup").Cells(RowNum, j - 1) = .Cells(i, j).Value
Next j
Next i
End With
End Sub