Remove duplicates

[aghaffar82]

Hello Experts

I am wanting to get rid of duplicate entries that are scattered all over the sheet and intend to keep the left-most as a unique value. so far example if there is a name appearing 3 times in the whole data set, I would like to keep the name that appears in the left-most column.

A small sample is attached for your kind reference and expert look: in the example below, I would like to delete the cell/content B13, D17 & E10.

Sample 5000 Rows-v2.xlsx
	A	B	C	D	E
1	parrot	christy	tara	hot	fab
2	jp	josh	fan	pat	adi
3	ag	cham	gem	dan	asdq
4	Singh	lav	besty	zu	rathore
5	benjamin	ana	far	ange	begood
6	jo	luca	ismil	ingrid	hx
7	joseph	kalr	derr	soy	val
8	ricky	mare	tan	ivan	alfonso
9	patty	vlo	servis	zully	marey
10	euro	qtm	udi	itson	ana
11	Ali	night	mosses	andre	midnight
12	matt	day	gaby	pam	keo
13	danny	jo	jon	saif	char
14	nina	farrukh	eve	dery	mouad
15	prism	sam	hach	tania	khalid
16	tiger	barry	floot	blush	wrath
17	cat	dia	holk	sam	divine
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:E17	Cell Value	duplicates	text	NO

 

[jasonb75]

What do you want to do with the remaining data once the duplicates have been deleted?

There are 5 possible outcomes that come to mind without even thinking about it.

 

[aghaffar82]

What do you want to do with the remaining data once the duplicates have been deleted?

There are 5 possible outcomes that come to mind without even thinking about it.

Thank you for your response
I need to upload it to our marketing software. Every column actually is a category and the left most is the most important and target customer can appear more than once and duplicates are to be removed from the right columns.
Hope that makes sense.

 

[aghaffar82]

Thank you for your response
I need to upload it to our marketing software. Every column actually is a category and the left most is the most important and target customer can appear more than once and duplicates are to be removed from the right columns.
Hope that makes sense.

I meant a customer cannot appear more than once in the entire sheet.

 

[jasonb75]

Clear the name and leave a blank cell where the duplicate was?
Delete the cell and move the other names in the same column up, leaving the blanks at the bottom?
Delete the cell and move the other names in the row left, leaving the blanks on the right?
Delete the cell and move the other names in the same column up, move the first entry from the next column to fill the empty cell at the bottom, leaving any empty cells at the bottom of the final column?
Delete the cell and move the other names in the same row left, move the first entry from the next row to fill the empty cell on the right, leaving any empty cells at the end of the final row?

 

[aghaffar82]

Sir this would be great: "Delete the cell and move the other names in the same column up, leaving the blanks at the bottom".

 

[aghaffar82]

Or perhaps, deleting the content of the cells would be faster as there would be around 30 columns and some of them would have about a million rows. Thank you

 

[Fluff]

Cross posted Delete duplicating entries across the worksheet

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

 

[aghaffar82]

Cross posted Delete duplicating entries across the worksheet

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

I am really sorry for doing so and as soon as I get an answer here or on the other site, I will update both the threads. Thank you

 

[Dossfm0q]

Public Sub RmvDbl()

Dim Rng As Range, MyRng As Range
Dim TXT As String, T As String
Dim TxTArr As Variant
Set MyRng = Range("A1").CurrentRegion
TXT = ""
    For C = 1 To MyRng.Columns.Count
        For Each Rng In MyRng.Columns(C).Cells
        T = " " & Rng.Value & " "
            If InStr(1, TXT, T, vbTextCompare) = 0 Then
            TXT = TXT & IIf(TXT <> "", ",", "") & T
            Else
            End If
        Next
    Next
    
    TxTArr = Split(TXT, ",")
    
   srw = MyRng.Row + MyRng.Rows.Count + 2
   Scl = MyRng.Column
   N = MyRng.Rows.Count
   For i = LBound(TxTArr) To UBound(TxTArr)
    RW = (((i + 1) - 1) Mod N) + 1
    cl = WorksheetFunction.Ceiling((i + 1), N) / N
    Cells(srw + RW, Scl + cl - 1) = Trim(TxTArr(i))
  
   Next
End Sub

Test.xlsm
	A	B	C	D	E
1	parrot	christy	tara	hot	fab
2	jp	josh	fan	pat	adi
3	ag	cham	gem	dan	asdq
4	Singh	lav	besty	zu	rathore
5	benjamin	ana	far	ange	begood
6	jo	luca	ismil	ingrid	hx
7	joseph	kalr	derr	soy	val
8	ricky	mare	tan	ivan	alfonso
9	patty	vlo	servis	zully	marey
10	euro	qtm	udi	itson	ana
11	Ali	night	mosses	andre	midnight
12	matt	day	gaby	pam	keo
13	danny	jo	jon	saif	char
14	nina	farrukh	eve	dery	mouad
15	prism	sam	hach	tania	khalid
16	tiger	barry	floot	blush	wrath
17	cat	dia	holk	sam	divine
18
19
20
21	parrot	christy	fan	pat	asdq
22	jp	josh	gem	dan	rathore
23	ag	cham	besty	zu	begood
24	Singh	lav	far	ange	hx
25	benjamin	ana	ismil	ingrid	val
26	jo	luca	derr	soy	alfonso
27	joseph	kalr	tan	ivan	marey
28	ricky	mare	servis	zully	midnight
29	patty	vlo	udi	itson	keo
30	euro	qtm	mosses	andre	char
31	Ali	night	gaby	pam	mouad
32	matt	day	jon	saif	khalid
33	danny	farrukh	eve	dery	wrath
34	nina	sam	hach	tania	divine
35	prism	barry	floot	blush
36	tiger	dia	holk	fab
37	cat	tara	hot	adi
Sheet1