Sorting data

[andycuk7]

Hi,

This maybe easy for some of you but to be honest i'm nearly putting my fist through my computer.

I have a simple sheet that carries approx 150 times, all mixed up with no order at all.

What I need to do with this is copy these times and with the copy create a schedule of order....range is C9:F27

HELP PLEASE

[ChemistB]

So you have like 150 times in, let's say A1:A150 and you want them copied into C9:F27? Do you want it going across and down or down and across?

[andycuk7]

sorry, the times are in c9:f27 and i need a vertical copied list that I can put in time order...

[ChemistB]

Try this formula entered as an array (use CONTRL SHFT ENTER instead of just ENTER. If done properly, you'll see {} around your formula)

=INDEX($C$9:$F$27,SUMPRODUCT(--($C$9:$F$27 = SMALL($C$9:$F$27,ROW(A1)))*(ROW($C$9:$C$27)-8)),SUMPRODUCT(--($C$9:$F$27 = SMALL($C$9:$F$27,ROW(A1)))*(COLUMN($C$9:$F$9)-2)))

See attachment (i didn't use times but result should be the same (format your cells as time)

Questions?

This maybe easy for some of you

Nope, not easy.

[andycuk7]

Sorry for delayed reply.

I keep getting REF erors when i try what you sheet has in it.

I've attached the sheet i am using if you can help pleeeease

Andy

[ChemistB]

Okay, my example assumed random scattered times and thus each time was unique. This is not the case with your data so my formula won't work. Where do you want the times (I'm assuming C10:F28?) moved to? Also, do you want them in order, C10, C11, C12, C13... or C10, D10, E10, F10, C11, D11....

[andycuk7]

The times are currently set to 03:00 06:00 ect ect, however when you enter an intake time the formula set will add 3hr/6hr/9hr/12hr to the intake time. I then need these to be put in a time order list in a random column to the right of the sheet??.

I'll enter some times so it looks a little better if you dont mind having a look foer me please.

Thanks a lot

Andy

[ChemistB]

Okay, this is much simplier (I think)
First, I modified your formulas to not calculate times if there is no input value.
For example in C10
=IF(ISNUMBER(A10),A10+$H$7,"")
This can be copied all the way down (the "$" anchor cell H7 so it doesn't change). Similarly with other columns (see attachment)

In J13 copied down
=IF(COUNT($C$10:$F$28)>=ROW(A1),SMALL($C$10:$F$25,ROW(A1)),"")
Does this work for you?

[andycuk7]

Wow, you are an absolute start !!!!

Thank you so much

[andycuk7]

Is it possiblefor the sheet to tell me what load the time has come from ??

Cheers