CODEWITHSUNDEEP
excelvba
rhitz
rhitz

Reputation: 1892

Change the order of table column in Excel

Well, its probably not bit easy anymore still here it comes.

I want to order the column with table content in Excel in specific order.

i.e I want all the columns with Prefix ('Product'/'product') to come at end in alphabetical order except 'Product_ID' to be first among them and everything else remains the same.

i.e Demo

Cust_ID | Name | Product_Quantity | product_Name| Product_ID | Price_per_quantity

1 | Rohit | 4 | Pen | A23 | $2

2 | Tim | 3 | Pot | P41 | $3

to

Cust_ID | Name | Price_per_quanity | Product_id | product_Name | Product_Quantity

1 | Rohit | $2 | A23...(respective columns data)

I wish to have a generalized way for n columns (with/without) using VBA script which contains more columns.

Also (2nd question) To reverse the columns irrespective of there value which is solved by fellow SO members.

Upvotes: 1

Views: 689

Answers (4)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

This answer is an addendum to the response given by @pnuts, which, if possible to use, is far simpler. If you cannot sort by column for some reason, one way to achieve your result is to take the transpose of the input (i.e. swap rows and columns), sort alphabetically by row, then transpose back to get the original format. Here is a step by step guide for how to do this:

enter image description here


Highlight a 4x3 region of the spreadsheet where you want the transposed data to go. Then enter a transpose array formula, which is =TRANSPOSE($A$1:$D$3) in the example above. Note carefully that the cell ranges are absolute ($) so that it doesn't change as Excels copies it over the range. Also remember to press CTRL + ALT + ENTER, rather than just ENTER, to tell Excel that you are entering an array formula. Next copy this data to a new location (values only), and sort in descending order by the letter column:

enter image description here


The final step is to take the transpose of this sorted data to obtain your final result:

enter image description here


Again, this involved using the TRANSPOSE function with an array formula.

Now you have your original data sorted by column.

Upvotes: 1

MGP
MGP

Reputation: 2551

Select the Cells you want to sort. Right Click on them, choose Sort -> Custom Sort

Click on Options and choose Left to Right. And set the settings as in the picture:

enter image description here

And press Ok.

Upvotes: 1

nehem
nehem

Reputation: 13642

Follow these steps

  1. Insert a top row for dummy purpose with values 1, 2, 3, 4
  2. Copy all data including this dummy row -> paste special -> transpose in some other location
  3. Sort the transposed data using "Largest to smallest" with "Expand" option
  4. Now again copy the data, this time without the first dummy column -> paste special -> transpose.

Phew...

Picture attached. enter image description here

Upvotes: 1

pnuts
pnuts

Reputation: 59475

Assuming a is in A1, insert a row at the top and populate with: 

=COLUMN()

copied across above all populated columns. Select all populated columns and sort by Row1 Largest to Smallest.

Upvotes: 3

Related Questions