Re-order Table Columns using Sort Function

**ea223** · 05-16-2023, 11:25 AM

I need to reorder the columns of a table and I am using the sort function to do this. The formula I am using is:

=IF(ISBLANK(SORT(Table1[#All], 1, 1, TRUE)),"",SORT(Table1[#All], 1, 1, TRUE))

It works but because the numbers in the original data are the table headers, it sorts 10 before 2 etc., presumably because it recognizes this as text rather than numbers. I can't figure out how to fix this. I need to keep the original data in table format for various reasons but I specifically put the numbers in the headers because I don't want to have to amend the formula every time (right now the formula is looking at "all of table 1" instead of A1 to F60 for example so if I add rows to table 1, the sort formula automatically includes them - hope this makes sense.)

Can anyone help?

**AliGW** · 05-16-2023, 11:26 AM

Not sure, but did you try this?

=IF(ISBLANK(SORT(Table1[All], 1, 1, TRUE)),"",SORT(Table1[All], 1, 1, TRUE))

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

**ea223** · 05-16-2023, 11:36 AM

Hi there,

Thank you for your reply. No, that formula won't work. I have attached a sample workbook.

Thank you

**AliGW** · 05-16-2023, 12:21 PM

Try this:

=LET(h,--Table1[#Headers],b,VSTACK(h,Table1[#All]),DROP(SORTBY(b,INDEX(b,1),1),1))

or this:

=IF(Table1[#All]="","",LET(h,--Table1[#Headers],b,VSTACK(h,Table1[#All]),DROP(SORTBY(b,INDEX(b,1),1),1)))

**AliGW** · 05-16-2023, 12:43 PM

Any good to you?

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

**AliGW** · 05-16-2023, 01:01 PM

Seen, but no reply. Not even one word of thanks.

**ea223** · 05-16-2023, 01:17 PM

Hello - that is because I am still trying to figure it out before posting my reply. It does not work when I paste it into my file and I'm not sure why.

**ea223** · 05-16-2023, 01:39 PM

I don't know how to make it work at all. When I double click on your formula in the sample workbook, it errors out.

I also need to exclude columns that have an X in the header...I forgot about that part. Any ideas?

**AliGW** · 05-16-2023, 02:00 PM

Perhaps if you provide a more realistic set of data? I’ll have another look tomorrow - it’s evening here.

If you need an explanation, then just ask. Clearly it works on the sample you gave us.

**ea223** · 05-16-2023, 02:14 PM

I know it works on the sample but it doesn't work when I copy and paste it into my workbook and when I double click and hit enter on the formula you wrote, it then errors out (despite it working when I open the file). I am not sure why that would be? Perhaps you know though!

Are you upset that it took me just under an hour to reply to you? I am so very sorry - I am working on several things at once! Once I saw I couldn't get it to work, I tried a few things, then moved onto something else for a bit before attempting to try again and responding when I still couldn't get it to work. Thank you for helping me with this.

I've attached the workbook again - I changed somethings in the raw data of the first table (not the headers) and your formulas errored out (I don't know why - I'm not suggesting they are incorrect, as they did work when I first opened the workbook). I also created a table underneath and the desired output beside it (reordered columns, columns with X in the headers removed).

**AliGW** · 05-16-2023, 02:31 PM

What error message do you get?

**sandy666** · 05-16-2023, 02:36 PM

with Power Query

1	3	10	2	4	5	7	6	9	8	1	2	3	4	5	6	7	8	9	10
chocolate	fridge	10	sweet	confectionary		treat	yes	1	special	chocolate	sweet	fridge	confectionary		yes	treat	special	1	10
banana	fruit bowl	5	sweet	fruit		not treat	yes	5	regular	banana	sweet	fruit bowl	fruit		yes	not treat	regular	5	5
strawberry	fridge	6	sweet	fruit		not treat	no	2	regular	strawberry	sweet	fridge	fruit		no	not treat	regular	2	6

Please Login or Register  to view this content.

**ea223** · 05-16-2023, 03:01 PM

If you open the revised workbook I attached - does it show a #NAME? error? That is what I am getting!

**ea223** · 05-16-2023, 03:09 PM

Interesting! I have not used power query before but will play around and see if I can get it to work on my original data set...

**sandy666** · 05-16-2023, 03:12 PM

sure, for more info see Learn Power Query

**AliGW** · 05-16-2023, 03:32 PM

Originally Posted by ea223

If you open the revised workbook I attached - does it show a #NAME? error? That is what I am getting!

This means that your version of 365 is not as recent as mine. Try running updates.

**johnnyL** · 05-16-2023, 03:53 PM

The following is a formula for the OP that only uses 2 functions

Formula:

Please Login or Register  to view this content.

**ea223** · 05-16-2023, 04:13 PM

Thank you! This almost worked really well but the only issue is it converts my numbers stored as text to actual numbers...which normally would be useful but in this case I actually need them as per the original. Do you know how to maintain the original formatting? Some of the numbers have 0s in front as well - I also need to maintain these!

**ea223** · 05-16-2023, 04:15 PM

This one didn't work for me! I got a NAME error again. But also based on the output I don't think it reordered the columns....not entirely sure though of course since it's all errors.

**ea223** · 05-16-2023, 04:16 PM

Unfortunately I am not an administrator - I'll see if they can do it for me soon!

**johnnyL** · 05-16-2023, 04:18 PM

@ea223, you probably should indicate which post #s you are referring to when you comment on whether something worked or didn't work.

**AliGW** · 05-16-2023, 05:16 PM

@Johnny - your forum profile needs updating, please.

**johnnyL** · 05-16-2023, 05:19 PM

@AliGW, anything in particular need updating?

**AliGW** · 05-16-2023, 05:41 PM

Yes. Your Excel version!

**johnnyL** · 05-16-2023, 05:50 PM

@AliGW,

My computer that I mainly use has Excel 2007 & I use it over 90% of the time, so that is what I would like any responses I receive, should I post a question about something, to be based on. The spouses computer, which I occasionally use has Excel 365.

With that in mind, should I still update my profile? If Yes, should it be 2007 & 365, just 365 since that it the newest version that I occasionally use, or something else?

**TMS** · 05-16-2023, 06:45 PM

If you have the functions, try:

Formula:

Please Login or Register  to view this content.

**TMS** · 05-16-2023, 06:55 PM

@johnnyL: I would suggest that you refer to both versions in your profile so everyone is aware of what you have access to. However, when you ask a question, state (in big bold letters) the version of Excel you need the solution to work for … or should I say "the version of Excel for which you need the solution to work"?

**johnnyL** · 05-16-2023, 07:01 PM

Too Easy, Done.

**TMS** · 05-16-2023, 07:12 PM

**AliGW** · 05-17-2023, 12:21 AM

See if this works for you:

=IF(Table1[#All]="","",LET(h,--Table1[#Headers],b,Table1[#All],SORTBY(b,h,1)))

Look at AliGW 2 in the attached.

SORTBY came out at the same time as SORT, so you should have it in your version.

**tanasedn** · 05-17-2023, 08:25 AM

Hi, ea223
Maybe this formula is working in your version. I hope it does because in the original post your formula is based on SORT function

Formula:

Please Login or Register  to view this content.

If the new Header need to be text, just change the cell formatting from number to text

**ea223** · 05-17-2023, 09:56 AM

Thank you everyone for the responses - I will be back later to properly review.

**AliGW** · 05-17-2023, 10:30 AM

Glad to have helped.

If that takes care of your original question once you have reviewed the suggestions, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

**Glenn Kennedy** · 05-17-2023, 11:43 AM

Looking over this, I'd guess you don't have the most up-to-date version of O365. The up-to-dateness (ugh) varies depending on the exact product that you have.

This WILL work in any O365 version AND keeps the headers as TEXT.

Formula:

Please Login or Register  to view this content.

**ea223** · 05-18-2023, 12:17 PM

@Glenn - thank you for this. This worked! Is there any way to return blank values as blanks instead of zeros, and not bring back any columns with an X in the heading?

**Glenn Kennedy** · 05-18-2023, 12:39 PM

Format/Format cells/Custom

0;-0;;@

OK will hide zeros. It's a bit more problematic to do it by a formula as some of your columns are text and others are numbers.

Columns containing X in the header... just omit them completely? I'm away for the night now, but can return to this tomorrow morning. Please post a sample file showing EXACTLY what sort of "headers with an X" that there might be. There may be some problems... but we'll see...

**TMS** · 05-18-2023, 01:30 PM

To retain blanks:

Formula:

Please Login or Register  to view this content.

**TMS** · 05-18-2023, 02:44 PM

@Glenn: the sample file in post #10 had a second table with Xs in the headings. It is Table13. Your existing solution works but has two columns on the end that have #N/A. Just need a tweak to dump (or hide) them.

This will retain blanks and hide the two columns of #N/A results.

Formula:

Please Login or Register  to view this content.

Must be an aesthetically more pleasing solution that dumps them.

**ea223** · 05-18-2023, 03:20 PM

@TMS - I think this finally worked!! Thank you so much!

**TMS** · 05-18-2023, 04:01 PM

You're welcome. Thanks for the rep.

Assuming you have FILTER (pretty sure all versions of 365 have it) we can combine part of my original formula with Glenn's and remove the X columns and retain the blanks.

Formula:

Please Login or Register  to view this content.

**johnnyL** · 05-18-2023, 09:41 PM

Excel 365 should definitely have the FILTER function.

**Glenn Kennedy** · 05-19-2023, 02:46 AM

I did it this way:

Formula:

Please Login or Register  to view this content.

Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Re: Re-order Table Columns using Sort Function

Thread Information

Users Browsing this Thread

Similar Threads

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions