Check duplicates in one column based on duplicates in another column and assign a value based on a condition.

[floydschoice]

I have a few columns and couple thousand rows.

Name	Location	Status	Action
Jake	Germany	out	Retain
Jake	Ethiopia	out	Delete
Jake	Germany	in	Retain
Jake	Germany	out	Retain
Jake	Ethiopia	out	Delete

In the above table, if Jake is "in" (3rd column) "Germany" (just an example of country) even once, then there should be "Retain" in the 4th column for each of the times Jake has been to Germany "in" and "out".
However, if Jake has been to Ethiopia and has no "in" entry for any of his travels to Ethiopia, then there should be a "Delete" in the 4th column for each of the times Jake has been to Ethiopia. I am relatively new to VBA and I have no idea how to make this work. Can someone help?

PS. The name and the countries mentioned are only example. There could n number of different names and countries in the sheet.

 

[Fluff]

Hi & welcome to MrExcel.
You can do that very simply with a formula like this

+Fluff.xlsm
	A	B	C	D
1	Name	Location	Status	Action
2	Jake	Germany	out	Retain
3	Jake	Ethiopia	out	Delete
4	Jake	Germany	in	Retain
5	Jake	Germany	out	Retain
6	Jake	Ethiopia	out	Delete
7	Bob	UK	in	Retain
8	Bob	France	out	Delete
9	Bob	Germany	out	Delete
10	Bob	Ethiopia	in	Retain
11	Bob	Germany	out	Delete
12	Bob	Ethiopia	out	Retain
13	Bob	UK	out	Retain
Report

Cell Formulas
Range	Formula
D2:D13	D2	=IF(COUNTIFS(A:A,A2,B:B,B2,C:C,"In")>0,"Retain","Delete")

 

[floydschoice]

Hi Fluff, this worked perfectly well. I did have a problem embedding this formula in the VBA code but I worked it out.

Hi & welcome to MrExcel.
You can do that very simply with a formula like this

+Fluff.xlsm
	A	B	C	D
1	Name	Location	Status	Action
2	Jake	Germany	out	Retain
3	Jake	Ethiopia	out	Delete
4	Jake	Germany	in	Retain
5	Jake	Germany	out	Retain
6	Jake	Ethiopia	out	Delete
7	Bob	UK	in	Retain
8	Bob	France	out	Delete
9	Bob	Germany	out	Delete
10	Bob	Ethiopia	in	Retain
11	Bob	Germany	out	Delete
12	Bob	Ethiopia	out	Retain
13	Bob	UK	out	Retain
Report

Cell Formulas
Range	Formula
D2:D13	D2	=IF(COUNTIFS(A:A,A2,B:B,B2,C:C,"In")>0,"Retain","Delete")

This worked perfectly well although initially I had problems embedding this in my VBA code but I figured it out. Thanks a lot for your help.

 

[Fluff]

Glad it helped & thanks for the feedback