• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to Re-Arrange columns on same sheet by column name or column index no?

Dar sir,

How to rearange columns on same sheet by column name or column index no?.

I have daily many files received by another sources.that require re-arrange columns
as per required structures...

like COLUMN C=A, E=B, I=C, J=D, G=E etc.

how to Re-Arrange that columns either name or column index number ??

please note that there are possibility of change heading name of columns in any future time. so search for heard name is meaning less...

if code available for that, It will be very helpful to me...

any help will be appreciated & hope there are some solution available there

Regards,
Chirag Raval
 

NARAYANK991

Excel Ninja
Hi ,

Please upload your workbook , and specify exactly which columns will be located where ; if possible , we should go by column labels (headers) instead of column numbers.

If possible , use two worksheets to show the Before and After condition.

Narayan
 
Dear Sir, Narayank,

Thanks for your support,

Attached as per your requirement...Sheet1 as "Original" & Sheet 2 as "Required".

in same sheet columns should be re-arrange (not required another sheet)

hope your co-operation

Regards,
Chirag Raval
 

Attachments

NARAYANK991

Excel Ninja
Hi ,

Can you confirm that the following 4 columns are not required in the output ?

SEASON , AREACODE , CUST_GRP and BLEND_DET

Narayan
 

rahulshewale1

Active Member
Hii @Chirag R Raval

Please find the attached solution Using Power Query.

It is very easy in power Query,

Reorder Column Formula
= Table.ReorderColumns(#"Changed Type",{"CUSTOMER", "BRAND", "ORDER_DP", "BLEND", "MATERIAL", "UNIT_CODE", "ORD_UNIT", "QUANTITY", "EX_PRICE", "VALUE", "SRNO", "TAGNO", "SEASON", "AREACODE", "CUST_GRP", "BLEND_DET"})

Delete Unwated Column
= Table.RemoveColumns(#"Reorderd Column",{"SEASON", "AREACODE", "CUST_GRP", "BLEND_DET"})


Regard
Rahul shewale
 

Attachments

Dear Sirs

Thanks for effort...

Dear sir Narayan.... surely that columns not required....
(hope you something give more useful code that I can use universally to "Delete" + "Shift" columns)

Dear sir Rahulshewale1....
thanks ..but I have excel 2010 have no power query & don't know about it...
is that matter of table....or something else?

regards,
Chirag Raval
 
Dear Narayan Sir,

That 4 columns not required....I forgot to mentioned in my first thread...& by mistake mentioned only 2 on required sheet thanks....That 4 columns not required

Regards,
Chirag Raval
 

rahulshewale1

Active Member
Hii @Chirag R Raval,

I am using Excel 2010.i would suggest us power query it works fast.

https://www.myexcelonline.com/blog/installing-power-query-excel-2010/

https://www.microsoft.com/en-in/download/details.aspx?id=39379

after Installed power Query ..follow step,

1. Convert Data range in Table (Shortcut Key is Ctrl + T)
2.Select Column which you want Move Hold Mouse Left Key and Move right or left as your Requirement.
3.For Remove Unwanted Column : Right Click Column which you want Delete Select Delete Option.
4.Click on "Close & Load Option "
5. You will get desire Result.


Regard
Rahul shewale
 

Attachments

Dear Sir rahulShewale..

Thank for your guidelines..

I have a macro that separates many files from large database in particular folder ..with many types formatting & processing included one part is Columns Re-Arranging...if I try to do it manually ,even if with power query...many days passed ...endless work...
can not work without macro...

so if code for re-arrange columns found , it will tremendous helpful for me...

Regards,
Chirag Raval
 
Dear Sir @RahulShwwalw1,

I installed Power query from your given links ...I will study it that how it can be helpful to me ..I start collecting information about it's power ....

thank you very much.

regards,
Chirag Raval
 
Dear Sirs,

Can something found on that?
It will be my life saver .. Delete some volume &
Re-Arrange colums as desired...

Regards,

Chirag Raval
 

rahulshewale1

Active Member
Dear @Chirag R Raval

See if is ok ?
Code:
Sub Reorder_col()
'
' Reorder_col Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
'
    Columns("C:C").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Sheets("original").Select
    Columns("I:I").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("B1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Sheets("original").Select
    Columns("G:G").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("C1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Sheets("original").Select
    Columns("H:H").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("D1").Select
    ActiveSheet.Paste
    Columns("D:D").EntireColumn.AutoFit
    Range("E1").Select
    Sheets("original").Select
    Columns("J:J").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    ActiveSheet.Paste
    Sheets("original").Select
    Application.CutCopyMode = False
    Columns("F:F").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("F1").Select
    ActiveSheet.Paste
    Sheets("original").Select
    Application.CutCopyMode = False
    Columns("L:L").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("G1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Sheets("original").Select
    Columns("M:M").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("H1").Select
    ActiveSheet.Paste
    Sheets("original").Select
    Application.CutCopyMode = False
    Columns("K:K").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("I1").Select
    ActiveSheet.Paste
    Sheets("original").Select
    Application.CutCopyMode = False
    Columns("N:N").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("J1").Select
    ActiveSheet.Paste
    Sheets("original").Select
    Application.CutCopyMode = False
    Columns("E:E").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("K1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Sheets("original").Select
    Columns("O:O").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("L1").Select
    ActiveSheet.Paste
    Range("N7").Select
    Sheets("original").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWorkbook.Save
    Range("D1").Select
End Sub
 
Last edited by a moderator:
Dear Sir @rahulshewale1 ,

Sorry to say but..Whole thing goes wrong...you catch Wrong file ...please download from my post no 284 of this thread for test.

Thanks for your try to help me...but ...i can also record macro...main point is recording will be hard coded without optimisation...& optimisation can do only through proper logical methods of coding & proper tools apply logically ...& only those I have less knowledge...

so I need a code that optimised with few lines but efficiently work..

hope you can understands..

Hi ,

Give me some time.

Narayan
Dear sir @NARAYANK991 ...sorry to disturb you ...to often divert your attention
from your work...I will be wait....till then I don't disturb you...

Regards,
Chirag Raval
 
Dear Sir @NARAYANK991 ,

Thank you very much ...its Working ...Rocking ...like a Magic on sheet.
exact...as desired...

I declare further variables in you code as per below.
Code:
          Dim maxrevitems As Variant
          Dim dataitems As Variant
          Dim i As Integer
          Dim currheaderlabel  As Variant
          Dim j As Variant
          Dim k As Variant
please guide..I declare right types of above variables ?

but its working....and also many thanks for your further help through code to force excel to convert data types of columns which are numbers but excel recognised those as text or general...


Again many Thanks to all for your tremendous supports..


Regards,

Chirag Raval
 

NARAYANK991

Excel Ninja
Hi ,

You can either retain the declarations you have posted , or change them to :
Code:
          Dim Origheadarray As Variant, Revheadarray As Variant, origdataarray As Variant, revdataarray As Variant
          Dim maxorigitems As Integer, maxrevitems As Integer, i As Integer, j As Integer
          Dim lastrow As Long, dataitems As Long, k As Long
          Dim currheaderlabel  As String
Narayan
 
Dear Sirs MarcL & Narayn Sirs..

Yes....
I always in favour to declare explicitly
We can not do logic without helping of Variable
& should use variant type only When We can no have have options..

Thank you Narayan sir for helping as to choose guide right variable
Types.. I will change.

Thanks again to you all experts to resolve this thread..

Regards,

Chirag Raval
 
Top