Mind Tickler

[jswhip]

I am looking for vba code to sort columns based upon values in another set of columns. The number of columns will change row by row, thus it needs to be dynamic in nature.

For example, the first row may have the following fields in columns A-H:

Field_A, Field_B, Field_C, Field_D, 3, 2, 7, 1 (where the 4 numeric values correspond to the first 4 fields).

Thus, I would like to sort columns A-D based upon the values in E-H. I.E - the output columns should be:
Field_D, Field_B, Field_A, Field_C, 1, 2, 3, 7

I thought these could be loaded into a dynamic two dimensional array and then sorting, but not sure how to go about doing it. Open to other suggestions as well if there is a better way.

 

[VoG]

You need to sort backwards, starting at the rightmost column, going backwards. The macro recorder will give you the code.

 

[jswhip]

Thanks for replying - appreciate it.

Not sure if I follow "....sort backwards, starting at the rightmost column, going backwards." Would you elaborate?

Thank you.

 

[VoG]

OK. Excel's sort feature only allows you to pick up to 3 columns to sort by - here is how to get around this. The trick is sorting by the last key first and working back to the first key. Say your data is in Columns A:E and you want to sort by A, B, C, D then E:

1. Select all of Columns A:E
2. Go to Data>Sort> sort by C then by D then by E
3. Click Sort
4. Now again with Columns A:E selected
5. Go to Data>Sort> sort by A then by B
6. Click Sort