Removing duplicates rows

[Gcsingh90]

Data

Tier Category	Name
Tier 1	Tom
Tier 1	Tom
Tier 1	Harry
Tier 1	Rafa
Tier 1	Daniel
Tier 2	Tom
Tier 2	Harry
Tier 2	Harry
Tier 2	CAN ATALAY
Tier 2	HANN-WEN SHEU
Tier 3	Oktar
Tier 3	Miria
Tier 3	Harry
Tier 3	CAN ATALAY
Tier 3	Tom
Tier 3	Rafa
Tier 3	Oktar

Required Output
Tier Category	Name
Tier 1	Tom
Tier 1	Tom
Tier 1	Harry
Tier 1	Rafa
Tier 1	Daniel
Tier 2	Tom
Tier 2	Harry
Tier 2	Harry
Tier 2	CAN ATALAY
Tier 2	HANN-WEN SHEU
Tier 3	Oktar
Tier 3	Miria
Tier 3	Oktar

For simplification just using 2 columns from the whole table, column one has Tier category and Column 2 has corresponding names. Trying to remove duplicates From the table for Tier C, for the names which are present in Tier 1 or Tier 2.

Tier 1,Tier 2 , Tier 3 can have duplicates. But a name present in Tier 2 and Tier 1 should not repeat in Tier 3.

Trying to automate this when ever new data get pasted in this table. Automatically it should remove Tier 1 and Tier 2 names which repeated for only Tier 3 category and return the distinct names for Tier 3. I tried the following. Need to complete the formula.

IF(COUNTIFS($B$2:B2,B2,$A$2:A2,"Tier A")>=1,"Keep","Remove")

IF(COUNTIFS($B$2:B100,B100,$A$2:A100,"Tier B")>=1,"Keep","Remove")

Need to solve this with the help of formula only. Any help is appreciated.

 

[Scott Huish]

Why do you have two Tier1, Tom values in your expected results?

 

[Gcsingh90]

Tier 1 has Tom and Tier 2 has Harry if you notice as Tier 1 and Tier 2 can have duplicate names/rows i.e., no need to remove the rows we can keep the exact for tier 1 and 2. But specifically for tier C, I need to consider only unique names it can also have duplicate names present in tier c itself thats why Tier 3 has Oktar, but cannot have names which are already occurred in Tier 1 or 2 hence all other names removed. Hope this helps.

 

[Gcsingh90]

I am not sure if my approach was correct. I was intending to check the names in Tier 1 and Tier 2 from top to bottom and compare with Tier 3 names, where Tier 3 will exclude the names which can be found in Tier 1 and 2 and return the unique names present in Tier 3

 

[dermie_72]

Why do your formulas have Tier A and Tier B, but in the table, they're labeled Tier 1 and Tier 2?
The required outcome does not remove duplicates, so your heading is confusing.
Can the data be sorted? If so, If Tier 3 items should not be in Tier 1 or 2, sort the data and the countif the names in tier 3 appear above them.

 

[Gcsingh90]

Sorry, Tier A = Tier 1 and Tier B = Tier 2. Correct, the heading is confusing I just want to remove the names in Tier 3 which are repeated in Tier 1 or Tier 2. I doubt the data can be sorted as data going to get copy and pasted every time and it should automatically remove names for Tier 3 category which got repeated in Tier 1 or 2. Hope this helps.

 

[dermie_72]

You could use a formula to identify if it's a Tier 3 and if so, countifs to see if the name appears in a different Tier, flag it to delete then deleting it.

=IF(A2<>"Tier 3","",IF(COUNTIFS(B:B,B2,A:A,"<>Tier 3")<>0,"Delete",""))

Assuming the Tier is in Column A and the name is in Column B

 

[Gcsingh90]

I will try thanks and confirm