Excel Sort

[SamDemon]

This is usually a very simple operation i know! Let me tell you what the problem is first: I go to sort data held in column D (company names) using the sort A-Z button. Upon pressing this i am asked whether i wish to expand the selection, to which i reply yes, as columns A-B and G - EF contain values that i always want to be associated with the company name. However when i press sort the values held within the columns G-EF move around. Has anyone seen this, or can anyone help at all??

Thanks in advance

Sam

It's just common sense, shame sense isn't common!

 

[Tiglet]

Haven't come across this myself, is the data in G-EF based on formulae that may be causing different results to be displayed?

Alternatively, manually highlight data in all the rows in cols A-EF then data sort, this may cure it.

Tiglet

[blue]Living on Earth is expensive, but it does include a free trip around the sun every year.[/blue]

 

[SamDemon]

Basically the columns are laid out as follows:-

Column A - Count
Column B - ATOL number
Column C - Company Name
Column D - 1st Total
Column E - 2nd Total
Column F - Cape Town 1st
Column G - Cape Town 2nd

all columns after E represnt a destination, and how the company views it, i.e. a primary or secondary route. if the company represents it as one of their primary or secondary routes a 1 is placed in the cell. These are then all calculated in Column D & E.

Hope this clarifies it a bit more.

Sam

It's just common sense, shame sense isn't common!

 

[Tiglet]

Sounds like it's because there are formulae, without seeing your formulae, I can't really help you but I recommend going through checking all the formulae you have and making sure that you haven't got any that have $ in them etc because that will change results if you are sorting on rows.

Hmmm, my signature's gone funny

Tiglet

[blue]Living on Earth is expensive, but it does include a free trip around the sun every year.[/blue]

 

[SamDemon]

this is the formula to calculate for Primary routes

=F7+H7+J7+L7+N7+P7+R7+T7+V7+X7+Z7+AB7+AD7+AF7+AH7+AJ7+AL7+AN7+AP7+AR7+AT7+AV7+AX7+AZ7+BB7+BD7+BF7+BH7+BJ7+BL7+BN7+BP7+BR7+BT7+BV7+BX7+BZ7+CB7+CD7+CF7+CH7+CJ7+CL7+CN7+CP7+CR7+CT7+CV7+CX7+CZ7+DB7+DD7+DF7+DH7+DJ7+DL7+DN7+DP7+DR7+DT7+DV7+DX7+DZ7+EB7+ED7

I want to sort on the column and not the row, so should i lock the above formula out?

Sam

It's just common sense, shame sense isn't common!

 

[KenWright]

Depends really. Where is that formula? Do you have a formula like that on each row, or is that formula sat on it's own somewhere, and is referring to a set of totals for each column that you want to collate.

By the way, that formula can be replaced with something like this if you prefer:-

=SUMPRODUCT((MOD(COLUMN($F7:$ED7),2)=0)*($F7:$ED7))

If you tied the 0 in there to a cell, eg K1 and put the 0 in the cell, then by changing the value in the cell from 0 to 1, you would be able to sum oodd columns as opposed to even columns, eg:

=SUMPRODUCT((MOD(COLUMN($F7:$ED7),2)=$K$1)*($F7:$ED7))

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Tiglet]

A further thought,

Try hitting F9 after sorting to force Excel to recalc formulae.


Tiglet

Living on Earth is expensive, but it does include a free trip around the sun every year