Forum Discussion

Sara970's avatar
Sara970
Copper Contributor
Oct 14, 2021
Solved

SOLVED Finding rows that have the same cells, just in a different order? Microsoft 365

Hi, I have a list of names, IDs, and relationships. Many of the relationships are listed twice, as the reciprocal. I'm trying to get rid of the rows that list the same relationship again.

 

Is there a way to find and delete rows that contain the same 7 cells, but the cells are in a different order? I'm trying to find duplicates that exist in a different position.

For example, A2, B2, C2 have the same data as D4, E4, F4. D2, E2, F2 have the same data as A4, B4, C4. G2 is the same as G4.

 

If it helps to simplify, the ID in Column A and D are unique to each person, so is there a way to find and delete the rows that have the same values in Column A and D, just swapped, and the same value in Column G?

 

I've attached a sample of the data I'm working with. Rows 2 and 4 are duplicates, Rows 3 and 5 are duplicates, Rows 6 and 7 are duplicates, etc. I'm trying to delete Row 4, Row 5, Row 7, and so on. 

(Note: The duplicate relationship rows are not always next to each other, and not every single relationship row has a duplicate reciprocal row somewhere else. Also, some names show up multiple times in different relationships.)

 

Thanks in advance!

3 Replies

    • Yea_So's avatar
      Yea_So
      Bronze Contributor

      HansVogelaar  Sara970 

       

      The narrative says:

      Hi, I have a list of names, IDs, and relationships. Many of the relationships are listed twice, as the reciprocal. I'm trying to get rid of the rows that list the same relationship again.

      it got rid of the spouse relationship which was not a repetition:

      how did that solve your problem?

       

      Ideally, the relationship description should reflect the "Subscriber" if not obvious like the Employer/Employee relationship is obvious.

       

       

       

       

      cheers

    • Sara970's avatar
      Sara970
      Copper Contributor
      I'm amazed. Thank you so so much!

Resources