CODEWITHSUNDEEP
excelspreadsheetexcel-2010
Patrick_92
Patrick_92

Reputation: 99

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:

example

How it should look after the sort:

desired result

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.

Upvotes: 1

Views: 1566

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

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

enter image description here

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

enter image description here

Next in Cell A2 Put this formula

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

enter image description here

Copy the formula to the right

enter image description here

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

enter image description here

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

enter image description here

Your final Sorted Data looks like this.

enter image description here

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

Upvotes: 2

Related Questions