Help with Excel required

M

m22au

I have a table of historical data in columns, that is in reverse chronological order .... ie. may 2003 data starting in row 2, with feb 2003 data in row 100.

Apart from going through line by line and rearranging myself, eg. in cell j2 having =c100

is there any quick way to invert the table of data so that the entries are in chronological order?
 
Quote from m22au:

I have a table of historical data in columns, that is in reverse chronological order .... ie. may 2003 data starting in row 2, with feb 2003 data in row 100.

Apart from going through line by line and rearranging myself, eg. in cell j2 having =c100

is there any quick way to invert the table of data so that the entries are in chronological order?
More...

Try Data - Sort :D
 
Quote from vladiator:



Try Data - Sort :D
More...

Thanks vladiator I did forget that function.

However in one of my data sets the date column is formatted (not my choice) as text, and the format of the date is not recognised by Excel as a date format (even as a custom date format).

So although Data - Sort will sort {30/11, 29/11, 28/11} in the opposite order, it will sort {1/12, 30/11, 29/11} as {1/12, 29/11, 30/11}.

Any other suggestions people?
 
Try reformatting the data in place using right-click, format cells and try some date options. Remember save first and undo if necessary.

There is also a DATE function that may allow you to convert into a new cell.

If that doesn't work, make a new column and parse the text out in pieces to make a new date. For example:

Cell A1 (bad date) = 30/01/02 as text (DD/MM/YY).
Cell B1 formula = MID(a1,4,2)&'/'&LEFT(a1,2)&'/'&RIGHT(a1,2)
 
I was able to format the "bad date" as a custom date format after all. Thanks vladiator and ktm.

Thanks again to vladiator for reminding me about the Data - Sort thing.


Quote from ktm:

Try reformatting the data in place using right-click, format cells and try some date options. Remember save first and undo if necessary.

There is also a DATE function that may allow you to convert into a new cell.

If that doesn't work, make a new column and parse the text out in pieces to make a new date. For example:

Cell A1 (bad date) = 30/01/02 as text (DD/MM/YY).
Cell B1 formula = MID(a1,4,2)&'/'&LEFT(a1,2)&'/'&RIGHT(a1,2)
More...
 
A different question for Excel experts. I would like to include a time function in a macro such that the macro kicks off at a certain time. For example, if time = xyz, than . . . Is there anything like this in Excel?
 
Quote from Hurricane:

A different question for Excel experts. I would like to include a time function in a macro such that the macro kicks off at a certain time. For example, if time = xyz, than . . . Is there anything like this in Excel?
More...

Look in the help files for OnTime

Cash
 
Finally I did it ... after 10 separate formulae ... using functions LEFT, RIGHT, SUBSTITUTE, TRIM, VLOOKUP, CONCATENATE, VALUE

thanks ktm for introducing me to all the text functions


Quote from ktm:

Try reformatting the data in place using right-click, format cells and try some date options. Remember save first and undo if necessary.

There is also a DATE function that may allow you to convert into a new cell.

If that doesn't work, make a new column and parse the text out in pieces to make a new date. For example:

Cell A1 (bad date) = 30/01/02 as text (DD/MM/YY).
Cell B1 formula = MID(a1,4,2)&'/'&LEFT(a1,2)&'/'&RIGHT(a1,2)
More...
 
You must log in or register to reply here.
Back
Top