Sort columns by column names

JanTo

New Member
Joined
May 12, 2015
Messages
26
Hi all,

Is it possible to sort colums by column name. E.g I have 4 columns named A,B,C and D. The initial data is not always in this order, it means that at first VBA code should find the column named B and put it first then find D column and put it second etc.
ABCD
226813
752217
2481325
1212236

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

I would like to get result copied to the other sheet but with different order of columns:

BDCA
224618
52171
15387
9222414

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

Thank you in advance!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi,

in F1


=INDEX($A$1:$D$5,ROW($A1),MATCH(LOOKUP(COLUMN(A$1),ROW($A$1:$A$4),{"B","D","C","A"}),$A$1:$D$1,0))

to be copied across



ABCDEFGHI
1ABCDBDCA
2226813613822
3752217517227
424813258251324
512122361262312

<tbody>
</tbody>
Foglio5

Worksheet Formulas
CellFormula
F1=INDEX($A$1:$D$5,ROW($A1),MATCH(LOOKUP(COLUMN(A$1),ROW($A$1:$A$4),{"B","D","C","A"}),$A$1:$D$1,0))

<tbody>
</tbody>

<tbody>
</tbody>



Hope it helps
 
Upvote 0
Hi,
It works well but I prefer vba, because i have a very large database and it is needed to be done once. Then new sorted database will be manipulated with other vba codes.


Thank you anyways,
Jan
 
Upvote 0
Hi,

Can somebody tell my why recorded macro gives me an error when I´m trying to run it (marked with red):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)




' copy Macro
'


'
Range("L30").Select
Cells.Find(What:="chassi", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("C26").Select
Cells.Find(What:="item name", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("B1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("D26").Select
Cells.Find(What:="order date", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("C1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("I26").Select
Cells.Find(What:="internal del.date", After:=ActiveCell, LookIn:=xlValues _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("D1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("J26").Select
Cells.Find(What:="fetch date", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("E1").Select
ActiveSheet.Paste
Range("A46").Select
Sheets("Sheet1").Select
Range("L26").Select
Cells.Find(What:="delivery address", After:=ActiveCell, LookIn:=xlValues _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("F1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("T26").Select
Cells.Find(What:="text", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("G1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("AC26").Select
Cells.Find(What:="materialOk", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("H1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("BB26").Select
Cells.Find(What:="ppo", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("I1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("BO26").Select
Cells.Find(What:="wash", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("J1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("BP26").Select
Cells.Find(What:="pdi", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("K1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("BQ26").Select
Cells.Find(What:="last move", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("L1").Select
ActiveSheet.Paste
 
Upvote 0
Maybe start with something like this and adjust red font ranges to match your data.

This seems to do what you want for 500 rows.

Howard

Code:
Option Explicit

Sub Column_Adjust()

  Dim lRowCount&
  lRowCount = Cells(Rows.Count, "A").End(xlUp).Row
  
  With Range("F1").Resize(lRowCount, 4)
    .Formula = "=INDEX($[COLOR="#FF0000"]A$1:$D$500[/COLOR],ROW($A1),MATCH(LOOKUP(COLUMN(A$1),ROW([COLOR="#FF0000"]$A$1:$A$500[/COLOR]),{""B"",""D"",""C"",""A""}),$A$1:$D$1,0))": .Value = .Value
  End With
  
  Range("F1").Resize(lRowCount, 4).Cut Range("A1")
  
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top