Excel Grouping Data to Summarise

[mardecl1]

I am trying to find out the average number of different product SKUs that a list of customers buys from me.

I have a spreadsheet (attached) that lists each order across a row. Each account could appear multiple times because they may have ordered on several occasions, with different or the same SKUs on each order. The account number is in column A, the ordering month in column B and then all of the SKUs in C-AK, including number of cases ordered each time.

I am trying to figure out how to summarise the data for each customer to just show how many different SKUs they ordered across the whole period so that I can then work out the average for the whole base of customers.

Please can someone show me how to work out how many different SKUs each unique account number ordered?

(There are 30,000+ rows in the full spreadsheet but I've just uploaded a sample)

Thank you so much in anticipation of a solution....this is driving me mad!

Also posted at http://www.msofficeforums.com/excel/...tml#post108596

[Pepe Le Mokko]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!



Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

http://www.msofficeforums.com/excel/...iple-rows.html

[mikeTRON]

EDIT: will repost after you fix the issue posted above.

[mardecl1]

Hello, Thank you for letting me know. I have now added a link

[avk]

I have attach file. I think you required account number, monthwise how many sku qty order.
using sumproduct with all three criteria calculated. I arranging "Data Validation" for SKU.
I entered formula some of account numbers, you can copy paste upto last record.

[mardecl1]

Hello, Thank you for offering to help. I don't think the above has worked though, I'm afraid. So, using account number 370474 as an example, they took 8 different SKUs over the months of March-July but your table shows a result of 2. Similarly, 10064949 took 12 different SKUs so the result should be 12. Apologies if I haven't explained my request very well. Does this make more sense now?

[avk]

need to change data in sheet3 from cell "P1" which is "Data Validation" "DropDownList"
Try it.

[avk]

need to change data in sheet3 from cell "P1" which is "Data Validation" "DropDownList"
Try it.

[mardecl1]

Hello again,
Ultimately, I need to be able to state the average number of different SKUs that a customer takes over a period of time. For example, I might establish that, on average, our customers tried 13 different SKUs over the course of 6 months. So, I don't need to know how many took each SKU...I just need to know how many different ones each customer bought. Is this possible? Once again, I appreciate your patience and assistance...

[JeteMc]

If I understand correctly you are looking to see how may different SKU's each customer bought and then average that.
First step could be to apply the subtotal function, from the ribbon on the Data tab. Subtotaling to break by account number and then count for each SKU.
Add a column (AN) which will populate with the total number of SKU's each customer has bought over the period using the following formula (this matches the numbers you gave in post #6):

Please Login or Register  to view this content.

You could then average the values in column AN using the AVERAGE function.
Let us know if you have any questions.

[newdoverman]

I have created a Pivot Table that summarizes the data for each account. I added a manual calculation on the right side of the pivot table (I hid many columns) that calculates the total unique skus ordered over the period. This calculation was done on the totals row for each account. The formula used for this calculation is as follows.

Please Login or Register  to view this content.

This formula produces an error if the word "Total" isn't found in the referenced cell in column A. If not an error, the formula counts the values greater than 0.

The average of the unique counts for each account is to the right of the Pivot table.

[newdoverman]

Here is a method that uses a listing of unique account numbers and calculates from that.
The unique listing of account numbers is accomplished by selecting the original list of accounts and pasting onto a new worksheet then while selected use on the Data tab, Remove Duplicates. The row of Sku numbers was copied and pasted starting in B1 where the unique listing of account numbers has been created.
This formula is entered in B2 and copied across and down:

Please Login or Register  to view this content.

Then, its is a matter of counting the values that are greater than 0 using this formula in AK2 and filled down.

Please Login or Register  to view this content.

For the Average, select the values in column AK and use this formula

Please Login or Register  to view this content.

[mardecl1]

AMAZING! Thank you so much. This is absolutely amazing!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.