**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.*