Solved

# consolidate data by two criteria

Posted on 2014-10-15
99 Views
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.
Example1.xlsx
0
Question by:acdecal
• 4
• 2

LVL 22

Expert Comment

ID: 40382606
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

ID: 40382811
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

acdecal earned 0 total points
ID: 40382874
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

LVL 22

Expert Comment

ID: 40383521
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

ID: 40383528
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

ID: 40391555
As stated
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

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…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…