create a "variable array?" to report data
 

I am not sure if a variable array is he right terminology but here is what I
want.

I am exporting data from a Sharepoint list. This data can change everyday
so I need the spreadsheet to reflect that via auto updating.

This is not a problem, works great, the issue is that the data comes in
formated incorrectly for what I need to report on.

The data come in as a list.
Thing Milestone Hours
AAAA 1 2
BBBB 1 3
CCCC 2 2
DDDD 2 4
EEEE 1 1
FFFF 3 5
GGGG 1 3
HHHH 3 1
IIII 3 3
JJJJ 3 3
27


I need to present it organized by milestone.
Thing Milestone Hours
AAAA 1 2
BBBB 1 3
EEEE 1 1
GGGG 1 3
9

CCCC 2 2
DDDD 2 4
6

FFFF 3 5
HHHH 3 1
IIII 3 3
JJJJ 3 3
12

is there a way to create an array on a second work sheet that would only
contain data that matches the Milestone critera?

Thanks

Jeff

--
Jeff

create a "variable array?" to report data
 

I don't know if you have used macros before. but this code will copy the
data on Sheet1 to Sheet2. then Sheet2 will look exactly like the way you
descrivbed in your posting. It will put the subtotals and add the bllank rows


Sub sort_data()

With Sheets("Sheet1")
Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows("1:" & Lastrow).Copy _
Destination:=Sheets("Sheet2").Rows(1)
End With
With Sheets("Sheet2")
.Cells.Sort _
Key1:=Range("B2"), _
Order1:=xlAscending, _
Header:=xlYes
StartRow = 2
RowCount = StartRow
Do While .Range("A" & RowCount) < ""
If .Range("B" & RowCount) < _
.Range("B" & (RowCount + 1)) Then

.Rows(RowCount + 1).Insert
.Rows(RowCount + 1).Insert
.Range("C" & (RowCount + 1)).Formula = _
"=Sum(C" & StartRow & ":C" & RowCount & ")"
.Range("C" & (RowCount + 1)) = "Subtotal"
StartRow = RowCount + 3
RowCount = RowCount + 3
Else
RowCount = RowCount + 1
End If
Loop
End With
End Sub

"Jeff" wrote:

I am not sure if a variable array is he right terminology but here is what I
want.

I am exporting data from a Sharepoint list. This data can change everyday
so I need the spreadsheet to reflect that via auto updating.

This is not a problem, works great, the issue is that the data comes in
formated incorrectly for what I need to report on.

The data come in as a list.
Thing Milestone Hours
AAAA 1 2
BBBB 1 3
CCCC 2 2
DDDD 2 4
EEEE 1 1
FFFF 3 5
GGGG 1 3
HHHH 3 1
IIII 3 3
JJJJ 3 3
27


I need to present it organized by milestone.
Thing Milestone Hours
AAAA 1 2
BBBB 1 3
EEEE 1 1
GGGG 1 3
9

CCCC 2 2
DDDD 2 4
6

FFFF 3 5
HHHH 3 1
IIII 3 3
JJJJ 3 3
12

is there a way to create an array on a second work sheet that would only
contain data that matches the Milestone critera?

Thanks

Jeff

--
Jeff