Non Array Summary

[seguin85]

Hi,

I get a data summary from another department and I am trying to pull certain data from it.

It is setup similar to the following:

	A	B	C	D	E	F
1	Job#	Activity#	Start Date	End Date	Hours
2	5-6-7	123	6/20/17	7/18/17	200
3	5-6-7	555	5/15/17	8/15/17	500
4	5-6-7	123	8/8/17	8/1/17	300
5	5-6-7	123	7/16/17	8/22/17	100
6	5-6-7	555	6/10/17	9/10/17	300

<tbody>
</tbody>

On the summary sheet, I want to be able to type in the job number and activity and it will populate the earliest start date for that combination (ignoring 0 and blanks), the latest end date and the sum of hours. I figured out a way to get it done by an array formula but it is slow down my.

	A	B	C	D	E
1	Job #	Activity	Start Date	End Date	Hours
2
3
4

<tbody>
</tbody>

Help would be appreciated!

 

[DRSteele]

This might help. There really are only a couple of methods for escaping the need for an array calculation; but arrays are the most efficient method anyway.

Note that the 5-6-7 must be entered as text.

	A	B	C	D	E
1	Job#	Activity#	Start Date	End Date	Hours
2	5-6-7	123	6/20/2017	7/18/2017	200
3	5-6-7	555	5/15/2017	8/15/2017	500
4	5-6-7	123	8/8/2017	8/1/2017	300
5	5-6-7	123	7/16/2017	8/22/2017	100
6	5-6-7	555	6/10/2017	9/10/2017	300
7
8
9	Look up Job#	Look up Activity#	Earliest Start Date	Latest End Date	Total Hours
10	5-6-7	123	6/20/2017	8/22/2017	600

<tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula C10 =1/AGGREGATE(14,6,1/((A10=A2:A6)*(B10=B2:B6)*C2:C6),1) D10 =AGGREGATE(14,6,(A10=A2:A6)*(B10=B2:B6)*D2:D6,1) E10 =SUMPRODUCT((A10=A2:A6)*(B10=B2:B6)*E2:E6) <tbody> </tbody>

<tbody>
</tbody>

 

[63falcondude]

This uses array formulas for the earliest start date and latest end date. Just make sure that you limit your ranges and do not use whole column references. You could also format the data as a table to limit the ranges.

I formatted column A on both sheets as Text to ensure that 5-6-7 is not seen as a date.

Excel 2010
	A	B	C	D	E
1	Job #	Activity	Earliest Start Date	Latest End Date	Hours
2	5-6-7	123	6/20/2017	8/22/2017	600
Summary

Cell Formulas
Range	Formula
E2	=SUMIFS(Sheet1!E$2:E$6,Sheet1!A$2:A$6,A2,Sheet1!B$2:B$6,B2)
C2	{=MIN(IF((Sheet1!A$2:A$6=A2)*(Sheet1!B$2:B$6=B2)*(Sheet1!C$2:C$6>0),Sheet1!C$2:C$6))}
D2	{=MAX(IF((Sheet1!A$2:A$6=A2)*(Sheet1!B$2:B$6=B2),Sheet1!D$2:D$6))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

Try MINIFS and MAXIFS if you have them.

 

[seguin85]

Aladin: I don't have minifs and maxifs...

63falcon and DRSteele: Is there a way to determine what method would use the least amount of resources? This is going to be a file I will be using a lot and would like it to remain snappy. I have no problem with arrays but it seemed like my file size was growing quickly as I added a number or arrays.

 

[Rick Rothstein]

63falcon and DRSteele: Is there a way to determine what method would use the least amount of resources? This is going to be a file I will be using a lot and would like it to remain snappy. I have no problem with arrays but it seemed like my file size was growing quickly as I added a number or arrays.

Are you willing to try a macro solution? I am not sure how fast it will be for you, but it won't fill your summary sheet with formulas, only the results as constants. If so, the following macro assumes your data is on Sheet1 and the output that you want is on Sheet2, but you can change those by changing the sheet names that I highlighted in red in my code below. The way this works is your summary sheet is assumed to have the headers filled in... what you need to do is list all of the Job numbers and Activity numbers you want summaries for in Columns A and B of your summary sheet, then run the macro which will, in turn, fill in the early start and late end dates and total hours in Columns C:E.

Sub GetJobActivitySummary()
  Dim R As Long, FM As Long, Sum As Long
  Dim Early As Date, Late As Date, FindWhat As String
  Dim DataWS As Worksheet, SummaryWS As Worksheet
  Dim FindMe As Variant, Data As Variant, Result As Variant
  Set DataWS = Sheets("Sheet1")
  Set SummaryWS = Sheets("Sheet2")
  SummaryWS.Range("C2:E2").Resize(SummaryWS.UsedRange.Rows.Count).ClearContents
  FindMe = SummaryWS.Range("A1").CurrentRegion
  Data = DataWS.Range("A1").CurrentRegion
  ReDim Result(1 To UBound(Data), 1 To 3)
  For FM = 2 To UBound(FindMe)
    FindWhat = FindMe(FM, 1) & "/" & FindMe(FM, 2)
    Early = #9/9/9999#
    Late = 0
    Sum = 0
    For R = 2 To UBound(Data)
      If Data(R, 1) & "/" & Data(R, 2) = FindWhat Then
        If Data(R, 3) < Early Then Early = Data(R, 3)
        If Data(R, 4) > Late Then Late = Data(R, 4)
        Sum = Sum + Data(R, 5)
      End If
    Next
    Result(FM - 1, 1) = Early
    Result(FM - 1, 2) = Late
    Result(FM - 1, 3) = Sum
  Next
  SummaryWS.Range("C2").Resize(UBound(Result), 3) = Result
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (GetJobActivitySummary) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

To make things simpler for you (especially if you will need to call this macro often), when you select the macro from the list, before clicking Run, you can click the Options button and assign a keyboard shortcut to it first and then use that keyboard shortcut the next time you want to run the macro. Alternately, you can assign this macro to a Form Control Button so that you can just click it in order to run the macro.

 

[seguin85]

Are you willing to try a macro solution?

Hi Rick,

I would be willing to run macros, I didn't even think of that as a solution. I do have some experience so I imagine I can get your code to work. This way whenever I dump their data into the sheet I just run the macro one time and it will keep my workbook working much more quickly.

Thanks!