Macro to sort raw data, remove duplicates and paste 2 specific data on a different sheet

Hello,

Requesting all excel experts for their assistance.

Cross posted -

http://forum.chandoo.org/threads/mac...7/#post-158477

The macro was created with a dummy raw data. It worked perfectly.

Now, the problem is, the code doesn't seem to work with actual raw data.

The code -

Sub test()

Application.ScreenUpdating = False

Dim i As Long, j As Long, k As Long, k1 As Long, cnt As Long
Dim rng As Range, fnd As Range
Dim x As Integer
i = Sheet2.Cells(Rows.Count, "R").End(xlUp).Row
k = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row

Sheet1.Range("$A$5:$A$" & k).ClearContents

Set rng = Sheet2.Range("R1:R" & i)
Set fnd = rng.Find(What:="*" & "max" & "*", LookIn:=xlValues, MatchCase:=False)

If Not fnd Is Nothing Then
    Sheet2.Range("R" & Sheet1.Range("D2") & ":R" & fnd.Row - 1).Copy
    Sheet1.Range("A5").PasteSpecial xlPasteValues
End If

k = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row

For j = k To 5 Step -1
On Error Resume Next
    If WorksheetFunction.Search("/", Sheet1.Range("a" & j), 1) <= 0 Then
        Rows(j).EntireRow.Delete
    End If
Next

k = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
k1 = Sheet2.Cells(Rows.Count, "F").End(xlUp).Row

Sheet2.Range("$F$1:$G$" & k1).ClearContents

cnt = 2

For j = 5 To k
    Sheet2.Range("G" & cnt) = Mid(Sheet1.Range("a" & j), WorksheetFunction.Search(" ", Sheet1.Range("a" & j), 1) + 2, 2) + 0
    Sheet1.Range("a" & j) = WorksheetFunction.Substitute(Sheet1.Range("a" & j), "   ", " ")
    Sheet2.Range("F" & cnt) = Trim(Mid(Sheet1.Range("a" & j), WorksheetFunction.Search("MR ", Sheet1.Range("a" & j), 1) + 2, 7))
    cnt = cnt + 1
Next

k1 = Sheet2.Cells(Rows.Count, "F").End(xlUp).Row

Sheet2.Range("$F$1:$G$" & k1).RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlNo

Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub

How the code works -

Raw data is pasted in column R in sheet2.

On sheet1 in cell D2 a row number is inserted. This row number tells the macro from where the raw data in sheet2 needs to be copied from.

Keyword min and max is entered in raw data on sheet2. Keyword max tells the macro till where the raw data needs to be copied.

Let's say, row number entered is 20 and keyword max is on row number 77. Macro copies raw data from cell 20 till cell 76. It will not copy cell number containing the keyword max.

This data is then pasted in column A cell 5 in sheet1, where, macro sorts the data, removes duplicates and splits 2 specific data which are pasted in 2 columns F and G in sheet2 (More precisely from F2 and G2). Keyword min is removed when the sorting is done.

Few things to note -

1) Duplicates are removed based on 6 digit code
2) 2 specific data that needs to separated after sorting raw data are (refer snapshot)

• 6 digit code
• numbers that comes immediately before all the names

I have attached sample sheet with the code

Note -

Sheet2 has dummy raw data based on which the code was developed. Run the macro from sheet1 to get an idea about the end result.

I have included actual raw data on sheet3. I want the macro to work on actual raw data.

Any assistance will be greatly appreciated.