How to use index to find the duplicate records then copy data

[mhghg]

Hello guys,
I have an excel sheet with some customers having contact information for mother and father. Some do not have. With the customer having both of contacts they will be the duplicated records with exact data for both of the rows except the column [parentcontact2] is NULL for the original row and the duplicated row having data.
I want to use index formula to check if it the same studname then Copy the parentcontact2
data in the duplicated row and paste it to the parentcontact2 of the original row. Then remove the duplicated row.

How to do it, help please!!!
Macy

 

[Nebu]

Hi:

Find the attached.

The yellow column contains the formula, before applying the formula insert a row after Parent contact2 , write the formula in the newly inserted column, Copy and pastespecial as values. Use the remove duplicate function in excel to remove the duplicates and delete the original Parentcontact2 column to achieve what you are looking for. I have displayed the output on Sheet1 (2).

Thanks

 

[mhghg]

Hi Nebu,
Thank you for your quick respond. I realize there is two issues when applying the formula.
1. Some of the records having Parentcontact1 but not parentContact2 on the same row so we apply your formula
2. Some of the records goes the other way around. It means having ParentContact2 on the main records but parentContact1 on the second row.
3. When applying the formual for some reasons. It randomly copies the parentcontact2 and paste it to the wrong records a few times
4. I attach again the new file so you can help me again, but I wonder if it is easier with VBA?
5. I try to study your formula, but not understand the meaning of using aggregrate funnction and why do we have to divide for [Pastoral]?

 

[Nebu]

Hi:

Find the attached. Click the remove duplicates button for the output.

Thanks

 

[mhghg]

Hi Nebu,
You saved my life
Macy