locked
Removal of Errors from a Column of Data RRS feed

  • Question

  • I have an interesting but frustrating problem that I'd appreciate assistance with.

    I currently have data in column A of a worksheet that are obtained from a .csv file. The data are typically 1 to 5 characters in length, with no punctuation separating the characters. The column is alphabetically sorted A - Z.

    Occasionally, errors occur in the .csv file which then creates errors on the worksheet. The errors are in the form of a period followed by a single character. For example, what should be AABC may occur as AABC.Q, or what should be XX occurs as XX.A
    The error only occurs at the end of the string.

    In addition, the error will occur in one of two ways:
    a)   It will add the period and letter to an entry but not duplicate the entry. In this case, for example, the column might include:
    ABC
    ABD
    ABE.Q
    ABF     and so on down; or
    b)   It will add the period and letter to a duplicate of an entry. In this case, for example, the column might include:
    ABC
    ABD
    ABE
    ABE.Q
    ABF     and so on down.

    I need VBA coding that will:
    a)   Loop down through column A (starting at cell A3 because A1 & A2 are headers) looking for occurrences where the last two characters of the string are a period and a letter; and
    b)   Delete the offending period and letter from the string

    I then need to go back through the column and delete duplicates, and then re-sort the column to get rid of gaps created by the deletion of the duplicated entry/entries.

    Help with this will be much appreciated.
    Saturday, July 2, 2016 9:57 AM

Answers

  • Hi Hans

    Don't worry - I've solved the problem. I tried your coding on another column of data - with errors - and it works perfectly. The problem was with the way the data gets into column A from the .csv file. I'm going to have to revamp some of my other coding but I can see that ultimately I'll get it to do exactly what I want.

    Many thanks for your guidance once again - I think that's the third one you've solved for me! Much appreciated.
    • Marked as answer by DaviDWF2 Saturday, July 2, 2016 2:08 PM
    Saturday, July 2, 2016 2:07 PM

All replies

  • Here is a macro. Please test on a copy of your data first.

    Sub RemoveDotLetter()
        Dim c As Range
        Dim s As String
        Dim f As Boolean
        Application.ScreenUpdating = False
        With Range("A2:A" & Rows.Count)
            Set c = .Find(What:="*.?", LookAt:=xlWhole, SearchDirection:=xlPrevious)
            If Not c Is Nothing Then
                Do
                    s = c.Address
                    If Right(c.Value, 1) Like "[A-Za-z]" Then
                        c.Value = Left(c.Value, Len(c.Value) - 2)
                        If c.Value = c.Offset(-1).Value Then
                            c.ClearContents
                            f = True
                        End If
                    End If
                    Set c = .FindNext(After:=c)
                    If c Is Nothing Then Exit Do
                Loop Until c.Address = s
            End If
            If f Then
                .Sort Key1:=Range("A2"), Header:=xlNo
            End If
        End With
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, July 2, 2016 10:28 AM
  • Hi Hans

    I've created that code and run it but it has no effect - no errors but also no activity of any sort

    Saturday, July 2, 2016 12:57 PM
  • I've just gone back and had a detailed look at both the errors, the .csv files and then at the source data that generate the .csv files. I've suddenly realised that there is actually no valid source data and the errors in the .csv files are just that - errors.

    This should therefore simplify the coding somewhat. All I require now is for the coding to loop down the column seeking cells in the column that contain a period, and totally delete the contents of those cells. Then re-sort the column to get rid of the blank cells.
    Saturday, July 2, 2016 1:15 PM
  • This should remove the cells with a dot:

    Sub RemoveDot()
        Application.ScreenUpdating = False
        With Range("A2:A" & Rows.Count)
            .Replace What:="*.*", Replacement:="", LookAt:=xlWhole
            .Sort Key1:=Range("A2"), Header:=xlNo
        End With
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, July 2, 2016 1:23 PM
  • Hi Hans

    Something not quite right here. Running that code results in:
    #Ref! now occurs in A2, A3 & A4
    A72 contains an error   (BRY.B)   and is untouched
    A103 & A104 each had their data replaced with a 0
    A196 contains an error  (FRV.W)   and is untouched
    A203 & A204 each had their data replaced with a 0
    A303 & A304 each had their data replaced with a 0
    A403 & A404 each had their data replaced with a 0
    A454 contains an error   (UA.C)   and is untouched
    Saturday, July 2, 2016 1:51 PM
  • Hi Hans

    Don't worry - I've solved the problem. I tried your coding on another column of data - with errors - and it works perfectly. The problem was with the way the data gets into column A from the .csv file. I'm going to have to revamp some of my other coding but I can see that ultimately I'll get it to do exactly what I want.

    Many thanks for your guidance once again - I think that's the third one you've solved for me! Much appreciated.
    • Marked as answer by DaviDWF2 Saturday, July 2, 2016 2:08 PM
    Saturday, July 2, 2016 2:07 PM