Create a Matrix? in Excel

[remeng]

Hi all,

I am trying to create a data matrix (i think that is what it is called) in Excel 97 to see if combination are occurring to find trending.

|Bob | Pat | Fred
Bob | - | x | x
Pat | X | - | x
Fred | - | x | -

What I want to know is how many time bob and pat work together. How many time bob, pat, and fred work together and so on and so on till I have a list of combination and total occurrences by sum since these are products sold.

The raw records look like this. I did also sort out most of the records that have only 1 product.

Cust | Apple | Pear | Orange
-----------------------------
12 | 1 | 0 | 2
102 | 1 | 1 | 1

apple & orange = 3
Apple & Pear & Orange = 3

This Matrix(?) is going to be 20 x 20. Really large to do this by hand and a huge amount of possible combinations.

I did try a Pivot table without any luck.

Any ideas and help is greatly appreciated.

 

[SkipVought]

hi,

First you ought to NORMALIZE your data from
[tt]
Cust | Apple | Pear | Orange
-----------------------------
12 | 1 | 0 | 2
102 | 1 | 1 | 1
[/tt]
to
[tt]
Cust Fruit Occurence
12 Apple 1
12 Orange 2
102 Apple 1
102 Pear 1
102 Orange 1
[/tt]
faq68-5287

From a NORMALIZED table, you can use the SUMIF() function to generate the customer usage you desire or a PivotTable, as you seem to be familiare with.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[remeng]

Skip,

How do I do that in 97? I don't see that function or nor is it in the help files. Thank you!

 

[remeng]

BTW - I don't have any duplicated customer numbers if that helps.

 

[SkipVought]

Set up the matrix structure in A1 on a new sheet.
[tt]
Cust Apple Orange Pear
12
102
[/tt]
Select ALL the cells and Insert > Name > Name -- Create name in TOP row

Then the formula in B2, using teh SUMPRODUCT() function
[tt]
B2: =SUMPRODUCT((Cust=$K2)*(Fruit=B$1)*(Occur))
[/tt]
Copy B2 thru all rows & columns of the matrix.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[remeng]

Could you show me an example of what the result will look like so I can understand this a little better?

Thanks Skip!

 

[SkipVought]

either use a PivotTable or the SUMPRODUCT() function yields similare results...
[tt]
Cust Apple Orange Pear
12 1 2 0
102 1 1 1
[/tt]
using the forumla posted above.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[remeng]

I think that we are looking for 2 different things. I am sure that I messed up my explanation. Let me try again.

Cust Apple Orange Pear
12 1 2 0
102 1 1 1

I am looking to get the result like this:

Cust Apple Orange Pear
Apple x 2 1
Orange 2 x 1
Pear 1 1 x

Since there are 2 instances that an apple and orange occurs, the count = 2.

since there is only one instance that an orange and pear occur then the count = 1.

I hope this explains it better.

 

[SkipVought]

For which Cust or without regard to ANY Cust?

If the former, where is THAT Cust id referenced?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[remeng]

Without a customer reference.

 

[SkipVought]

I disagree with your results.

[tt]
Cust Apple Orange Pear
12 1 2 0
102 1 1 1
[/tt]
I am looking to get the result like this for Cust 12:
[tt]
Cust Apple Orange Pear
Apple x 2 1
Orange 2 x 1
Pear 1 1 x[/tt]

Rather
[tt]
Cust Apple Orange Pear
Apple x 3 1
Orange 3 x 2
Pear 1 2 x
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

We have an even LARGER difference if WITHOUT a Cust reference.

My results
[tt]
Cust Apple Orange Pear
Apple X 5 3
Orange 5 X 4
Pear 3 4 X
[/tt]
My formula in B2
[tt]
B2: =IF($A2=B$1,"X",SUMPRODUCT((Fruit=$A2)*(Occur))+SUMPRODUCT((Fruit=B$1)*(Occur)))
[/tt]
keeping in mind that I am referring to the NORMALIZED table structure using named ranges...
[tt]
Cust Fruit Occur
12 Apple 1
12 Orange 2
102 Apple 1
102 Pear 1
102 Orange 1
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[remeng]

Skip,

What I am trying to answer is how many customers ordered a combination and not really how many products per combination were ordered. hope that helps. I know this seems confusing

 

[SkipVought]

how many customers ordered a combination

[tt]
Cust | Apple | Pear | Orange
-----------------------------
12 | 1 | 0 | 2
102 | 1 | 1 | 1
[/tt]
In column E
[tt]
E2: =SUMPRODUCT((B22>0)*1)
[/tt]
Or use a PivotTable using the Count of Fruit on my NORMALIZED table as source.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[remeng]

Skip I have 20 different products to go through. That is a matrix of 20 x 20 with 20 squares voided. Is that going to work since there is a large volume of combinations?

The help file on sumproduct is really basic and honorable at explaining what it does. I think I am total lost right now on the best approach.

 

[SkipVought]

Why even use a matrix to answer your "how many customers ordered a combination" question.

I'm proposing using your 20 column source table OR normalizing your data and using a PivotTable Count of Fruit.

???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[remeng]

I guess it comes down to me not fully understanding what the normalizing of the data actually dues. Like I said before 97 doesn't have that particular function and even if it did I don't understand what it actually does. I guess I just need some more help in understanding it.

thank you,

mike

 

[SkipVought]

???

Like I said [highlight]before 97[/highlight] doesn't have that particular function

BEFORE. Are you using Excel 95!?

Excel 97-2003 has very minor differences. You previously has said you were working 97, which DOES HAVE the PivotTable features described.

Your data is not normalized, which mean that it is a lot more difficult to analyse the data. However, I gave you a solution using your source data, posted on 20 Feb 12 14:59 with formula...

[tt]
Cust | Apple | Pear | Orange
-----------------------------
12 | 1 | 0 | 2
102 | 1 | 1 | 1

In column E

E2: =SUMPRODUCT((B22>0)*1)

[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[remeng]

Skip,

yes it is 97. I guess I just don't know what normalized means. Can you explain that and what it does to the data?

Thank you!

 

[SkipVought]

Forget normalize for now. Seems we can't handle two concepts and focus on a solution.

Does the formula posted directly above, applied to your current source data (meaning adjusted to the full range of your data), answer your question?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}