Remove Duplicate Data and Keep Unique in a separate sheet

gimran

New Member
Joined
Dec 15, 2011
Messages
33
Dear,

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

1122
1133
1144
1155
1133
1122
1199
1133
1144
1100

<tbody>
</tbody>

Now I want the data to be like below:


<colgroup><col width="72" style="width:54pt"> </colgroup><tbody>
</tbody>
1122
1133
1144
1155
1199
1100

<tbody>
</tbody>

Please help me to do that.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Here is a vba sub:

Open/create a test workbook with a Worksheet which:
  • Has the data in it
  • Is named "Data"


A
1Data
21122
31133
41144
51155
61133
71122
81199
91133
101144
111100

<tbody>
</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

Code:
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
1Data
21122
31133
41144
51155
81199
111100

<tbody>
</tbody>
Data (2)
 
Upvote 0
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
1Data
21122
31133
41144
51155
61133
71122
81199
91133
101144
111100

<tbody>
</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

Code:
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
1Data
21122
31133
41144
51155
81199
111100

<tbody>
</tbody>
Data (2)
 
Upvote 0
Here are two ways (formulas) to extract unique items. Hope this helps.


Excel 2012
ABCDE
111221122Unique
2113311331122
3114411441133
4115511551144
5113311991155
6112211001199
711991100
81133
91144
101100
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top