A real challenge for Dsum / Sumproduct in VBA 1

[axel67]

Hi all,
Here is a real challenge (which I can't cope)....
I have the folowing table in Excel and I would like to report on combined criteria i.e. via DSUM or SUMPRODUCT:

A B C
1 Product Staff Sales
2 P1 S2 10
3 P2 S3 5
4 P1 S1 15
5 P3 S1 20
6 P4 S3 5
7 P3 S2 10
8 P1 S3 15

For Example I would like to know sales where prodcut group = "Luxury" (product P1 & P4) AND the sales team = "North" (Staff S2 and S3).

This is so far "usual" and "old" stuff, but here is the challange:

So rather than creating multiple condition lines in my DSum or Sumproduct which queries each individual condition, I would like to use the DSum or Sumproduct function in VBA and make a check if the line item is part of a group.

I had the following thoughts:

1.) create a kind of "Where Product IN ('P1','P4) and Where Staff IN ('S2', 'S3')" statement like:
SUMPRODUCT((A2:A8=F2:F3),(B2:B8=G2:G3),(C2:C8))
in cells "F" and "G" I would have the items to compare to.

2.) combine i.e. all the products of my product group to a string like "P1/P4" and the use the SEARCH function within the Sumproduct criteria, like =SumProduct((A2:A8=SEARCH(A2:A8,"P1/P4",1),(C2:C8)) but well the "dammed thing" does not inderstand that...

The reason why I am trying to do the above is that I can have many (100+) criteia that I would like to match within one dimension. For example I have 5000 different prodcut codes and I want to match sales to those that correspond to product group "Luxury" which contains 650 product codes.

Above did not get me anywhere.....If someone has an idea, it would be welcome. Or if someone could point me to a source where i can find information about using SQL on an Excel range within VBA.

I think a solution to this would be appreciated by many people, as it would make analysis so much easier.

Thanks for any hints,
Axel

 

[xlbo]

you need to add an extra column at detail level that groups your products together - you then do a simple sumproduct / pivot table based on those groups

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[axel67]

Thanks Geoff, but I would like to avoid adding extra help calculation columns.
That's what I have done alllways in the past, but I actually have different hierachical product groups. That means that I would need to create as many help columns as I have hierachy levels within one column dimension (meaning 10 hierachy levels = 10 help columns). And this would just be to report on the product hierachy. Don't even think about to have combined reporting on product, customer and country...
You will quickly have more help columns than initial Excel data table columns.
This is why it would be handy to have a possibility within just one single formula - without any parallel help coulmn(s) to the Excel database - either a type of:

- "In" statement or
- feeding into the function a CONCATENATE() of all the items that are contained in the product group and that the look-up formula compares via a "search" to that string.

I know it is not easy, but that's why it is posted here....

Thanks again,
Axel

 

[xlbo]

don't get why you would need 10 helper colums.....

If you need 10 helper columns to create your product hierarchy, how on earth do you think you are going to be able to do it in one sumproduct formula !!

Please define the logic behind your grouping as that will determine the bast way to do this - some samples of products and what group(s) they belong to would also be helpful

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[combo]

It would be quite easy in relational database, as access for instance.
If you like to keep all in excel, you can use its limited power of joining tables in MS Query.
Using your data (assuming the sheet is named "Sales"), I would first add two new sheets:
1. "Products" with "Product" and "Group" columns and appropriate data (P1,P2 etc in first, Luxury, Stantarg etc. in second column),
2. "Teams" with "Team" and "Region" columns.
Next, after saving and closing the worksheet, the new one can store reports. With external database query and pivot table with external data (excel file), and MS Query switched on you can link tables and extract data, either in plain or pivot table.
Anyway, the price is limited flexibility and some problems when moving file.

combo

 

[axel67]

Thanks for the responses! Combo do you have any link I can use for reading to make myself knowlegable about the MS Query in VBA?

I have pulled out an article that describes "Computed Criteria" in DSum. (

http://support.microsoft.com/kb/282851/

)

They use the following example:

In the formula that uses the computed criteria, you must use a relative reference to the first record in the field of the database that you want to reference. In the following example, the formula contains a relative reference to cell B2 in the formula =MONTH(B2)=MONTH($H$3).
****************************************

Now it would be of course great if it works when I genereate a cell that combines for example all products in one long string and then and then use something like this as computed criteria:
=SEARCH(A2,"P1/P4",1)>0

Geoff, I have put just a conceptual sample table and hierachies. It's just not very handy to make these additional help columns I think. I'd love to give the user one formla "that does it all" (maybe not making the coffee.... ), without a lot of additional "maintenance" on the database.


Product SalesStaff Country Sales
P1 S2 UK 10
P2 S3 France 5
P1 S1 Italy 15
P3 S1 Florida 20
P4 S5 Australia 5
P3 S2 Sweden 10
P1 S3 UK 15
P1 S6 France 10
P2 S1 Italy 5
P1 S2 Canada 15
P3 S2 Australia 20
P5 S3 Denmark 5
P3 S4 UK 10
P1 S3 France 15



Sales team:
S1 Team North
S4 Team North
S2 Team South
S3 Team South
S5 Team West
S5 Team East
Team South Team South West
Team West Team South West
Team North Team North East
Team East Team North East



Country:
UK Western Europe
France Western Europe
Italy Western Europe
Denmark Nothern Europe
Sweden Nothern Europe
Australia Overseas
America Overseas
Florida America
Canada America


P1 Luxury
P2 Mass market
P3 Mass market
P4 Luxury
P5 Basic
Mass market Standard
Luxury Standard



Cheers,
Axel

 

[xlbo]

not sure I really get what you are trying to do - seems to me that you would be better off with a few helper columns rather than trying to do something very complicated - keep it simple.

All you would need is a couple of basic vlookups to populate your groups from lookup tables and then a pivot table would give you all you need to know very quickly and easily...very little maintenance for users - all they would need to do is modify the lookup tables if anything was changed / added....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[axel67]

I admit it is not easy to understand. If I come accross a good solution, I will share it here in the forum.

Thanks for your thoughts so far.

Cheers,
Axel

 

[combo]

Excel as relational data source:

http://www.exceluser.com/explore/msquery1_1.htm

Another excellent excel site:

http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/build_queries.html

Skip Vought's TT FAQ: faq68-5829,
MS short info:

http://office.microsoft.com/en-us/word/HP051902761033.aspx

combo

 

[axel67]

I found an interesting solution, for those who might be interested....
In the microsoft help for the DSUM function it shows that you can use "Criteria created as the result of a formula" in the DSUM function.

see article

http://office.microsoft.com/en-us/excel/HP052090691033.aspx#Example7

)
****************************************
Criteria created as the result of a formula
You can use a calculated value that is the result of a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) as your criterion. Remember the following important points:

The formula must evaluate to TRUE or FALSE.
Because you are using a formula, enter the formula as you normally would, and do not type the expression in the following way:
=''=entry''

Do not use a column label for criteria labels; either keep the criteria labels blank or use a label that is not a column label in the range (in the examples below, Calculated Average and Exact Match).
If you use a column label in the formula instead of a relative cell reference or a range name, Excel displays an error value such as #NAME? or #VALUE! in the cell that contains the criterion. You can ignore this error because it does not affect how the range is filtered.

The formula that you use for criteria must use a relative reference (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.) to refer to the corresponding cell in the first row (in the examples below, C7 and A7).
All other references in the formula must be absolute references (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.).
The following subsections provide specific examples of criteria created as the result of a formula.

******************

I was looking for a kind of "In List" or "In String" functionality for the DSUM formula, that would return boolean (true/false). This does not exist within Excel standard functions. As such I have written two UDF functions myself:

Option Compare Text ' That is, "AAA" is equal to "aaa".

Function InSlashString(DBStartCell As String, SlashList As String) As Boolean
If (SlashList Like "*/" & DBStartCell & "/*") Then InSlashString = True Else InSlashString = False
End Function

Function InList(DBStartCell As String, CompareList As Range) As Boolean
Set MyRange = CompareList
InList = False
For Each cell In CompareList.Cells
If (DBStartCell Like cell) Then
InList = True
Exit Function
End If
Next
End Function


The first function checks if a cell value is part of a "/" slash list:
Example:
CellValue slashlist
axel /Mary/Peter/Axel/Tom/

The second funtion checks if a cell value is part of a range list:
CellValue Range List
Axel Mary
Peter
Axel
Tom

You can use both funtions instead of the "Exact()" example of the microsoft help.

The great thing is that with this technique you can easily define several "or" criteria for different dimensions within the DSUM function, WITHOUT generating a huge number of criteria lines as input.

So a GREAT SOLUTION but unfortuantely BAD PERFORMANCE (no much better that ordinary array formulas)!

For those who are interested, I can send them a sample file.

I am looking now if it is not possible to use the DSUM in VBA and to handover a programatically generated argument list for the criteria (especially Arg3):

Application.WorksheetFunction.DSum(Arg1, Arg2, Arg3)

Anyone has an idea?

Greetings,
Axel