Organizing Data by Column Headers

loriann614

New Member
Joined
Dec 17, 2019
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
Hello,
I am attempting to write code that will organize my data based on the column headers. I have looked for an answer on other threads and found some that were close to what I am looking for but not quite, and as I am new to VBA I don't feel confident in my ability to recognize what I should change to fit my needs. I am hoping someone on here can help me out.

To be more specific, my data is exported from an online site into a CSV file. The rest of the data cleaning/analysis process that I have designed macros for requires that the data be in a specific order - meaning, Column A has to be the data with the header "Coded Result", Column B has to be the data with the header "Date Specimen Collected", etc. When the file is exported from the site, it is possible that the columns will not be in the right order. I know one could of course manually move columns around to be in the right order, but my coworkers would prefer it to be an automated process, so I am trying to design a macro that will select columns based on their header and copy/paste them to the right placement; my thinking is the easiest way to do this is to have the macro find the column based on the header name, copy the whole column, and then paste it to a different sheet where I specify which column to use (ex. "find column with header 'Patient DOB' in sheet1, copy the whole column, and paste to sheet2 column H"). If you have a different idea of how to accomplish this, though, I am all ears.

Some details - there are 15 different columns, so the code will have to identify and organize 15 different headers; the column headers will remain the same every time the data is pulled, but the length of the columns will change.

I hope all that makes sense. I greatly appreciate any help you can provide.
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
What are the 15 headers and their corresponding columns?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Rich (BB code):
Sub Loriann()
    Dim ary As Variant
    Dim i As Long
    Dim fnd As Range
    
    ary = Array("Ward", "District", "County", "postcode")
    For i = 0 To UBound(ary)
        Set fnd = Range("1:1").Find(ary(i), , , xlWhole, , , False, , False)
        If Not fnd Is Nothing Then
            If fnd.Column <> i + 1 Then
                fnd.EntireColumn.Cut
                Columns(i + 1).Insert
            End If
        End If
    Next i
End Sub
Change the values in red to match you headers & add the other in the same manner.
They need to be in the order, you want the columns at the end of the macro
 

loriann614

New Member
Joined
Dec 17, 2019
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Rich (BB code):
Sub Loriann()
    Dim ary As Variant
    Dim i As Long
    Dim fnd As Range
   
    ary = Array("Ward", "District", "County", "postcode")
    For i = 0 To UBound(ary)
        Set fnd = Range("1:1").Find(ary(i), , , xlWhole, , , False, , False)
        If Not fnd Is Nothing Then
            If fnd.Column <> i + 1 Then
                fnd.EntireColumn.Cut
                Columns(i + 1).Insert
            End If
        End If
    Next i
End Sub
Change the values in red to match you headers & add the other in the same manner.
They need to be in the order, you want the columns at the end of the macro

Wow this works perfectly! Thank you so much!!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,126,993
Messages
5,622,049
Members
415,875
Latest member
Tarali

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
Top