Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 19

Thread: Reorder Columns Based on Headers

  1. #1
    Join Date
    26th July 2012
    Posts
    3

    Reorder Columns Based on Headers

    I have been struggling with this problem and am relatively new to VBA, so apologies in advance. I've got a spreadsheet with 55 columns of survey data and when I receive data from others, the columns can be in a different order. I would just record a macro of cutting and pasting columns in the correct order, except the order could be different coming from different users. So, I need to lookup the header values in the top of the table and, no matter what order I receive them in, rearrange them into the exact same target order each time (either on the same sheet or on a new sheet).

    An example of the first few columns would be:
    Author, Department, Business Process, Frequency, Question 1, Question 2, Question 3, Question 4, Question 5 (...and so on until column 55)

    However, I may receive this from other users:
    Author, Department, Frequency, Business Process, Question 1, Question 3, Question 2, Question 5, Question 4 (...and so on until column 55)

    Some of the headers are long strings of text too, because they are actual survey questions.

    I'd appreciate any help with this. Thank you so much.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    31st July 2012
    Location
    Houston, TX
    Posts
    54

    Re: Reorder Columns Based on Headers

    Hey Missartemis,

    I have a couple of clarification questions about your project, but I think it should be fairly straightforward.

    1) Are the headers always in Row 1? Or does it vary like the order?
    2) Is it always the same 55 columns or is it possible to have new columns (new headers) or fewer columns (lose headers)?

    With this information we should be able to write you some code that you can then massage to work for the exact scenario that you want.

    Cheers,
    -Wilson

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    26th July 2012
    Posts
    3

    Re: Reorder Columns Based on Headers

    Hi Wilson,

    1) Yes, the headers are always going to be in Row 1.
    2) It is possible to have a few extra columns (57 instead of 55) in the data received from other users. Those two possible new headers aren't any answers that I need to track though. There should never be fewer headers though.

    I hope this helps clarify a little bit. Appreciate the reply!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    31st July 2012
    Location
    Houston, TX
    Posts
    54

    Re: Reorder Columns Based on Headers

    Hey MissArtemis,

    This may not be the most elegant solution, but I believe it should work. See code below:

    VB:
    Sub Column_Sort() 
         
        Dim columncount As Integer 
        Dim columncheck As Integer 
        Dim firstvalue As Integer 
        Dim secondvalue As Integer 
        columncount = 1 
        columncheck = 2 
         
         
        Do Until columncheck = 58 
            Do Until columncount = columncheck 
                firstvalue = Get_Value(columncount) 
                secondvalue = Get_Value(columncount + 1) 
                If firstvalue > secondvalue Then 
                    Cells(1, columncount).EntireColumn.Cut 
                    Cells(1, columncount + 2).Insert 
                    columncount = columncount - 1 
                    exitcount = 0 
                ElseIf firstvalue = secondvalue Then 
                    Cells(1, columncount).EntireColumn.Cut 
                    Cells(1, 58).EntireColumn.Insert 
                    columncount = columncount - 1 
                    exitcount = exitcount + 1 
                    If exitcount > 5 Then 
                        columncount = columncount + 1 
                    End If 
                Else 
                    exitcount = 0 
                End If 
                columncount = columncount + 1 
            Loop 
            columncount = 1 
            columncheck = columncheck + 1 
        Loop 
         
         
    End Sub 
    Function Get_Value(ByVal titlename As Integer) 
         
         
        Select Case Cells(1, titlename).Value 
        Case "Author" 
            Get_Value = 1 
        Case "Department" 
            Get_Value = 2 
        Case "Business Process" 
            Get_Value = 3 
        Case "Frequency" 
            Get_Value = 4 
        Case Else 
            Get_Value = 57 
        End Select 
         
         
    End Function 
    
    
    The code above would have to be put into a Module (In the toolbar in the VBA window go to Insert > Module). You can edit to suit the order you would like the columns to be sorted in using the Function. In the above code for example, "Author" would be Column #1, "Department" would be Column #2, etc... You can simply duplicate the
    VB:
    Case "Author" 
        Get_Value = 1 
    
    
    section over and over and change what is inside the quotes to the exact text in your column header and change the number to match where you want the column to appear (1 = "A", 2 = "B", etc...). Let me know if this works for you or if you have any questions.

    Cheers,
    -Wilson

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th August 2011
    Location
    Brisbane, Austalia
    Posts
    3,246

    Re: Reorder Columns Based on Headers

    Hi

    You can do the same thing a different way. Shouldn't matter how many columns you have so it will cope if you change from 57. Example file attached. Original data sheet 1, new imported data sheet 2 and the results goto sheet3.

    Take care

    Smallman

    VB:
    Option Explicit 
    Sub test() 
        Dim Ar As Variant 
        Dim i As Integer 
        Dim lc As Integer 
        Dim fnd As Integer 
         
        lc = Range("IV1").End(xlToLeft).Column 
        Ar = Sheet1.Range(Cells(1, 1), Cells(1, lc)) 
         
        For i = 1 To lc 
            fnd = Sheet2.Rows(1).Find(Ar(1, i)).Column 
            Sheet2.Columns(fnd).Copy Sheet3.Cells(1, i) 
        Next i 
    End Sub 
    
    
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    26th July 2012
    Posts
    3

    Re: Reorder Columns Based on Headers

    Hi Wilson and Smallman,

    Thank you both for the solutions! I wasn't able to update right away, but the problem is fixed now. Thank you again - you guys rock.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    15th May 2017
    Posts
    7

    Re: Reorder Columns Based on Headers

    Hello,

    I needed to reorder data columns and your spreadsheet is the thing.
    I do have a problem though with some mismatches. Example: I have a column called "OR" and one called "Horse" and the excel results sheet returns "Horse" in the "OR" column. Would ir be possible to have a full match between column headers to avoid this issue? The data are exported statistics and have always the same column name.

    Thank you and best regards,
    Laurent

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    15th May 2017
    Posts
    7

    Re: Reorder Columns Based on Headers

    ... Forgot to ask:
    If a column on sheet 1 doesn't appear on sheet 2, I get an error message. Any way around that?

    Thank you,
    Laurent

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    6th January 2005
    Posts
    283

    Re: Reorder Columns Based on Headers

    Can you give me a list of every column header you want, in the order you want them? And I assume the client list will use the same spelling ie Horse = Horse, Not Horse = "Hrse" or such like?

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    15th May 2017
    Posts
    7

    Re: Reorder Columns Based on Headers

    Found an answer to my first question. I guess it takes "OR" as an instruction instead of a column name string. It works when I change the column name into "Off. OR".

    It leaves on question: If a column on sheet 1 doesn't appear on sheet 2, I get an error message. Any way around that?

    Thank you,
    Laurent

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Reorder Columns By The Column Name
    By John215 in forum Excel General
    Replies: 6
    Last Post: May 1st, 2012, 02:06
  2. Reorder columns by name + Wildcard?
    By John215 in forum Excel General
    Replies: 2
    Last Post: April 28th, 2012, 04:03
  3. Reorder columns by name
    By dmginaz in forum Excel General
    Replies: 2
    Last Post: March 23rd, 2012, 01:29
  4. Reorder Columns
    By billyj in forum Excel General
    Replies: 4
    Last Post: October 26th, 2005, 20:59

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno