So I need to build a matrix, and I suspect "SUMPRODUCT" will be friend on
this, but boy, is it scary.

I have a list of SKUs, the customer(s) who bought each, and the quantity
each customer bought:

SKU 1, WalMart, 5000
SKU 1, Target, 3500
SKU 2, KMart, 4200
SKU 2, WalMart, 6700
SKU 2, Dollar Tree, 2100
etc...

I'd like to:
- Rank my customers based on total purchases, and then break them into
deciles;
- List each SKU down the left hand column, then, going across, show how much
of each product was bought in each decile

In other words, for SKU 1 - how many dollars were spent on that SKU by my
"Top Ten" customers, by my "Second Ten", and so on.

(Note - "Top Ten" is my top ten PERCENT)

Any quick and easy way to pull this off? I have in my mind what I'd like
tosee, but don't have an example of where else something similar has been
done, so links are a big help as well.

Thank you,

Pivot Tables should give you what you want.

"RJB" wrote:

So I need to build a matrix, and I suspect "SUMPRODUCT" will be friend on
this, but boy, is it scary.

I have a list of SKUs, the customer(s) who bought each, and the quantity
each customer bought:

SKU 1, WalMart, 5000
SKU 1, Target, 3500
SKU 2, KMart, 4200
SKU 2, WalMart, 6700
SKU 2, Dollar Tree, 2100
etc...

I'd like to:
- Rank my customers based on total purchases, and then break them into
deciles;
- List each SKU down the left hand column, then, going across, show how much
of each product was bought in each decile

In other words, for SKU 1 - how many dollars were spent on that SKU by my
"Top Ten" customers, by my "Second Ten", and so on.

(Note - "Top Ten" is my top ten PERCENT)

Any quick and easy way to pull this off? I have in my mind what I'd like
tosee, but don't have an example of where else something similar has been
done, so links are a big help as well.

Thank you,

You know, I should always try Pivot Tables... I always try really complicated
stuff and someone comes along and says "Pivot Table" and we're done in a
flash.

EXCEPT...

Here, I can't simultaneously do my deciles AND matrix, can I?

"Vicky" wrote:

Pivot Tables should give you what you want.

"RJB" wrote:

So I need to build a matrix, and I suspect "SUMPRODUCT" will be friend on
this, but boy, is it scary.

I have a list of SKUs, the customer(s) who bought each, and the quantity
each customer bought:

SKU 1, WalMart, 5000
SKU 1, Target, 3500
SKU 2, KMart, 4200
SKU 2, WalMart, 6700
SKU 2, Dollar Tree, 2100
etc...

I'd like to:
- Rank my customers based on total purchases, and then break them into
deciles;
- List each SKU down the left hand column, then, going across, show how much
of each product was bought in each decile

In other words, for SKU 1 - how many dollars were spent on that SKU by my
"Top Ten" customers, by my "Second Ten", and so on.

(Note - "Top Ten" is my top ten PERCENT)

Any quick and easy way to pull this off? I have in my mind what I'd like
tosee, but don't have an example of where else something similar has been
done, so links are a big help as well.

Thank you,

By deciles, if you mean finding the top 10% or 20%, I am not seeing in Pivot
tables. What about matrices? What output do you want?


"RJB" wrote:

You know, I should always try Pivot Tables... I always try really complicated
stuff and someone comes along and says "Pivot Table" and we're done in a
flash.

EXCEPT...

Here, I can't simultaneously do my deciles AND matrix, can I?

"Vicky" wrote:

Pivot Tables should give you what you want.

"RJB" wrote:

So I need to build a matrix, and I suspect "SUMPRODUCT" will be friend on
this, but boy, is it scary.

I have a list of SKUs, the customer(s) who bought each, and the quantity
each customer bought:

SKU 1, WalMart, 5000
SKU 1, Target, 3500
SKU 2, KMart, 4200
SKU 2, WalMart, 6700
SKU 2, Dollar Tree, 2100
etc...

I'd like to:
- Rank my customers based on total purchases, and then break them into
deciles;
- List each SKU down the left hand column, then, going across, show how much
of each product was bought in each decile

In other words, for SKU 1 - how many dollars were spent on that SKU by my
"Top Ten" customers, by my "Second Ten", and so on.

(Note - "Top Ten" is my top ten PERCENT)

Any quick and easy way to pull this off? I have in my mind what I'd like
tosee, but don't have an example of where else something similar has been
done, so links are a big help as well.

Thank you,

Yeah, that's exactly what I wanted.

I took the data table, subtotaled by item, sorted, accumulated, and divided
by the total to get the accumulated percent of each. (7%, 12%, 15%, etc.).

Then I added a column to multiply the percentage by ten and ROUNDUP. That
gave me the stratification (1st percentile, 2nd, etc.).

THEN I did your pivot table, and it all fell into place.

It's an ugly flippin' thing, but it did what I wanted.

Thank you.