Rearrange Data From Last Row to First Row

[kumara_faith]

Hi ,

I have the data as follows:

Book2
	A	B	C	D	E	F	G
1	Date	Open	High	Low	Close*	Adj Close**	Volume
2	Sep 17, 2021	4,469.74	4,471.52	4,427.76	4,432.99	4,432.99	5,622,210,000
3	Sep 16, 2021	4,477.09	4,485.87	4,443.80	4,473.75	4,473.75	3,321,030,000
4	Sep 15, 2021	4,447.49	4,486.87	4,438.37	4,480.70	4,480.70	3,154,760,000
5	Sep 14, 2021	4,479.33	4,485.68	4,435.46	4,443.05	4,443.05	2,568,730,000
6	Sep 13, 2021	4,474.81	4,492.99	4,445.70	4,468.73	4,468.73	3,096,390,000
7	Sep 10, 2021	4,506.92	4,520.47	4,457.66	4,458.58	4,458.58	2,851,140,000
8	Sep 09, 2021	4,513.02	4,529.90	4,492.07	4,493.28	4,493.28	3,035,300,000
9	Sep 08, 2021	4,518.09	4,521.79	4,493.95	4,514.07	4,514.07	2,808,480,000
10	Sep 07, 2021	4,535.38	4,535.38	4,513.00	4,520.03	4,520.03	3,098,870,000
11	Sep 03, 2021	4,532.42	4,541.45	4,521.30	4,535.43	4,535.43	2,609,660,000
12	Sep 02, 2021	4,534.48	4,545.85	4,524.66	4,536.95	4,536.95	2,897,010,000
Sheet3

I need the data to be arranged from the earliest date to the current date. The data is from Year 2010 to Year 2021. The format of the data is Date. But when I try to use sort to sort this from year 2010 to year 2021, it shows as below which is inaccurate

Book2
	A	B	C	D	E	F	G
1	Date	Open	High	Low	Close*	Adj Close**	Volume
2	Apr 01, 2010	1,171.23	1,181.43	1,170.69	1,178.10	1,178.10	4,006,870,000
3	Apr 01, 2011	1,329.48	1,337.85	1,328.89	1,332.41	1,332.41	4,223,740,000
4	Apr 01, 2013	1,569.18	1,570.57	1,558.47	1,562.17	1,562.17	2,753,110,000
5	Apr 01, 2014	1,873.96	1,885.84	1,873.96	1,885.52	1,885.52	3,336,190,000
6	Apr 01, 2015	2,067.63	2,067.63	2,048.38	2,059.69	2,059.69	3,543,270,000
7	Apr 01, 2016	2,056.62	2,075.07	2,043.98	2,072.78	2,072.78	3,749,990,000
8	Apr 01, 2019	2,848.63	2,869.40	2,848.63	2,867.19	2,867.19	3,500,760,000
9	Apr 01, 2020	2,498.08	2,522.75	2,447.49	2,470.50	2,470.50	5,947,900,000
10	Apr 01, 2021	3,992.78	4,020.63	3,992.78	4,019.87	4,019.87	4,151,240,000
11	Apr 02, 2012	1,408.47	1,422.38	1,404.46	1,419.04	1,419.04	3,572,010,000
Sheet3

Is there anyway I can use a formula to populate the data from the last row i.e from year 2010 to year year 2021? Basically to sort the data from year 2010 to year 2021. Appreciate all the help.

 

[edmitchell]

Hi -

I suspect based on the result of the sort that Excel is treating the contents of column A, as text rather than as a 'true' date. Might be worth converting these '=datevalue(a2)' (which converts a date held as text into a 'true' date (e.g. a number which then can be displayed as a date)), if that were to work then you could either sort by this 'new' column or copy/paste special and put the value of the formula back into A2, etc

If that still doesn't do what you need how about posting a sample via the utility 'xl2bb' so I can actually see how Excel is holding the content of A2, etc?

 

[kumara_faith]

Hi ,

I tried that and it returned as #value.

Here is a link to the file. Hope you can access the file.

Sample.xlsx

Sheet3 Date,Open,High,Low,Close*,Adj Close**,Volume Sep 17, 2021,4,469.74,4,471.52,4,427.76,4,432.99,4,432.99,5,622,210,000 Sep 16, 2021,4,477.09,4,485.87,4,443.80,4,473.75,4,473.75,3,321,030,000 Sep 15, 2021,4,447.49,4,486.87,4,438.37,4,480.70,4,480.70,3,154,760,000 Sep 14, 2021,4,479.33,4,485....

docs.google.com

 

[edmitchell]

Hi

having downloaded the file, the content in A is 'just' text, so we need to convert it to a date so excel will then sort it correctly, so if you insert a new column in front of the current B, and then enter the formula of

=DATEVALUE(MID(A2,5,2) & " " & LEFT(A2,3) & " " & RIGHT(A2,4))

--- into B2 (and the rest of B)
you can then either sort this column or copy and paste the values back into A (and then obviously delete the new B column)

 

[kumara_faith]

Hi ,

When I try to enter the formula, it is showing the following error:

 

[kumara_faith]

Hi,

My apologies. Pasted the formula wrongly. That worked. Thank you for your valuable time and patience. Have a great day. ?