Excel rearrange column using copy + paste

1

I am trying to create a method to automatically rearrange the column when performing copy + paste.

Say I have an excel file with table containing columns on the first row:

ID | NAME | DATE      | TIME     | FAVORITE_FOOD
12 | John | 2/12/2015 | 12:00 PM | Chipotle
13 | Jake | 2/13/2015 | 12:01 PM | BigMac

and so on (imagine the above blockquote as a typical table in excel)

I am trying to figure out a way so when I copy the entire table, when I paste it on the second excel file it will automatically rearrange the column order into: date, time, ID, name, favorite_food.

Is this possible to create conditionals on the second excel file to automatically rearrange the column order? If not, what are some ways I can automate this process?

Iggy

Posted 2016-03-11T01:31:21.417

Reputation: 111

Answers

1

You can accomplish this by combining INDEX() and MATCH(). Index returns the contents of a cell within some range that you define, and you address it by feeding in a row number and a column number. For example:

=INDEX(A1:D5,3,2)

That will return the value of cell B3, which is the intersection of the third row and the second column within the selection.

Match searches within a selection for the cell that contains some value that you want to search for and returns the column number. If your sample data begins in A1, then

=MATCH("TIME",A1:E1,0)

Will return the number 4.

So, putting it together. If your data starts in A1, and you want to copy it to a new location starting at G1, copy this formula to G2. Then, drag it to the right and down. If you ever want to reorder the destination, all you have to do is change the column titles.

=INDEX($A:$E,ROW(),MATCH(G$1,$A$1:E$1,0))

enter image description here

picobit

Posted 2016-03-11T01:31:21.417

Reputation: 211

Thanks for the answer! It does the job for the table content; however, I still have to rearrange the table title (Date, Time, ID, Name, Favorite_Food) myself. Is there a way to scan through the table title and look automatically places the Date and Time on 1st and 2nd column title, and automatically paste the remaining table title? – Iggy – 2016-03-15T18:51:10.173

Is this something that you're trying to automate across hundreds of files? If not, then that's a step that's best done by hand. This formula will do it, but it's needlessly complex and you still end up typing the columns names out in the order that you want (it's just done inside a formula instead.) =CHOOSE(COLUMN()-COLUMN($G$2)+1,"DATE","TIME","ID","NAME","FAVORITE_FOOD") – picobit – 2016-03-16T01:29:01.400

Asked: 2016-03-11T01:31:21.417

Viewed: 562 times

Active: 2016-03-22T09:05:49.467