understanding sorting values in excel

Blakespops

I have 3 values in column a-c (1.0,2.0,2.1) set out over 27 rows covering every possible combination but I have to extend this to seven columns making sure in any three columns all possible combinations are covered. my question is apart from spending hours trial and error sorting this is there any way I can use something like conditional formatting to sort the values for me?

buglawton

I suggest at any rate, install this free Excel add-in
http://www.asap-utilities.com/
It can do all manner of sorting, filtering and editing tricks with your open Excel sheet.

EchoLocation

Conditional Formatting is, unsuprisingly, for Formatting cells based on conditions. It has nothing to do with sorting.
Just use multiple levels in the Sort options.

As an addition, given this is about the 3rd time you've asked for help on this, wouldn't it be better for you to tell whoever is asking you to do this that you don't really know how?

Blakespops

EchoLocation wrote: »

Conditional Formatting is, unsuprisingly, for Formatting cells based on conditions. It has nothing to do with sorting.
Just use multiple levels in the Sort options.

As an addition, given this is about the 3rd time you've asked for help on this, wouldn't it be better for you to tell whoever is asking you to do this that you don't really know how?

Thanks for your reply but this is for collage and as I would lose marks if I can not work out how to do this I can not tell them. plus I am just trying to learn more about excel and I am not asking someone to do it for me as then I would have answer but no clue how to do something like this on next assignment. I am sure I could do it with macro but was told it is a formatting task

Blakespops

buglawton wrote: »

I suggest at any rate, install this free Excel add-in
http://www.asap-utilities.com/
It can do all manner of sorting, filtering and editing tricks with your open Excel sheet.

Thanks am downloading as I type. will spend rest of day playing with it to understand how to use it better.:beer:

EchoLocation

Blakespops wrote: »

Thanks for your reply but this is for collage and as I would lose marks if I can not work out how to do this I can not tell them. plus I am just trying to learn more about excel and I am not asking someone to do it for me as then I would have answer but no clue how to do something like this on next assignment. I am sure I could do it with macro but was told it is a formatting task

Isn't it your College's job to teach you what to do?

Formatting is about the displaying of data, I can't see how what you described has anything to with that.

When you say 'in any three columns', how does that work over seven columns? Do you mean A-C, B-D, C-E, D-F and E-G ?

Blakespops

EchoLocation wrote: »

When you say 'in any three columns', how does that work over seven columns? Do you mean A-C, B-D, C-E, D-F and E-G ?

If A, B, + C contain the possible combinations d has to have the values placed so they fit with:

A, B, D
A, C, D
B, C, D

Our tutor has told us that in 3 rows it will take 27 so if the values are placed just right you should be able to pick any three of the seven columns and find all 27 possibilities.

The last task we did involved over 6500 rows and this one is meant to be easier. The problem is I know how to do things like this with a pen and paper but not with formula on excel and I did complain that as a math class working things out with pen and paper should begood enough only to be told that times have changed and excel is used more and more in the workplace so I need to learn it as part of my math course. However as I do not know the first thing about how to get a formuls to work on excel I guessed I would be able to find someone to help me understand this side of excel.

EchoLocation

So, the 7 columns are simply an extended version of the 3 columns?
ie. All possible combinations of 1.0, 2.0 and 2.1 across 7 columns?

So 2,187 rows?

If you know mathmatically what you need to do, please post it and we can help you with a formula.

johnmc

I have had great success learning Excel by registering (free) with www.utteraccess.com

There are also a lot of worked examples on www.mrexcel.com

As has been said, formatting is about the display of the data and has nothing to do with sorting.

Blakespops

EchoLocation wrote: »

If you know mathmatically what you need to do, please post it and we can help you with a formula.

Here is formula for combinations: nCr = nPr / r!. However to get it to work out an interlocking way of covering all combinations are covered in any given 3 columns. starting in column 'A1' you simply go down placing 9 of each value in three groups of nine starting with the lowest to highest. Column 'B' you again start with the lowest value to highest placing nine groups of three. Finally in column 'C' you just start lowest to highest repeating all way down in singles (e.g a,b,c,a,b,c,a,b,c) to row 27.

Blakespops

EchoLocation wrote: »

So, the 7 columns are simply an extended version of the 3 columns?
ie. All possible combinations of 1.0, 2.0 and 2.1 across 7 columns?

So 2,187 rows?

No it is ALWAYS only all possibilities in 3 columns but arranged in such a way that you can pick any 3 of the seven and have all combinations show up. if it was all across 7 that would be 3*3*3*3*3*3*3=2,187 as you state however I just need 3*3*3=27 this is why we start with 27 rows because if we are to pick any three columns we are just after what is mathematically possibly for the three columns picked. As stated it is easy to work out the first three columns but after that it is not so easy as column d has to complete all combinations for a,b,d. A,c,d. And b,c,d then column E has to complete all combinations, see below for first three columns I wish Excel could identify pattern and continue it as needed:

A B C D E F G
X X X
X X Y
X X Z
X Y X
X Y Y
X Y Z
X Z X
X Z Y
X Z Z
Y X X
Y X Y
Y X Z
Y Y X
Y Y Y
Y Y Z
Y Z X
Y Z Y
Y Z Z
Z X X
Z X Y
Z X Z
Z Y X
Z Y Y
Z Y Z
Z Z X
Z Z Y
Z Z Z