Looking for what is the best formula to look for customers, products and nth value...

[pasqui83]

Good evening,

I'm normally pretty excel and formulas but this has left me a little stumped.

I need to create the following but I'm not sure what the best way of going about this is. Apologies if this may sound simple. I did try to look on here but I could quite find anything that matched what I was trying to do.

I have alot of data (around 8,000 rows), within those rows I have the following (I'll just list the vital stuff).

• Customers (Over 70 unique)
• Products (Over 200 unique)
• Year
• Month
• Group (over 60 unique)

Group is simply where those Products are grouped to.

What Do I need help with?

I am trying to find the following:

A) Top 10 customers with the highest number of Products.

What this means is if Customer A has 60 products, list me those Products from largest to smallest.

Example:
1) Customer A
a) Product - Mobile Phone
b) Product - Telephones
c) Product - Network switches etc...


B) The highest number of Products by Customers

The reverse of A.

Example:
1) Product - Mobile Phone
a) Customer A
b) Customer C
c) Customer D

I'm not sure the best way to gather this dataset to get the information needed. I tried to use a pivot table which counts all the Customers and Products, but it's not quite what I need, but I'm not sure if the information there I can interrogate that?

Trying to think of key steps here but was hoping you guys can give me some pointers?

Currently, my thoughts are:

1) I first need to create the top 20 listing of customers so will need to do a countif to establish this. I'm not sure if it I can create a separate data for this or can use the same one? I can't seem to find anything that shows I can use countif with large so I am thinking I will need to calculate this first.

2) Within the top 20, I then need to do the same as the first but only for the products from largest to smallest. Again I'm not sure of the best way to do this.

3) Within this, I also need to have the Year criteria, essentially if there is a value, then make sure to find only all those customers/products within that year, otherwise return me everything.

Any help or advice on this will be much appreciated.

Many thanks

[pasqui83]

Please IGNORE this post.. I made this much more complicated than it should have been!!!

Good old Pivot table! I just wasn't using it correctly... It's been a long week!