VBA - find and remove duplicate records

[zareva]

Number 1	Date	Number 2	Name	Name ID	Amount	Currency	Receipt Code	Time	Status	Status1
12345678901234	10/31/2015	30340259	john	123	769	USD	9315	2014-03-06T04:20:54.977+0000	Unmatched	Successful
12345678904321	10/31/2015	30336976	lily	343	1349.96	USD	7781	2014-03-06T02:59:32.503+0000	Unmatched	Successful
12345678901234	10/31/2015	30340259	john	123	769	USD	9315	2014-03-06T04:20:54.977+0000	Reconciled	Successful
12345678904321	10/31/2015	30336976	lily	343	1349.96	USD	7781	2014-03-06T02:59:32.503+0000	Reconciled	Successful
12345678906644	10/31/2015	30336925	Tony	999	1817.94	USD	7752	2014-03-06T02:57:47.900+0000	Unmatched	Successful

<tbody>
</tbody>

I have the table above.
The objective is a VBA code to loop through the column named - Number 2 and if it finds duplicate records (values) to delete all the rows that contains such duplicates. For example, if the code finds that rows 1 and 3 contain one and the same value in the Number 2 column, to delete both rows -1 and 3. Furthermore, the code has to copy the unique records (rows) that hadn't been deleted to a new sheet. In this sheet I need to have a search box that will allow me to search by the whole or only part of the number in Number 1 column - just s ctrl+f works.
As a final result,I need to have a table in the new sheet that only consist of the last row in the example table above. Please have in mind that I will add new records daily to the initial table and need to have only unique records in the second sheet. Might be, it's a god idea if the initial table (sheet 1) be copied firstly to a new sheet and afterword the code to starts. This way I will not loose the original data in sheet one, which is required.
Thank you in advance,
Zareva

 

[zareva]

Hi guys,

If it's so difficult such a code to be implemented, please give me any clues and ideas how to sort my issue out.

Thank you in advance,
Zareva

 

[GTO]

Greetings Zareva,

What code have you written so far?

Mark

 

[zareva]

Greetings Zareva,

What code have you written so far?

Mark

nothing so far I'll be grateful if you are able to do something here
just to clarify - I need the code to delete all rows that contain one and the same values in Number 2 column and to create a new sheet that consist of the rows left (without duplicates). Also the second sheet has to have a search box that will allow me to search by the whole or only part of the number in Number 1 column - just as ctrl+f works. The initial sheet should keep the original data unviolated (no deleted records) cos I everyday add new unmatched records there.

Thank in advance,
Zareva

 

[GTO]

Well here is the first part, if only a smaller number of rows. I am afraid that I missed that last part until too late (delete all the rows that contain... vs. keep the original data)

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>  <br><SPAN style="color:#00007F">Sub</SPAN> example()<br><SPAN style="color:#00007F">Dim</SPAN> rngData <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> n       <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>  <br>  <SPAN style="color:#00007F">With</SPAN> Sheet1 <SPAN style="color:#007F00">'<---Using CodeName</SPAN><br>    <br>    <SPAN style="color:#007F00">'// Find last cell with data    //</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> rngData = RangeFound(.Range("C:C"))<br>    <br>    <SPAN style="color:#007F00">'// If no cell with data, OR, if cell with data as at less than row 3 (no possible  //</SPAN><br>    <SPAN style="color:#007F00">'// duplicates)                                                                     //</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> rngData <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> rngData.Row < 3 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#007F00">'// Set a reference to cells in Col C   //</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> rngData = .Range(.Range("C2"), rngData)<br>    <br>    <SPAN style="color:#007F00">'// Plunk in a formula to detect Dups //</SPAN><br>    rngData.Offset(, 9).Formula = _<br>      "=COUNTIF(" & rngData.Address(-1, -1) & "," & rngData.Cells(1).Address(0, 0) & ")"<br>    <SPAN style="color:#007F00">'// Overwrite return of formula w/values    //</SPAN><br>    rngData.Offset(, 9).Value = rngData.Offset(, 9).Value<br>    <br>    <SPAN style="color:#007F00">'// Run up from the bottom, deleting non-uniques  //</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> n = rngData.Rows.Count <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1<br>      <SPAN style="color:#00007F">If</SPAN> rngData.Offset(, 9).Rows.Value > 1 <SPAN style="color:#00007F">Then</SPAN><br>        rngData.Rows.EntireRow.Delete<br>      <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN><br>    <br>    <SPAN style="color:#007F00">'// Rid formula returns //</SPAN><br>    rngData.Offset(, 9).Clear<br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>  <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>  <br><SPAN style="color:#00007F">Function</SPAN> RangeFound(SearchRange <SPAN style="color:#00007F">As</SPAN> Range, _<br>                    <SPAN style="color:#00007F">Optional</SPAN> <SPAN style="color:#00007F">ByVal</SPAN> FindWhat <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "*", _<br>                    <SPAN style="color:#00007F">Optional</SPAN> StartingAfter <SPAN style="color:#00007F">As</SPAN> Range, _<br>                    <SPAN style="color:#00007F">Optional</SPAN> LookAtTextOrFormula <SPAN style="color:#00007F">As</SPAN> XlFindLookIn = xlValues, _<br>                    <SPAN style="color:#00007F">Optional</SPAN> LookAtWholeOrPart <SPAN style="color:#00007F">As</SPAN> XlLookAt = xlPart, _<br>                    <SPAN style="color:#00007F">Optional</SPAN> SearchRowCol <SPAN style="color:#00007F">As</SPAN> XlSearchOrder = xlByRows, _<br>                    <SPAN style="color:#00007F">Optional</SPAN> SearchUpDn <SPAN style="color:#00007F">As</SPAN> XlSearchDirection = xlPrevious, _<br>                    <SPAN style="color:#00007F">Optional</SPAN> bMatchCase <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> = <SPAN style="color:#00007F">False</SPAN>) <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">If</SPAN> StartingAfter <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> StartingAfter = SearchRange(1)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> RangeFound = SearchRange.Find(What:=FindWhat, _<br>                                      After:=StartingAfter, _<br>                                      LookIn:=LookAtTextOrFormula, _<br>                                      LookAt:=LookAtWholeOrPart, _<br>                                      SearchOrder:=SearchRowCol, _<br>                                      SearchDirection:=SearchUpDn, _<br>                                      MatchCase:=bMatchCase)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

See what you can do as to first creating a copy of the original sheet, then delete rows of duplicate values.

Mark

 

[zareva]

Hi Mark,

Thank you very much - the code is working for a small amount of data but I hope it'll cope also with bigger data.
I would like to ask you to update the code so it firstly to copy the initial data to a new sheet, and to starts afterwards. This way it will not cut the original data. I hope it will not be a difficult for you- it's crucial for me.
I would like also to ask you to add a search box into the second sheet that will enable me to search by the number in Number 1 column. I want to search by this number or part of it, and when I press search, the code to display data only for this number, hiding the rest data. Is it possible?

Warm Regards,

Zareva