understanding sorting values in excel

Blakespops

An easy way to explain it is to state the question I have on paper " ...after working out combinations of date,month,year giving you 27 rows extend combinations to 7 column's so that any 3 columns from the 7 cover all combinations"

Dave_C_2

Blakespops wrote: »

...after working out combinations of date,month,year...

Did you mean day, month, year? But there again 1.0, 2.0 and 2.1 don't fit this pattern.

I could be hopelessly wrong here, but:

Apparently this involves dates and I still haven't a clue what the point of this exercise is. All I can see is mixing things up for the sake of mixing things up - what is the end result?

If it is day month and year then some combinations will be wrong, like 29 Feb is only possible in a leap year and months have varying numbers of days. It gets very messy very quickly.

If the dates were input into a single cell using Excel's date format, then this whole problem goes away.

Dave

Blakespops

Sorry I have just re-read what I need and it is a rolling combination ao column's A,B,C--B,C,D--C,D,E etc cover all combinations also is it possible to have this so you could extend it to cover larger options in future for example 3x3x3x3 giving 81 combinations so I can extend them in the future as above?

Day (this relates to how many days per week are worked based on a clocking in card) Month (same as days only months worked per year) Year (as others years worked in a life time)are the true three values I just used a previous question I had asked on here reworded to cover what I needed. Column a is the day of the month, column b is month and c the year and I need to have the 27 combinations of them and extend it as I mention in this reply. as for not seeing why we need this I can fully understand why but if I asked the whole task including all parts of it you would know why although you would I am sure be wondering what you have done wrong to be given this to doin the first place lol.

what I am saying is you are seeing this out of context to the rest of the problem.

Blakespops

Dave_C wrote: »

Did you mean day, month, year? But there again 1.0, 2.0 and 2.1 don't fit this pattern.

I could be hopelessly wrong here,
Dave

Yes sorry Dave as I stated the question was being seen out of context. I have updated the question so you can understand a little better what the three values are and having the combinations interlink although after a phone call this morning I understand what I need and can do it by hand but do not understand excel for how to have it work it out. I can just put column b in numericle order and see at a glance what I need to add to have all combinations for B,C,D then place c in order to do next one etc but if I am to learn anything about excel so I do not need to ask and maybe I can answer questions myself I need to learn how to have excel do it.

paddyrg

I don't really understand the question. I used to model data systems for extremely complex requirements, and pride myself on being pretty analytical, but I am baffled. It sounds like the 'what' and the 'how' are pulling in opposite directions here. The key to data systems is thinking about purity, simplicity and usually removing redundancy. At the moment I see a pile of spaghetti and we're focussing in on the shapes of the individual noodles, not the meal.

I would strongly suggest going back to the drawing board - simple data can become complex enough quickly enough, but this seems to be unclear in its aim, possibly using the wrong tool, and a bit confused all round. Starting from scratch with the question and textbooks may save you more time overall!

Blakespops

1.0 2.1 1.0 2.0 1.0 2.0 2.1
1.0 2.1 2.0 2.0 1.0 2.1 2.1
1.0 2.1 2.1 2.0 1.0 1.0 2.1
1.0 1.0 1.0 2.0 2.0 2.0 2.1
1.0 1.0 2.0 2.0 2.0 2.1 2.1
1.0 1.0 2.1 2.0 2.0 1.0 2.1
1.0 2.0 1.0 2.0 2.1 2.0 2.1
1.0 2.0 2.0 2.0 2.1 2.1 2.1
1.0 2.0 2.1 2.0 2.1 1.0 2.1
2.0 2.1 1.0 2.1 1.0 2.0 1.0
2.0 2.1 2.0 2.1 1.0 2.1 1.0
2.0 2.1 2.1 2.1 1.0 1.0 1.0
2.0 1.0 1.0 2.1 2.0 2.0 1.0
2.0 1.0 2.0 2.1 2.0 2.1 1.0
2.0 1.0 2.1 2.1 2.0 1.0 1.0
2.0 2.0 1.0 2.1 2.1 2.0 1.0
2.0 2.0 2.0 2.1 2.1 2.1 1.0
2.0 2.0 2.1 2.1 2.1 1.0 1.0
2.1 2.1 1.0 1.0 1.0 2.0 2.0
2.1 2.1 2.0 1.0 1.0 2.1 2.0
2.1 2.1 2.1 1.0 1.0 1.0 2.0
2.1 1.0 1.0 1.0 2.0 2.0 2.0
2.1 1.0 2.0 1.0 2.0 2.1 2.0
2.1 1.0 2.1 1.0 2.0 1.0 2.0
2.1 2.0 1.0 1.0 2.1 2.0 2.0
2.1 2.0 2.0 1.0 2.1 2.1 2.0
2.1 2.0 2.1 1.0 2.1 1.0 2.0

Above is the seven columns and you will see all combinations covered in the 5 sets if you split them into columns:
A,B,C
B,C,D
C,D,E
D,E,F
E,F,G

the point as I state is knowing how to get excel to do this working out for me.

EchoLocation

Right, I think I understand it now. All 27 combinations in any 3 consecutive rows - so any 3 columns should add up to 137.7

If that's correct, then it should be easy to use Excel to extend the number of columns, to replicate your example, by using a lookup table based on the first 3 columns...

Firstly, sort your first 3 columns into numerical order from A-C, so the first row is 1.0, 1.0, 1.0 and the last is 2.1, 2.1, 2.1

Then, below your data create a 2 column lookup list. Join the values (A1&B1&C1 etc) of each of the first 3 columns together - creating a list of 27 unique values in the first column. Then in the cells next to each of these unique values simply add the next value to populate the list - which will be the order you have shown in Col D in your example (ie. 1-9= 2.0, 10-18=2.1, 19-27=1.0), although I think you could add them in any order and still achieve the same result (?)

Assuming your data is A1:C27 (ie. no headers), enter the following formula into the cell D1...
=VLOOKUP(A1&B1&C1,$A$29:$B$55,2,FALSE)

Where $A$29:$B$55 = The 2 column lookup list of unique values.

You can then drag this formula down against the remaining 26 rows, then drag all 27 formulas in Col D across as many columns as you need.

Not entirely sure I've completely grasped it, so the above may be of no use whatsoever.