How to reorganize and sort the data to get top 3 value of each group

[BoardWalk]

Hello,

Some of our customers have their own specific price list and I need to reorganize it to make a standardized price list by level. For example, if an item 'Uniform A' is in many of our customers price list, I'd like to get the top 3 values and put the price information in those new three pricelist, let's call it 1) Gold 2) Diamond 3) Platinum for convenience.

What makes it more complicated is that I want to exclude the specific price that is applied to only one or two customers because that doesn't really represent the general pricelist.

The data structure I have looks like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Item[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]G1[/TD]
[TD]Uniform A[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Uniform B[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]G2[/TD]
[TD]Uniform A[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Shoes A[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Shoes B[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Shoes C[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]G3[/TD]
[TD]Uniform A[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Uniform B[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


And what I need to see in the final version looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Pricelist[/TD]
[TD]Item[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Platinum[/TD]
[TD]Uniform A[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Uniform B[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Diamond[/TD]
[TD]Uniform A[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Uniform B[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Would there be an efficient way to reorganize the data? Please help me. You can find the real data in the link. I've done my best and you can find the order of my work under each tab.

https://drive.google.com/file/d/0B6f6OaKsH5OJQkx0MmlMVU5PSkk/view?usp=sharing

 

[sergioMabres]

Hi Walk
You could use something like

{=SMALL(IF(Workbook!$C$2:$C$163='final version'!C2,Workbook!$D$2:$D$163),B2)}

I have modified your sheet to your need for some codes, as an example, I had to modify also sheet Workbook because you need the code in every row, that is to say like in a database, have a look at the file, the file is at:

https://onedrive.live.com/redir?resid=52B7AEC35713C28B!1785&authkey=!AIfwLGfGwe2xM4g&ithint=file%2cxlsx

cheers
Sergio

 

[BoardWalk]

Hello Mr.Sergio,

Thanks for your help. It absolutely made my life much easier.

I have one more question though. If I want to count top 3 values that is applied to more than just one customer (column E in sheet 'Workbook' that you sent me), what else should I do?

 

[sergioMabres]

Hi Board
Sheet "final version" shows top 3 for a code (I think item is the product column C) not matter what the customer is, top 3 values that is applied to all customers in the sheet workbook.
Cheers
Sergio