Announcement

Collapse
No announcement yet.

Reorder Columns Based on Headers

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    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

    Comment


    • #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!

      Comment


      • #4
        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:

        Code:
        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
        Code:
        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

        Comment


        • #5
          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

          Code:
          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
          sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

          Comment


          • #6
            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.

            Comment


            • #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

              Comment


              • #8
                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

                Comment


                • #9
                  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?

                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      Re: Reorder Columns Based on Headers

                      For i = 1 To lc
                      on error resume next
                      Fnd = 0
                      fnd = Sheet2.Rows(1).Find(Ar(1, i)).Column
                      on error goto 0
                      If Fnd <> 0 Then
                      Sheet2.Columns(fnd).Copy Sheet3.Cells(1, i)
                      end if
                      Next i

                      Comment


                      • #12
                        Re: Reorder Columns Based on Headers

                        Amend code above, basically it will only copy it if it finds it, else it finds next one..

                        Code:
                        For i = 1 To lc 
                        on error resume next
                        Fnd = 0
                        fnd = Sheet2.Rows(1).Find(Ar(1, i)).Column 
                        on error goto 0
                        If Fnd <> 0 Then
                        Sheet2.Columns(fnd).Copy Sheet3.Cells(1, i) 
                         end if
                            Next I

                        Comment


                        • #13
                          Re: Reorder Columns Based on Headers

                          Thank you very much, I can now set my standard column headers in sheet 1, regardless if I export the data next time around.
                          Sheet 3 now returns blank columns when and where sheet 1 column headers are missing in sheet 2 (exported data). Can these blank columns in sheet 3 be headed by that same column header taken from sheet 1, obviously without any further data in the column?

                          Again, thank you very much for your support!
                          Best regards,

                          Laurent

                          Comment


                          • #14
                            Re: Reorder Columns Based on Headers

                            Code:
                            For i = 1 To lc 
                                On Error Resume Next 
                                Fnd = 0 
                                fnd = Sheet2.Rows(1).Find(Ar(1, i)).Column 
                                On Error Goto 0 
                                If Fnd <> 0 Then 
                                    Sheet2.Columns(fnd).Copy Sheet3.Cells(1, i) 
                                else
                                    Sheet3.cells(1, i)= Sheet1.cells(1,i)
                                End If 
                            Next I

                            Comment


                            • #15
                              Re: Reorder Columns Based on Headers

                              Just WOW!!!

                              Cheers!
                              Laurent

                              Comment

                              Working...
                              X