Large 2 way pivot analysis

[Ed_Global]

Hello,

I have customer price list information extracted into Excel from our sales order processing system.. It has all my customers and all the SKU's that they buy and associated price. We currently set up a single price list for each customer in the back office system. I know anecdotally there is a lot of customers who have the same price list as another - so a lot of unnecessary duplication.

Into excel I now have a 2 way pivot with product code in the rows and customer in the columns with the price paid in the detail.

I want to work out which customers have the same price list - ie contain the same products and at the same price.

I'm a proficient excel user and used to dealing with big volumes of data but struggling a bit on this one.

I have got a total of 2196 products (rows) and 559 customers (columns).

Help appreciated.

Regards

Ed

 

[Fazza]

hi, Ed

suggest you explain in words how the checking is done. list the steps that need to be done. after that is explained & understood it can be done in Excel

regards, Fazza

 

[Ed_Global]

hi, Ed

suggest you explain in words how the checking is done. list the steps that need to be done. after that is explained & understood it can be done in Excel

regards, Fazza

The standard price list would be contained in a 2 column list column 1 would be the product code and column 2 the price.

A separate table has a list of product codes in the 1st column, the second column is the prices that customer 1 pays for the product (if they buy it). Column 2 has the prices customer 2 pays for the products (if they buy them) and so on up to column 559 which has the prices the 559th and last customer pays for the products.

I want to know which customers pay the standard price (ie the seperate list of prices) for the every single product they buy.

Regards

Ed

 

[Fazza]

thanks, Ed. that helps with the basic idea.

what output is wanted?

it would help to have some sample data - even if only 3-5 records & 3-5 columns. enough information to cover all possibilities - so no data, matching values, non-matching.
both of the input & the output

OK?