Consolidate data

[welchs101]

I have a list that i am trying to consolodate.

In colA i have an id-number. In colB i have hours worked.

What i want to do is consolodate the list such that i have one row for each "id" and in column B i want the total hours worked for that "id".

So my problem is i have a list like this

ID Hours worked
11 4.5
11 4.0
11 1.0
12 8.0
13 4.0
14 1.0
14 3.0
14 4.0
14 5.0
15 8.0

What i want is to consolodate the list such that i have the following

ID Hours worked
11 9.5
12 8.0
13 4.0
14 13.0
15 8.0

Anyone have idea as to the best way to do this via VBA? Ideally i would like to keep the original list intact and create a separate consolodated list.

[JBeaucaire]

I would suggest you click in the data and then Data > PivotTable.

[welchs101]

i need to do this via vba........

[welchs101]

hi paul,

just saw your post.........i will check

thanks.

[welchs101]

hi paul,

yes this works great! Apparently i left out one important thing though.........the sheet this is occuring on is "hidden" so when i run your macro for a hidden sheet i get a runtime error.

[Paul]

Hi Welchs, the following should work pretty well. It inserts a unique list of ID's into column D and then the sums (using SUMIF formulas) into column E.

Sub welchs()
Dim colA As Long, colD As Long
colA = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:A" & colA).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("D1"), Unique:=True
Range("E1").Value = "Hours"
Range("E2").Formula = "=SUMIF($A:$A,D2,$B:$B)"
colD = Range("D" & Rows.Count).End(xlUp).Row
Range("E2").AutoFill Destination:=Range("E2:E" & colD)
End Sub

[Paul]

Ok, put this code into a standard module (VB Editor -> Insert -> Module) and change "Sheet2" to your hidden sheet's name. If you still get an error, is the hidden sheet also protected? You would have to unprotect it at the beginning of the macro, then re-protect it at the end.

Sub welchs2()
Dim colA As Long, colD As Long
With Sheets("Sheet2")
colA = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("A1:A" & colA).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("D1"), Unique:=True
.Range("E1").Value = "Hours"
.Range("E2").Formula = "=SUMIF($A:$A,D2,$B:$B)"
colD = .Range("D" & .Rows.Count).End(xlUp).Row
.Range("E2").AutoFill Destination:=.Range("E2:E" & colD)
End With
End Sub

[welchs101]

this works! thanks.

[welchs101]

Hi Paul,

I was wondering if you coudl help with one more thing.

ID Hours worked Name
11 4.5 A
11 4.0 A
11 1.0 A
12 8.0 B
13 4.0 C
14 1.0 D
14 3.0 D
14 4.0 D
14 5.0 D
15 8.0 E

Suppose i had a name col such as the one above......how would i also get the name in the new list.

[Paul]

I'd suggest not having a name column, and in a separate area of the worksheet/workbook have a list of ID's and their corresponding names (e.g. on Sheet7 in columns A and B). You could then use VLOOKUP to match the name to the correct ID's in column D on the main sheet.

It's just a better way to store data, and keeps the file size smaller by not replicating data over and over.

[welchs101]

ok...thanks.

I learned a little with your script.........i was not considering anything like this. I was thinking about multiple loops.....anyway.....thanks.