Sort sheet based on header criteria

**axtryo** · 06-14-2017, 02:01 AM

Hi all,

I have a challenging task that really goes beyond my knowledge. I need code doing the following.

Attached book has 2 sheets. Sheet 1 contains a list the way it's passed on to me. Cell E1 (highlighted just for visual purposes), comes with a different content each time. That specific content dictates how the below rows should be sorted out. All cells containing that criteria (plus any additional content) have to end in column E. Sheet 2 shows how I need the sheet to end. From sheet 1, notice row 5 has content down to column F. In that case, I need to insert a row below row 5, and move the content of E5 down to D6 and F6 to E6. Then, row 10 in Sheet 1, has content down to G10, so 2 new rows need to be inserted below it, and the content of E10 needs to be moved down to D12 & G10 down to D13, (so F10 moves to E11), as shown in Sheet 2. Notice in this row, however, F10 is the cell containing the criteria from the header but it comes first in order than G10 (since cells per row are sorted out alphabetically based on their content), therefore, it needs to end as cell E11. Row 12 in Sheet 1 is similar than row 5.

Guess a loop that looks into the content of E1 and then reads each row to compare it is the way to go? ...

Attached book is a sample. The lists I receive actually contain many more rows (up to a few thousands) and, in some cases, content of some rows might go down to column J. Nevertheless, cell E1 will always be the one dictating the sort. Is this even possible? ...

Any help will be highly appreciated.

Note: I don't need a new sheet created, I added it just to show how the list is meant to end.

**dflak** · 06-14-2017, 04:32 PM

See the attached spreadsheet. Note that the code is hard coded for only two additional locations. Also the code refers to a source sheet (sheet with raw data) and destination sheet. It is marked clearly in the code where the sheet names need to be changed if required.