# How do you Auto arrange cells in excel based on their values?



## lee_1133 (Aug 24, 2002)

Hi,

Got a few questions which tie in to what i am doing so i'll try to explain as best i can.

I want to be able to have excel automatically arrange the order in which it displays the information based on the value or information in a cell. Eg i have a list of things i sell online and I track their performance using the spreadsheet were i have a sheet for every product type which are linked to a master snapshop sheet which has the prices, weight and other info about the items. The product name on this main sheet is hyperlinked to the sheet containing the product and in turn the data in that sheet is formulated back to the main snapshot the aim being as little data input as possible to calculate everything. 
I would like to be able to have the snapshop mainsheet automatically put for example the best performing product at the top of the list. At the moment a have to select the cell and expand the data range but this is erratic as i don't think i do it properly. Sometimes it messes all the data up. I am not that good with excel so i am probably going to be told why don't you do this or that etc but that is ok if anyone has an idea.
Anyone able to help?
Apologies if its difficult to follow but i will give more info if requested.
If someone has a link to a webpage that can help would be great also.

Regards,
lee_1133


----------



## etaf (Oct 2, 2003)

can you post an example
i would thought all you need to use is a sort - but it may not be that simple


----------



## lee_1133 (Aug 24, 2002)

Hi,

What i want it to do is automatically ammend the order of the data to show the most profitable item at the top (without me needing to manually highlight the cells and sort A-Z or Z-A. So that as the sales change so do the order in which they appear in the list. Is it possible?


----------



## The Villan (Feb 20, 2006)

Click on a cell in the column that you want to be your sort column.
On your toolbar there are two sort buttons Ascending and descending. Click on the button that you require. Hey presto the data should be sorted.

This asumes that you do not have blank columns or rows amongst the data that you want sorted. Excel assumes that the first row of data is field headings and leaves that row at the top, and sorts all the other rows below.

As mentioned before, you should really upload an example so that we can see what the real problem is.


----------



## lee_1133 (Aug 24, 2002)

Ok I think you are getting confused. I know how to use the Acend / Decend tab what i want to know is can the data be set to automatically change position based on the data in the cells.
Take this example i am selling 10 items.
item number 5 is the best seller making the most money so i want it to automatically move to the top cell in the list. (No clicking acend / decend) I want it to happen Automatically then if item number 8 become the best seller making the most money when the data chages to reflect this I want item 8 to automatically go to the top of the list.
Hope that helps


----------



## bomb #21 (Jul 1, 2005)

If you want it to happen truly automatically you'll need to use code (AKA macros). You could get _close_ to it with formulas, however that wouldn't be 100% safe unless you could *guarantee* that no 2 products would ever have the same amount.


----------



## The Villan (Feb 20, 2006)

You are asking Excel to read your mind. To my knowledge it isn't designed to do that.

You can write Auto macro's for when you open the spreadsheet. However I really can't see the point when with 2 clicks of the mouse, you have the result you desire.

I have always said that you can do 90% of your work for you with little effort whilst the remaing 10% requires a lot of effort. Where do you draw the line?

Maybe somebody has a simple solution that doesn't require a lot of hard work just to geta simple result


----------



## The Villan (Feb 20, 2006)

Sorry Bomb - I was typing at the same time as you.


----------



## The Villan (Feb 20, 2006)

Or so I thought


----------



## bomb #21 (Jul 1, 2005)

The Villan said:


> Or so I thought


I'm waiting to see where this goes, whether it gets to a point where it would be appropriate for you to link to the pivot table primer you did a while back. :up:


----------



## The Villan (Feb 20, 2006)

Did I? LOL When was that?


----------



## bomb #21 (Jul 1, 2005)

IIRC you did a macro to create a basic p/t on the fly from a large dataset, complete with instructions/explanation in Word.


----------



## The Villan (Feb 20, 2006)

are you referring to this one? See upload


----------



## bomb #21 (Jul 1, 2005)

Yup, 'cept there's no macro(s).  /


----------



## The Villan (Feb 20, 2006)

You have a good memory Bomb. You must be considerably younger than me 

I wa strying to upload the instructions, but getting an error on upload.


You may have a point, but we need to see the spreadsheet so that we take the right direction.


----------



## The Villan (Feb 20, 2006)

Yes thats right - pivot tables save the macro work. However as mentioned before, it would be useful to see the spreadsheet first to see how adaptable it is.


----------



## bomb #21 (Jul 1, 2005)

Usually my memory sucks, but that one stuck because you went to a lot of trouble.

Anyways, the point as far as the OP is concerned:

Yes, you _could_ have a "total of all products table" on a master sheet and write some code so that every time you update figures on the products sheets, the master table is autofiltered (e.g. by "Top 5") to another area which is then sorted descending. But TBH it would (as Villan states) be overkill (and in some respects counter-productive) when you can do the exact same thing *when required* with a click or 3.

rgds,
bomb


----------



## The Villan (Feb 20, 2006)

Glad we agree there Bomb.


----------



## lee_1133 (Aug 24, 2002)

Hi,

I would have thought excel would be able to work out the value of a cell against cells around it (i.e Above and below) then move those cells to the desired position as set by the user i.e Highest number at top for example. If this cannot be done i am interested to know more about using macros instead if anyone would be kind enough to explain.


----------



## The Villan (Feb 20, 2006)

Quote
I would have thought excel would be able to work out the value of a cell against cells around it (i.e Above and below) then move those cells to the desired position as set by the user i.e Highest number at top for example. 
End of Quote

Excel is smart but not that smart. However Excel gives you tools additional to its base programming to allow individual requirements to be programmed.

You still haven't uploaded your workbook(s), so that people trying to help you, can decide on how, if at all possible, your desire can be met. So please can you upload that workbook(s), before we go any further.


----------



## bomb #21 (Jul 1, 2005)

OK, file attached just for fun. There a 3 product sheets, "ItemA/B/C". The *act* of selecting (i.e. switching to) the "Summary" sheet runs the code *Macro1*, which autofilters the top *2* from Summary!A:B to Summary!D1 & then sorts by Summary!E descending.

To test, switch to *ItemA* & change B1 to value *10*. Then switch back to *Summary*.

To see the code for Macro1, press *ALT+F8*, then highlight it & click *Edit*.


----------



## The Villan (Feb 20, 2006)

Nice one there Bomb. Wish I was still involed with the Macro side, but have no need anymore, and have forgotten so much. Still little memories come back when I have a look at the macros.

I was trying to do a similar thing with the if & vlookup functions trying to avoid macros completely, but didn't have enough time, however, not sure if all eventualities would be covered. It was hurting my brain. Not used to it anymore. LOL 

Maybe I will try and get my grey matter pumping into action, just for the fun of it.


----------



## etaf (Oct 2, 2003)

cool work :up:


----------



## bomb #21 (Jul 1, 2005)

Well the thing is ... sometimes I'll say use a "For Each ... Next" loop, then Zack will say "don't, it's ineffective, use AutoFilter". Then I'll *think* "hmm, doesn't _really_ matter if there's not too much data, plus using a loop might start a poster thinking about how code works, structure, syntax, etc.". Although I won't actually *say* anything because, well, basically Zack can kick my butt with code.

Howsoever, in _this_ case AutoFilter is perfect, because you can do "show me top 2" and if 2 values are *equal* for "2nd place" it'll give you 3. So it's *all* good.

Hi Zack.


----------



## The Villan (Feb 20, 2006)

Well it certainly works.

I have given up on the function way. 

It would be nice if there was a function that was something like

=DSORT(Input range,Output range) where the output range could be defined for how many items you want to see and would appear away from the Input range.

I use to create my own functions many years ago, but have forgotten how all of that works now. Not sure if you could create a function like mentioned above.


----------

