sumproduct and unique fields

[jerry12302]

I have a sample table below to illustrate my problem, the numbers in red are what I'm looking for a formula to calculate, probably a sumproduct, although I'm open to any suggestions.

Weight through conduits are to be summed only once if they are repeated in any particular month. (DEF1000 is in building 2 and 5 in March, but its weight should only be summed once) I hope this makes sense.

systemsfile.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1								Weight	(countconduitsonlyonceifduplicated)
2	Building	Conduit	Type	Weight	Added	Removed			Jan-07	Feb-07	Mar-07	Apr-07
3	BLDG1	ABC999	Metal	100	Dec-06			Metal	100	300	300	100
4	BLDG2	DEF1000	Plastic	100	Mar-07	Jan-08		Plastic	300	400	400	300
5	BLDG3	ABC1001	Metal	200	Mar-07
6	BLDG4	ABC1002	Plastic	200	Apr-06			Detailforillustrationpurposes:
7	BLDG5	DEF1000	Plastic	100	Sep-06	Apr-07		Metal	ABC999	ABC999	ABC999	ABC999
8	BLDG6	ABC1000	Plastic	100	Jun-07					HIJ3000	HIJ3000
9	BLDG7	HIJ3000	Metal	200	Feb-07	Mar-07
10	BLDG8	ABC5000	Plastic	100	Feb-07			Plastic	ABC1002	ABC1002	ABC1002	ABC1002
11	BLDG9	HIJ3000	Metal	200	Feb-07				DEF1000	DEF1000	DEF1000
12										ABC5000	ABC5000	ABC5000
Sheet3

Any suggestions would be appreciated.

 

[Fazza]

Great, Jerry.

Please check and post both the results you expect and those you receive.

I get these results

Type Jan Feb Mar Apr
Metal 100 300 500 500
Plastic 300 400 400 400


You have it working so the VBA is really for info. Fazza

Sub CreateRefreshableQueryTable()

  ' "sumproduct and unique fields" thread at
  ' http://www.mrexcel.com/forum/showthread.php?p=1483747

  'Code to create the query table.
  '
  'Only need be run once. After it is created, the table can be refreshed like a pivot
  'table - data, refresh when the active cell is within the table. Or the exclamation
  'point icon on the toolbar. (When updating be sure the named ranges are up to date
  'with any changes to the source data! This could be easily done with VBA, BTW.)

  'Assumes named ranges for the data tables, not dynamic.
  'Names are "MainData" and "MonthsList"
  'Referenced table header names are.
  ' In MainData - "Conduit", "Type", "Weight", "Added", "Removed"
  ' In MonthsList - "TheMonth"

  Dim strConn As String
  Dim strSQL As String
  Dim wksNew As Worksheet

  strConn = Join$(Array("ODBC;DSN=Excel Files;DBQ=", _
      ActiveWorkbook.FullName, ";DefaultDir=", ActiveWorkbook.Path, _
      ";DriverID=790;MaxBufferSize=2048;PageTimeout=5;"), vbNullString)

  strSQL = Join$(Array( _
      "TRANSFORM Sum(A.Weight)", _
      "SELECT A.Type", _
      "FROM (SELECT DISTINCT MD.Conduit, MD.Type, MD.Weight, ML.TheMonth", _
      "FROM MainData MD, MonthsList ML", _
      "WHERE MD.Added <= ML.TheMonth AND (MD.Removed Is Null OR MD.Removed>ML.TheMonth)) A", _
      "GROUP BY A.Type", _
      "PIVOT A.TheMonth"), vbCr)
  
  Set wksNew = Worksheets.Add

  With wksNew
    With .QueryTables.Add(Connection:=strConn, Destination:=.Range("A1"), Sql:=strSQL)
      .Refresh BackgroundQuery:=False
    End With
  End With
  Set wksNew = Nothing
  
End Sub

 

[Fazza]

Another question, can the result set be placed into the same spreadsheet the tables are in?

Yes, for sure. It is as you said - change the source data and then refresh the table. It is as simple as that. Can only suggest you try again...

 

[Fazza]

The only problem I see is that it is not accounting for the removed field. Metal for March, for example, should be 200 less because 200 was removed in March. Is there a way to account for this?

Jerry,

There must be an error/misunderstanding in my interpretation of the logic/rules.

So, it will be a matter of understanding the correct rules and modifying the SQL to suit.

Can you please review the requirement, and the sample data and results, and even the SQL and results it generates, to understand where the misunderstanding has occured? I'll also start from the beginning to see if I can see where I've gone wrong.

regards, Fazza

 

[Fazza]

Jerry,

I don't understand the March metal result.

My understanding is to include original worksheet rows
row 3 ABC999 weight 100
row 5 ABC1001 weight 200
row 11 HIJ3000 weight 200

These total 500. Your first post has value 300 for March metal.

This sort of difference from your data is what I was referring to at the start of my first post, BTW.

Can you please check and advise?

Regards, Fazza

 

[jerry12302]

You're right, I had been changing some of the data to experiment, and I thought I had it back the way it was originally, obviously not... Your query works great and is accounting for removal dates. It's also adding only the unique conduit values, in case there are duplicates, which is correct.

Thanks for your help, this is going to save me a lot of work and time. Now I just have to figure out how the SQL works in case I want to use it in other applications as well (I'm gonna need a lot of coffee).

 

[Fazza]

Great, Jerry.

SQL is actually simple enough, for the basic things, anyway. (I'm only a self-taught novice at SQL [and at Excel] and that is enough for what I do.) A little bit of familiarity & practice goes a long way. There are many books on SQL and plenty of websites. Some links below FYI. Regards, Fazza

http://www.w3schools.com/sql/default.asp
http://www.sqlzoo.net/