Hi, I want to remove duplicate data and it is dependent on cell in different column.

[akr1903]

sample sheet.xlsx
	A	B	C	D
1	Country	Email	Application Status	Initiated On
2	United Kingdom	qqqqqqqqqqq@gmail.com	Submitted to University	07-23-2021 16:07
3	United Kingdom	qqqqqqqqqqq@gmail.com	New	07-23-2021 16:07
4	United Kingdom	qqqqqqqqqqq@gmail.com	Offer Received (Unconditional)	07-23-2021 16:07
5	United Kingdom	qqqqqqqqqqq@gmail.com	Submitted to 3rd Party	04-15-2021 23:04
6	United Kingdom	qqqqqqqqqqq@gmail.com	Deposit Made	04-15-2021 23:04
7	United Kingdom	qqqqqqqqqqq@gmail.com	Submitted to University	04-15-2021 23:04
8	United Kingdom	eeeeeeeeee@gmail.com	Submitted to University	05-18-2021 15:05
9	United Kingdom	eeeeeeeeee@gmail.com	Application Rejected	05-18-2021 15:05
10	United Kingdom	rrrrrrrrrrrrr@gmail.com	Offer Received (Unconditional)	05-20-2021 13:05
11	United Kingdom	rrrrrrrrrrrrr@gmail.com	Info required post submission	04-19-2021 17:04
12	United Kingdom	rrrrrrrrrrrrr@gmail.com	Offer Received (Conditional)	04-19-2021 17:04
13	United Kingdom	rrrrrrrrrrrrr@gmail.com	Info required post submission	04-19-2021 17:04
14	United Kingdom	aaaaaaaaaa@gmail.com	New	08-20-2021 13:08
15	United Kingdom	aaaaaaaaaa@gmail.com	Submitted to University	06-05-2021 11:06
16	United Kingdom	aaaaaaaaaa@gmail.com	Application Rejected	05-21-2021 19:05
17	United Kingdom	aaaaaaaaaa@gmail.com	In Progress	05-21-2021 19:05
18	United Kingdom	aaaaaaaaaa@gmail.com	Deposit Made	05-21-2021 19:05
19	United Kingdom	aaaaaaaaaa@gmail.com	Submitted to University	05-21-2021 19:05
20	United Kingdom	aaaaaaaaaa@gmail.com	Submitted to University	05-21-2021 19:05
21	United Kingdom	bbbbbbbbbbbb@gmail.com	Offer Received (Conditional)	05-18-2021 13:05
22	United Kingdom	bbbbbbbbbbbb@gmail.com	Submitted to University	05-18-2021 13:05
23	United Kingdom	ccccccccccccccc@gmail.com	New	08-11-2021 13:08
24	United Kingdom	ccccccccccccccc@gmail.com	Submitted to University	08-11-2021 13:08
25	United Kingdom	ccccccccccccccc@gmail.com	Info required post submission	04-10-2021 15:04
26	United Kingdom	ccccccccccccccc@gmail.com	Submitted to University	04-10-2021 15:04
27	United Kingdom	ccccccccccccccc@gmail.com	Offer Received (Conditional)	04-10-2021 15:04
28	United Kingdom	ddddddddddddd@gmail.com	Offer Received (Unconditional)	07-28-2021 13:07
29	United Kingdom	ddddddddddddd@gmail.com	Deposit Made	06-12-2021 12:06
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:B29	Cell Value	duplicates	text	NO
B1:B29	Cell Value	unique values	text	NO
B1:B29	Other Type	Color scale		NO

 

[akr1903]

The places where deposit is made I want all the data in that row, and delete rest rows with similar email address. Eg C6 has deposit made, I want the data in row 6 and rest rows that is row 7, 5,4,3,2 to be removed. In places where no deposit is made I want all the data. Eg for email address eeeeeeeeee@gmail.com, rrrrrrrrrrrrr@gmail.com, bbbbbbbbbbbb@gmail.com and ccccccccccccccc@gmail.com no deposit is made, so I want all the data for those emails.

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this macro with a copy of your workbook.

Sub RemoveDepositDupes()
  With Range("C2", Range("C" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace(Replace("if(@<>""Deposit Made"",if(countifs(%,%,@,""Deposit Made""),""#N/A"",@),@)", "@", .Address), "%", .Offset(, -1).Address))
    On Error Resume Next
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
  End With
End Sub

 

[JEC]

Nice one! Can be done with names as well, which is easier to read in my opinion.

Sub RemoveDepositDupes2()
  With Range("C2", Range("C" & Rows.Count).End(xlUp))
    .Name = "b"
    .Offset(, -1).Name = "a"
    .Value = Evaluate("if(b<>""Deposit Made"",if(countifs(a,a,b,""Deposit Made""),""#N/A"",b),b)")
    
    On Error Resume Next
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
  End With
End Sub