Sort by Column

[emvella]

Hi,


I copy from a website and paste in excel like this:
https://yadi.sk/i/B8kbXytFc6b19w


I don't know if it is possible or not.
What I want is that I Sort the list by column C (time) but because each game is in 2 rows then I cannot do it because I am having a mess like this:
https://yadi.sk/i/-vbH9AzC7oMFLg


I want to Sort them like this:
https://yadi.sk/i/5hodV2BqrslKbQ


Thanks

 

[Special-K99]

Use a Helper column.

in a blank column say column I

in I1
=A1+0.1

in I2
=IF(A2="",C1+0.1,A2+0.1)
and copy this formula only down the column and include one more row which will be blank (so that all data consist of 3 rows - even a blank row)

Now sort on Column I.

 

[emvella]

Hi Special-K99 and thanks for your reply.

In I1, I copied this =A1+0.1
In I2 I copied this =IF(A2="",C1+0.1,A2+0.1)
https://yadi.sk/i/KMev0o_kP_Y2CQ

In I3 I copy I1 or I2?

 

[Special-K99]

in I3 and subsequent rows copy I2.
Copy as far as there is data in column B and one more row.
This ensures that all the data consist of three rows - two with data and one blank row, otherwise when you sort it doesnt produce a blank row on one row of data.

 

[emvella]

I am having like this:
https://yadi.sk/i/4KNfIzTFcAelgA
Maybe I am doing something wrong

I want to Sort them like this:
https://yadi.sk/i/5hodV2BqrslKbQ

 

[Special-K99]

Sorry, my fault, the formula needs adjusting, i was testing in column A.

in a blank column say column I

Start your data in row 2

in I2
=IF(C2="",I1+0.1,C2+0.1)
and copy this formula only down the column and include one more row which will be blank (so that all data consist of 3 rows - even a blank row)

Now sort on Column I.

 

[emvella]

Hi Special-K99, it is not working.

Can I send you an excel sheet?

 

[Special-K99]

Sure, I dont know if you can send an attachment using PM.
Might be easier to upload to an online storage site and then provide the link to it.

 

[emvella]

https://drive.google.com/open?id=1cbWo2_l4jBZ4gMgxwNzaaTDVHa7mLtJS

 

[Special-K99]

Aarrrgh! You've changed the data again.
In Post #5 the times are in column C, now they're in column B!! Grrr!!

Ok.

Insert a row so the data starts in row 2.

in H2
=IF(B2="",H1+0.1,TEXT(B2,"hhmm")*100+0.1)
copy this formula down as far as row 16 (the Blank row - this ensures each data entry has THREE lines (5*3=15) + the blank row at the top = 16)

Now select columns B:H and sort on column H

The times are now in order and each accompany row has been sorted with its original "parent" time.