Solved

consolidate data by two criteria

Posted on 2014-10-15
6
97 Views
Last Modified: 2014-10-20
I need to create a consolidated table from a longer table.  The raw data is as attached.  I need to consolidate by code and date.  ie: numbers of hours per code per date.  A pivot table gives me the correct data but I need it in column format to feed into another workbook.
Thanks in advance.
Example1.xlsx
0
Comment
Question by:acdecal
  • 4
  • 2
6 Comments
 
LVL 22

Expert Comment

by:Flyster
Comment Utility
You can use SUMIFS. Place this formula in H4 and copy down:

=SUMIFS(C:C,A:A,F4,B:B,G4)

Flyster
0
 

Author Comment

by:acdecal
Comment Utility
That would required me to have data in columns F & G to use as criteria.  I have over 5,000 rows of raw data that I need to consolidate by week and code.  I have wrote VBA code to do this but I'm afraid it will take a considerable amount of time to run.  I was hoping for an easier solution like using the consolidation function or subtotaling or getting the data from a pivot table.
0
 

Accepted Solution

by:
acdecal earned 0 total points
Comment Utility
I used the code below:   I little clunky but it works.


Sub consolidate()
Dim D1 As String, D2 As String, C1 As String, C2 As String
Dim i As Long, MH As Long, j As Long



j = 1
For i = 2 To 600
 MH = Cells(i, 3).Value + MH
 C1 = Cells(i, 1).Value

 C2 = Cells(i + 1, 1).Value
If C2 = "" Then GoTo A:
If C1 = C2 Then

 D1 = Cells(i, 2).Value
 D2 = Cells(i + 1, 2).Value
If D1 = D2 Then


Else

Cells(j, 10).Value = C1
Cells(j, 11).Value = D1
Cells(j, 12).Value = MH
MH = 0
j = j + 1



End If
Else

Cells(j, 10).Value = C1
Cells(j, 11).Value = D1
Cells(j, 12).Value = MH
MH = 0
j = j + 1


End If
Next i
A:
Cells(j, 10).Value = C1
Cells(j, 11).Value = D1
Cells(j, 12).Value = MH

End Sub
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 22

Expert Comment

by:Flyster
Comment Utility
Here’s one way to achieve the result you’re looking for. Concatenate column A & B with this formula:

=A2&" "&TEXT(B2,"mm/dd/yyyy")

Then go to the Data tab and select Subtotal. In the “At change in” dropdown select the new field.  In the “Add subtotal to” dropdown select Mhs. See attached.
Example1.xlsx
0
 

Author Comment

by:acdecal
Comment Utility
That's a clever way of determining a unique item but would require me to un-concatenation to get it to the format I need. Thanks for your efforts!
0
 

Author Closing Comment

by:acdecal
Comment Utility
As stated
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now