Excel query

Excel query

Didn't find your answer?

Say I had a dump of data in a format
A,B,C,D, E in columns (ie 5 columns of data). And I wanted to re-Oder the columns to say

A,C,E,B,E ...is there a formula I could use?

Btw in real practise the schedule is huge so copying and pasting isn't possible (well it is but that's not what I want as its very timely!)

Many thanks for any help given

Replies (15)

Please login or register to join the discussion.

avatar
By duncanedwards
02nd Sep 2014 20:10

I obviously don't understand ...

the question because it appears straightforward.

Can you give more information about the various columns and the type of data they contain?

Assuming it's much more complicated than I have read it, and certainly if it's a regular task, it's possible that someone could write you a VBA(?) routine.  Not me sadly; it's a dark art as far as I am concerned.

Thanks (0)
avatar
By RFL H
03rd Sep 2014 08:15

The easy way surely

Is to highlight the whole column, Cut and Paste to the order you want.

Am I missing something?

Thanks (0)
avatar
By chatman
03rd Sep 2014 08:20

Too big for the clipboard

Apparently it is too big for the clip board, so cut and paste is not possible.

Thanks (0)
Replying to Tax Dragon:
avatar
By duncanedwards
03rd Sep 2014 09:15

More information required

chatman wrote:

Apparently it is too big for the clip board, so cut and paste is not possible.

 

That's not exactly what the OP said "copying and pasting isn't possible (well it is but that's not what I want as its very timely!)"  But I have no idea what the issue is.

Thanks (0)
Replying to davidwinch:
avatar
By chatman
03rd Sep 2014 09:22

@duncanedwards

duncanedwards wrote:
That's not exactly what the OP said "copying and pasting isn't possible (well it is but that's not what I want as its very timely!)"  But I have no idea what the issue is.

Yes you are quite right. I think that, as it did not appear to make sense, I made up something that did!

Thanks (0)
avatar
By neileg
03rd Sep 2014 09:33

Access

For large amounts of data Access is almost always a better tool than Excel.

If you imported the data into Access you would then run a query that put the columns in the order you want.

Thanks (1)
avatar
By paulwakefield1
03rd Sep 2014 09:57

If your problem

is that there are many rows, the simplest solution is to format the data as a table and then just drag the columns to where you need them.

Alternatively you could have formulae set up on the right hand side of your table which simply pull the data across into the column order you want. Using your example Column F would be: =[@Column1], Column G would be =[@Column3], etc. But this would be very memory hungry.

You could adopt a similar approach without Formatting as a table. This will not only suffer from the memory issues but also not cope well with changing volumes of data; however it has the advantage it could be on a spearate sheet.

A pivot table solution could be possible but it would rely on having a unique data field in each row to stop aggregation of data.

Another approach is to use Powerpivot (or possibly Power Query but I haven't explored that enough to know). This would depend on the version of Excel you are running. This may be the only viable solution in Excel if you have many columns and do not have sufficient memory and do not want to use vba.

Thanks (0)
By alan.rolfe
03rd Sep 2014 10:24

Just use the macro recorder

If a cut and paste is actually possible, you can use the macro recorder to watch what you do with a cut and paste.

The recorded macro can then be called up whenever you need to do the re-order.

If you are happy using VBA you can make it a bit more fancy, but even a basic macro can save a lot of time if you are regularly doing the same repeated actions.

 

Thanks (0)
Simon Hurst
By Simon Hurst
03rd Sep 2014 10:25

Data, Sort and custom list

If it's purely a sorting issue (which it probably isn't if you meant to include E twice in the example)  then you could try setting up a Custom List of the column headings in the right order and use Data, Sort, Options, Left to Right, then Order by Custom List.

If you have access to it, then Paul's suggestion of Power Query would provide an interesting and much more automatic option including the ability to repeat the same column wherever and as many times as you require it.

Also, on the Cut and Paste suggestions, Shift+Drag is probably easier for moving columns.

Thanks (1)
avatar
By paulwakefield1
03rd Sep 2014 10:44

Two much simpler suggestions

Thank heavens for Simon. I always end up reading his posts and banging my head on the table "why didn't I think of that?".

Thanks (1)
By Democratus
03rd Sep 2014 10:50

Data Sort options left to right

Insert a row above the data with a number for where you want the column to be sorted.

A B C D E

 1 4 2 5 4

 

And just do Data Sort, select options and change the sort to left to right.

Edit - Simon got there before me - that's what happens when you get interrupted during a post. 

Thanks (0)
avatar
By paulwakefield1
03rd Sep 2014 10:58

And another...

I think I'm going to delete my original post. Personally I blame the rather good evening last night. :-)

Thanks (2)
By Democratus
03rd Sep 2014 11:03

Nah leave it... we all need to see we are not alone...

in having these types of thing. Goodness knows I have.

Thanks (0)
Simon Hurst
By Simon Hurst
03rd Sep 2014 11:56

Power Query option

Hi Paul - not at all - the Power Query suggestion was an excellent one that made me think "why didn't I think of that"!

Thanks (0)