Results 1 to 6 of 6

Thread: Checking column headers on data extract - is there a better way
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    North Yorkshire, UK
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Checking column headers on data extract - is there a better way

    I receive a number of data files each month from a number of different sources, and while they are all supposed to be in a standard layout (column headers and column order), they often aren't.

    I've written some code that checks to see if the values on the sheet match to either...
    - the standard layout (in which case no changes are required)
    - a known incorrect layout (in which case, I know what actions are required to get the file back to standard layout - moving/deleting/adding/renaming columns)
    - a new unknown layout (in which case I display an error message)

    The code I have (simplified of course, the actual files have almost 60 column headers) is shown below, but it's a bit of a pain to populate all of the new values whenever a new data file layout turns up (roughly one new layout every 3 or 4 months on average).

    Is there an easier/more efficient/quicker to add new variations/better way of doing this?

    Code:
    Sub data_layout_checks()
    'v0: standard layout
    If UCase([A1]) = "NAME" And UCase([B1]) = "REGION" And UCase([C1]) = "AGE" And UCase([D1]) = "SALES" Then
        MsgBox "Standard layout.  No changes required."
        Exit Sub
    End If
    'v1: known incorrect version 1
    If UCase([A1]) = "NAME" And UCase([B1]) = "REGION" And UCase([C1]) = "SALES" And UCase([D1]) = "AGE" Then
        Columns("D:D").Cut
        Columns("C:C").Insert Shift:=xlToRight
        MsgBox "Converted to standard layout (v1)."
        Exit Sub
    End If
    'v2: known incorrect version 2
    If UCase([A1]) = "SALES" And UCase([B1]) = "DEPARTMENT" And UCase([C1]) = "REGION" And UCase([D1]) = "EMPLOYEE NAME" And UCase([E1]) = "AGE(Y)" Then
        Columns("B:B").Delete Shift:=xlToLeft
        Columns("A:A").Cut
        Columns("E:E").Insert Shift:=xlToRight
        Columns("A:A").Cut
        Columns("C:C").Insert Shift:=xlToRight
        [A1].Value = "Name"
        [C1].Value = "Age"
        MsgBox "Converted to standard layout (v2)."
        Exit Sub
    End If
    'error if unknown layout
        MsgBox "Unknown layout.  Unable to fix automatically."
    End Sub
    It's curtains for your Dr. Horrible ... lacy, gently wafting curtains.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,298
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Checking column headers on data extract - is there a better way

    If the headers are correct, but the order is not try
    Code:
    Sub SetCols()
       Dim ColAry As Variant
       Dim i As Long, lc As Long
       Dim Fnd As Range
       
       ColAry = Array("NAME", "REGION", "AGE", "SALES")
       
       Application.ScreenUpdating = False
          
       For i = 0 To UBound(ColAry)
          Set Fnd = Range("1:1").find(ColAry(i), , , xlWhole, , , False, , False)
          If Not Fnd Is Nothing Then
             If Fnd.Column <> i + 1 Then
                Fnd.EntireColumn.Cut
                Columns(i + 1).Insert Shift:=xlToRight
                Application.CutCopyMode = False
             End If
          End If
       Next i
       lc = Cells(1, Columns.count).End(xlToLeft).Column
       If lc > UBound(ColAry) + 1 Then Range(Cells(1, UBound(ColAry) + 2), Cells(1, lc)).EntireColumn.Delete
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    North Yorkshire, UK
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Checking column headers on data extract - is there a better way

    It could be that the headers are correct, but not in the right order, but it could also be:
    - wrong headers in the right order
    - wrong headers in the wrong order
    - missing columns that are not required (these are added in and populated with null)
    - additional columns that are not required (these are deleted)
    - any combination of all of the above

    I can't think of a better way to do it than my current approach. It works, but it's a just a pain when I have to add in a new variation with around 60 "and" criteria.
    It's curtains for your Dr. Horrible ... lacy, gently wafting curtains.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,298
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Checking column headers on data extract - is there a better way

    Depending on what the headers are, it may be possible to rename them.
    For instance if you only have one header that contains the word "Name" any instance of "Employee name", "First name", "Full name" etc could easily be changed to "Name"
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    North Yorkshire, UK
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Checking column headers on data extract - is there a better way

    There *should* only be one instance of each column name, although the same column can have different names from extract to extract depending on who produces it. I do have a list of standard column names and the known variations, so I can easily replace a "known incorrect" to make it "correct", but I have had instances where the same column has been duplicated (which is fine as long as I know in advance and I can then just delete one of the surplus instances), but there will be instances of missing columns and surplus columns to deal with as well.

    I need a way of saying "If the file is in "layout A", then that's fine and no changes are required, but if it's in "layout B" then I know that I need to do actions a, b, and c to make it standard, but if it's in "layout C" then I know that I need to do actions b, c, d, and e to make it standard (and so on for all known variations which are "fixable - i.e. have all mandatory columns in them somewhere so that I can work out what needs to go where, what needs to be renamed, what needs to be deleted, what needs to be inserted and so on so I can write that into the code), but if it's not standard or any of the known "fixable" variations, then I display a warning that the file cannot be fixed by the macro and that manual checking is required. Once manually checked, if it's got all the mandatory columns, I can then add that new variation to the code with instructions on how to fix it, so that if that particular version appears again, it then knows that while it's not standard, it does know what to do to make it standard.

    The code I have does do this, I was just wondering if there was a better way of doing it given all the typing of the headers provided in each column for the new variations.

    I had toyed with the idea of comparing arrays, but populating those was just as fiddly as listing all of the "A1 = name", "B1 = Age" and so on, and there was also the issue that the number of entries in each array wasn't necessarily always going to be the same which made array comparisons difficult. For example, if standard is "NAME", "REGION", "AGE", "SALES", but "AGE" is not a mandatory field, then a file that just contained NAME", "REGION", "SALES" (in any order, as long as it's an order I've seen before and know how to fix), is fine, but then I'd be comparing a 3 entry array (from the data file) to a 4 entry array (the standard column headers).

    I think it probably best to just stick with what I have, but thanks for your suggestions.
    It's curtains for your Dr. Horrible ... lacy, gently wafting curtains.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,298
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Checking column headers on data extract - is there a better way

    Ok, thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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