Sort row data into columns with same heading in excel 2010

Put simply, I need to sort row data for a specific range into the correct columns based on that columns heading. For example, if there are five columns labelled A through E, and data in the rows below ranging from A through E; I need all of the A's to be in the A column, all of the B's in the B column etc. Example start data:

How it should look after the sort:

It also must be able to work with the possibility of having empty cells. For example; if the first example data had no B in row 3, the data must not shift over to the left so that C is in the B column etc.

Other info: not feasible to do by hand - over 450 rows.

Answers


It also must be able to work with the possibility of having empty cells.

Taking the above into consideration.

NON VBA WAY

Insert enough columns so that the data moves to the right

Next in the row one, duplicate the values from your data

Next in Cell A2 Put this formula

=IF(COUNTIF($H$2:$L$2,A1)>0,A1,"")

Copy the formula to the right

Next remove "$" from the table range and add it to the header in formula in Cell A2 so that we can copy the formula down. This is how it would look

=IF(COUNTIF(H2:L2,$A$1)>0,$A$1,"")

Similarly your B2 formula will look like this

=IF(COUNTIF(H2:L2,$B$1)>0,$B$1,"")

Change it for the rest

How highlight cells A2:E2 and copy the formula down.

Your final Sorted Data looks like this.

Copy columns A:E and do a paste special values on Col A:E itself so that the formulas change into values and then delete Cols H:L


Need Your Help

If we delete a branch on the server, can we automatically delete it in our local copy?

git github branch

Usually, when I send Pull Requests on github, I would create a branch, make my changes, then ask them to merge that branch into their repo.

How to perform cross-site scripting attack testing that has actionable feedback?

javascript ruby-on-rails ruby-on-rails-3 security xss

I have a site security monitor that just recently started raising errors about cross-site scripting attacks. I've found the test results to be quite lacking in actual details for issue resolution....