# Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have multiple columns on my spread sheet and want to switch from A-Z to Z-A based on values in column A
Solved by V. H. in 38 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 26/09/2018 - 03:06
Welcome, Thanks for choosing Got It Pro-Excel. I can help you with that problem.
Excelchat Expert 26/09/2018 - 03:06
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
Excelchat Expert 26/09/2018 - 03:06
Do you have sample data which we can use to illustrate how to solve this problem?
User 26/09/2018 - 03:08
ok
Excelchat Expert 26/09/2018 - 03:08
I am seeing you are putting data on the sheet to your right.
Excelchat Expert 26/09/2018 - 03:08
Let me know once you are done.
User 26/09/2018 - 03:10
I have given a simple sample I would ;like the data to be reformatted by latest date going down to earliest date with rows remaining attached to original values in column A
Excelchat Expert 26/09/2018 - 03:11
Okay.
User 26/09/2018 - 03:11
I understand there is a simple solution but I just can't seem to find it
Excelchat Expert 26/09/2018 - 03:12
Based on what you’ve shared, you need to order your data in descending order i.e. from the latest to earliest
User 26/09/2018 - 03:12
yes based on col A
Excelchat Expert 26/09/2018 - 03:12
Okay.
Excelchat Expert 26/09/2018 - 03:13
First of all
Excelchat Expert 26/09/2018 - 03:13
Is the data in column A of your original data the same as the one you have given?
User 26/09/2018 - 03:13
yes
Excelchat Expert 26/09/2018 - 03:14
I am seeing cell A1 has the value:
Excelchat Expert 26/09/2018 - 03:14
201890-11
User 26/09/2018 - 03:14
do I ned to restructure it?
Excelchat Expert 26/09/2018 - 03:14
Which date is that/
User 26/09/2018 - 03:14
year month - day of month
Excelchat Expert 26/09/2018 - 03:15
If we separate that, we can get:
Excelchat Expert 26/09/2018 - 03:15
2018 - 90 - 11
User 26/09/2018 - 03:15
yes and I can make that happen
Excelchat Expert 26/09/2018 - 03:16
But 90 does not represent any month in reality.
Excelchat Expert 26/09/2018 - 03:16
?
User 26/09/2018 - 03:16
Sept
Excelchat Expert 26/09/2018 - 03:16
So it should read from left to right?
User 26/09/2018 - 03:16
yes
User 26/09/2018 - 03:17
I am just used to those dates, but I can restructute the dates if that is what is wrong
Excelchat Expert 26/09/2018 - 03:17
Okay.
Excelchat Expert 26/09/2018 - 03:18
For you to get the correct results, you must restructure them.
User 26/09/2018 - 03:18
OK what fornat does Excel like?
User 26/09/2018 - 03:19
?
Excelchat Expert 26/09/2018 - 03:19
Yeah
Excelchat Expert 26/09/2018 - 03:20
You see your format is not recognized as date
Excelchat Expert 26/09/2018 - 03:20
It is recognized as text.
User 26/09/2018 - 03:20
Ok waht date structure do you suggest??
User 26/09/2018 - 03:20
I see
Excelchat Expert 26/09/2018 - 03:21
We can convert them to:
User 26/09/2018 - 03:21
Please tell me the recommended date structure for Excel
Excelchat Expert 26/09/2018 - 03:21
ddmmyyyy
Excelchat Expert 26/09/2018 - 03:21
To get there we will write a function.
User 26/09/2018 - 03:21
Ok so 11902018
Excelchat Expert 26/09/2018 - 03:21
No
User 26/09/2018 - 03:22
OK 11092018
Excelchat Expert 26/09/2018 - 03:22
11-09-2018
Excelchat Expert 26/09/2018 - 03:22
That is okay.
Excelchat Expert 26/09/2018 - 03:22
Once we format them like that then Excel will sort them correctly.
User 26/09/2018 - 03:22
Can it be a string 11092018 or can it have - 11-09-2018?
Excelchat Expert 26/09/2018 - 03:23
The way Excel sorts data is different depending on the data type.
Excelchat Expert 26/09/2018 - 03:23
We will convert them to date data type.
Excelchat Expert 26/09/2018 - 03:24
User 26/09/2018 - 03:24
I can do it if I know what Excel likes
Excelchat Expert 26/09/2018 - 03:25
The most important is that you convert them to date data type.
Excelchat Expert 26/09/2018 - 03:25
You will have to extract the year, date and month separately.
Excelchat Expert 26/09/2018 - 03:25
Then use the DATE excel function.
User 26/09/2018 - 03:26
can you give me quick example? does it require different columns??
Excelchat Expert 26/09/2018 - 03:27
Yeah, you will have to have an extra column
Excelchat Expert 26/09/2018 - 03:27
I am working on the example:
User 26/09/2018 - 03:27
OK what does the column look like?
User 26/09/2018 - 03:27
OK
Excelchat Expert 26/09/2018 - 03:28
It should look like what is in D1
User 26/09/2018 - 03:28
Ok I can do that
Excelchat Expert 26/09/2018 - 03:28
Creating a formula which can accomplish that.
Excelchat Expert 26/09/2018 - 03:28
You can do that?
User 26/09/2018 - 03:29
I don't know about a formula, but I can do it manually for about 200 rows
Excelchat Expert 26/09/2018 - 03:29
You don't have to do that manually
Excelchat Expert 26/09/2018 - 03:29
Let me process you a formula which can achieve that.
User 26/09/2018 - 03:29
OK Great
Excelchat Expert 26/09/2018 - 03:30
I am creating a formula that will solve this problem. Will get back to you shortly.
User 26/09/2018 - 03:30
OK
Excelchat Expert 26/09/2018 - 03:33
Excelchat Expert 26/09/2018 - 03:33
The formula which will return the date correctly is:
Excelchat Expert 26/09/2018 - 03:33
=DATE(LEFT(A1,4),MID(A1,6,1)&MID(A1,5,1),RIGHT(A1,2))
Excelchat Expert 26/09/2018 - 03:34
I have put it in cell D1
Excelchat Expert 26/09/2018 - 03:34
Can you see that?
User 26/09/2018 - 03:35
Yes I was coping the formula
User 26/09/2018 - 03:35
No
Excelchat Expert 26/09/2018 - 03:35
It returns an equivalent date format, right?
User 26/09/2018 - 03:36
Yes it did, but I was copying the foymula when the screen scrolled
Excelchat Expert 26/09/2018 - 03:37
Now what you will do, apply the formula to your data.
Excelchat Expert 26/09/2018 - 03:37
That is to the new column you will create.
Excelchat Expert 26/09/2018 - 03:37
Now when you will sort the data, it will sort it correctly from the oldest to the earliest.
User 26/09/2018 - 03:37
Right can you post the formula again?
Excelchat Expert 26/09/2018 - 03:38
The formula:
Excelchat Expert 26/09/2018 - 03:38
=DATE(LEFT(A1,4),MID(A1,6,1)&MID(A1,5,1),RIGHT(A1,2))
Excelchat Expert 26/09/2018 - 03:39
Can you see the online sheet to your right?
User 26/09/2018 - 03:39
Yes
Excelchat Expert 26/09/2018 - 03:40
I have put the formula in column D
User 26/09/2018 - 03:40
I se it on the top panel as Fx
Excelchat Expert 26/09/2018 - 03:41
Use this:
Excelchat Expert 26/09/2018 - 03:41
Now you will use column D instead of A to sort the data.
User 26/09/2018 - 03:42
Right...Thanks
Excelchat Expert 26/09/2018 - 03:42
You will sort from Z - A
User 26/09/2018 - 03:42
User 26/09/2018 - 03:42
Right
Excelchat Expert 26/09/2018 - 03:43
That is oldest to earliest.
User 26/09/2018 - 03:43
Yes
Excelchat Expert 26/09/2018 - 03:43
User 26/09/2018 - 03:43
Yes
Excelchat Expert 26/09/2018 - 03:43
Yaaaay!
Excelchat Expert 26/09/2018 - 03:43
It was my pleasure helping you solve this problem. Any time you have a problem with Excel do not hesitate to come back for help.
User 26/09/2018 - 03:43
Indeed
Excelchat Expert 26/09/2018 - 03:43
Please remember to rate us at the end of the chat for better and quality services. Thank you!
User 26/09/2018 - 03:43
OK
User 26/09/2018 - 03:44
And Thank You

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.