Remove Duplicate Data and Keep Unique in a separate sheet

[gimran]

Dear,

I have a data set among which there are many duplicate data and I want to find the unique data in separate sheet.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]1122[/TD]
[/TR]
[TR]
[TD]1133[/TD]
[/TR]
[TR]
[TD]1144[/TD]
[/TR]
[TR]
[TD]1155[/TD]
[/TR]
[TR]
[TD]1133[/TD]
[/TR]
[TR]
[TD]1122[/TD]
[/TR]
[TR]
[TD]1199[/TD]
[/TR]
[TR]
[TD]1133[/TD]
[/TR]
[TR]
[TD]1144[/TD]
[/TR]
[TR]
[TD]1100[/TD]
[/TR]
</tbody>[/TABLE]

Now I want the data to be like below:

[TABLE="width: 72"]
<colgroup><col width="72" style="width:54pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 72, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]1122[/TD]
[/TR]
[TR]
[TD]1133[/TD]
[/TR]
[TR]
[TD]1144[/TD]
[/TR]
[TR]
[TD]1155[/TD]
[/TR]
[TR]
[TD]1199[/TD]
[/TR]
[TR]
[TD]1100[/TD]
[/TR]
</tbody>[/TABLE]

Please help me to do that.

 

[tlowry]

Here is a vba sub:

Open/create a test workbook with a Worksheet which:

• Has the data in it
• Is named "Data"

	A
Data

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1122[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1133[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1144[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1155[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1133[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1122[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1199[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1133[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1144[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1100[/TD]

</tbody>

Data​

Put the following code into a module:

• Get to vba editor Development tab > Visual Basic
• Create a module Insert > Module
• Cut and Paste the code into the module

Save the workbook

Run the macro UniqueList

Sub UniqueList()
    Sheets("Data").Copy after:=Sheets(1)
    Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row) _
            .AdvancedFilter Action:=xlFilterInPlace, Unique:=True
   
End Sub

Results in a new worksheet:

	A
Data

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1122[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1133[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1144[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1155[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1199[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1100[/TD]

</tbody>

Data (2)​

 

[gimran]

Thanks for the reply, but is there any other process without macro and VBA.

Here is a vba sub:

Open/create a test workbook with a Worksheet which:

• Has the data in it
• Is named "Data"

	A
Data

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1122[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1133[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1144[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1155[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1133[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1122[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1199[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1133[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1144[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1100[/TD]

</tbody>

Data​

Put the following code into a module:

• Get to vba editor Development tab > Visual Basic
• Create a module Insert > Module
• Cut and Paste the code into the module

Save the workbook

Run the macro UniqueList

Sub UniqueList()
    Sheets("Data").Copy after:=Sheets(1)
    Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row) _
            .AdvancedFilter Action:=xlFilterInPlace, Unique:=True
   
End Sub

Results in a new worksheet:

	A
Data

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1122[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1133[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1144[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1155[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1199[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1100[/TD]

</tbody>

Data (2)​

 

[Mike Szczesny]

Here are two ways (formulas) to extract unique items. Hope this helps.

Excel 2012
	A	B	C	D	E
1	1122		1122		Unique
2	1133		1133		1122
3	1144		1144		1133
4	1155		1155		1144
5	1133		1199		1155
6	1122		1100		1199
7	1199				1100
8	1133
9	1144
10	1100
Sheet1

Cell Formulas
Range	Formula
C1	{=IFERROR(INDEX($A$1:$A$10,SMALL(IF(FREQUENCY(IF($A$1:$A$10<>"",MATCH($A$1:$A$10,$A$1:$A$10,0)),ROW($A$1:$A$10)-ROW($A$1)+1),ROW($A$1:$A$10)-ROW($A$1)+1),ROWS($C$1:C1)))," ")}
E2	{=IFERROR(INDEX($A$1:$A$10,MATCH(0,COUNTIF($E$1:E1,$A$1:$A$10),0))," ")}
Press CTRL+SHIFT+ENTER to enter array formulas.