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
Don't worry, I can help you do that.
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
Thank you for your patience.
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:
[Uploaded an Excel file]
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
I sincerely appreciate your help
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
That can help you to solve your problem, right?
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.
