Listing unique records

[akuku]

Hello!


I have one excercise which I am approaching for couple of hours unsuccessfully.

Please see the picture attached - http://postimg.org/image/ia4erts5l/


We have units and every each unit has it's specific duration. There is a huge number of units and I need to prepare the summary showing most common duration (Y1) without listing all unit with this most common duration and all units with different duration (G,H,K).

The way of showing it is not so important as you can see 3 different methods to do so in the picture. The units and duration may vary after updating the sheet.


I know that we can resolve this by playing with filter and sorting. I need functions, to make it automated process which will find the most common duration and list under this common other units with different durations.


I hope that it is understandable.


Thanks for help in advance!

 

[crouchsw]

I would use a pivot table. There are going to be other ways, but this will likely be the simplest.


https://docs.google.com/file/d/0B0di3bZpuEb0dEpkOUdKTGFpYjg/edit?usp=sharing

 

[jeffreyweir]

Can you upload a sample sheet?

 

[akuku]

I am not sure about this pivot table, since it needs to be automated, I have to just generate the numbers and have this summary.


But, I have some general idea which you can find in here http://www.speedyshare.com/ezkja/market.xlsx


I got the most common duration and now I think it'll be feasable to obtain those duration and units by offset and matching functions.


I'll try to send the full file tomorrow but I really need some sleep. Maybe some of you will find better solution

 

[crouchsw]

Add VBA script to refresh the pivot table automatically:

[pre]

sub worksheet_change()
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
end sub

[/pre]

 

[jeffreyweir]

akuku - can you post the file somewhere else? The speedyshare service requires me to download an exe file, and I can't do this.

 

[SirJB7]

@jeffreyweir

Hi!

Click on the filename, between the text "Download" with stars below and the price-alike orange label with the size, it downloads directly.

Regards!