Solved: Changing the column order in a worksheet according to an array

[AJS]

Hi All,

I have a series of data sets which I import into a worksheet, where the columns of data are often in a fairly arbitrary order. I'd like to be able to sort those columns according to the entry in the first row of these columns, but not alphabetically - rather, according to their order in an array held elsewhere in the workbook. Any ideas how I could do this?

Thanks, Aaron

[Dave]

After importing your data, load your data into an array in the column order you require, remove your original data and replace with your array contents or... before importing your data transfer the data from your existing array into a temporary array (in the order you require the output) and then transfer them back to the original array...this seems better. Dave
ps I may have misread this? If you already have your column order constructed in your array then just use "Cells(Row,Column)" to match your array position.

[AJS]

It may not be as easy as all that - to clarify, the array is a list of elements, sorted by increasing atomic number, and not all elements will be present in the dataset - usually, around 13-21 of them out of 80-odd in the array, and in no particular order. Also, the number of data points (rows) will be variable as well -usually somewhere between 200 and 700. It might be better to sort in-place by selecting whole columns... if I could figure it out!

Thanks, Aaron

[mdmackillop]

Hi Aaron,
Have a look at this attachment. It uses the order of the items on sheet2 to reorder the rows on sheet1. Note that there is a named range "MyData" (Sheet2A1:B80) used in the temporary formula, which will require to be adjusted for real data.
Regards
MD

[VBA] Option Explicit
Sub DoSort()
Dim Rw As Long, Col As Long
Rw = Cells(Cells.Rows.Count, 1).End(xlUp).Row()
Col = Cells(1, Cells.Columns.Count).End(xlToLeft).Column()
Columns("A:A").Insert
Range("A1:A" & Rw).FormulaR1C1 = "=VLOOKUP(RC[1],MyData,2,FALSE)"
Range(Cells(1, 1), Cells(Rw, Col + 1)).Sort Key1:=Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("A:A").Delete
End Sub

[/VBA]

[AJS]

Thanks for that - I wound up using the following variation:

[VBA] Cells(EleRow, i + 1).Select
Range(Selection, Selection.End(xlDown)).Select
NumAll = Selection.Rows.Count

Set EleRange = Range(Cells(EleRow, 2), Cells(EleRow + NumAll - 1, EleCols + 1))

Rows(EleRow).Insert
For i = 1 To EleCols
For j = 1 To 84
If EleAM(1, j) = Cells(EleRow + 1, i + 1) Then
Cells(EleRow, i + 1) = EleAM(2, j)
Exit For
End If
Next j
Next i
For i = 1 To EleCols
Set EleRange = Union(EleRange, Cells(EleRow, i + 1))
Next i

EleRange.Sort Key1:=Cells(EleRow, i + 1), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal

Rows(EleRow).Delete
[/VBA]

Thanks, Aaron