You are here:

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


Hello Tom,

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 -

Select Code  copy to clipboard
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
   End If

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

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

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

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 6 digit code numbers that comes immediately before all the names. (refer snapshot)

Can I send you my sample sheet. Any assistance will be greatly appreciated.


You will need to send the sample sheet, but it needs to have the data that you say does not work.  You will also have to show some example of what you expect the output to be after the macro runs.  You can send it to

It is disappointing that you asked me to modify your previous code which took quite sometime and then immediately I see you asking other experts to write new code for what appears to be the same problem.  My time may not be valuable to you but it is to me. If you send this to me, then make sure you intend to use it - otherwise work with whoever wrote the code above.   

Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 All rights reserved.