SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2847688%22%20slang%3D%22en-US%22%3EFinding%20rows%20that%20have%20the%20same%20cells%2C%20just%20in%20a%20different%20order%3F%20Microsoft%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2847688%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20a%20list%20of%20names%2C%20IDs%2C%20and%20relationships.%20Many%20of%20the%20relationships%20are%20listed%20twice%2C%20as%20the%20reciprocal.%20I'm%20trying%20to%20get%20rid%20of%20the%20rows%20that%20list%20the%20same%20relationship%20again.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIs%20there%20a%20way%20to%20find%20and%20delete%20rows%20that%20contain%20the%20same%207%20cells%2C%20but%20the%20cells%20are%20in%20a%20different%20order%3F%20I'm%20trying%20to%20find%20duplicates%20that%20exist%20in%20a%20different%20position.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EFor%20example%2C%20A2%2C%20B2%2C%20C2%20have%20the%20same%20data%20as%20D4%2C%20E4%2C%20F4.%20D2%2C%20E2%2C%20F2%20have%20the%20same%20data%20as%20A4%2C%20B4%2C%20C4.%20G2%20is%20the%20same%20as%20G4.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20it%20helps%20to%20simplify%2C%20the%20ID%20in%20Column%20A%20and%20D%20are%20unique%20to%20each%20person%2C%20so%20is%20there%20a%20way%20to%20find%20and%20delete%20the%20rows%20that%20have%20the%20same%20values%20in%20Column%20A%20and%20D%2C%20just%20swapped%2C%20and%20the%20same%20value%20in%20Column%20G%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20a%20sample%20of%20the%20data%20I'm%20working%20with.%20Rows%202%20and%204%20are%20duplicates%2C%20Rows%203%20and%205%20are%20duplicates%2C%20Rows%206%20and%207%20are%20duplicates%2C%20etc.%20I'm%20trying%20to%20delete%20Row%204%2C%20Row%205%2C%20Row%207%2C%20and%20so%20on.%26nbsp%3B%3C%2FP%3E%3CP%3E(Note%3A%20The%20duplicate%20relationship%20rows%20are%20not%20always%20next%20to%20each%20other%2C%20and%20not%20every%20single%20relationship%20row%20has%20a%20duplicate%20reciprocal%20row%20somewhere%20else.%20Also%2C%20some%20names%20show%20up%20multiple%20times%20in%20different%20relationships.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2847688%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2847769%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20rows%20that%20have%20the%20same%20cells%2C%20just%20in%20a%20different%20order%3F%20Microsoft%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2847769%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1185781%22%20target%3D%22_blank%22%3E%40Sara970%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%20I%20added%20a%20formula%20in%20column%20I%20that%20specifies%20the%20rows%20that%20can%20be%20deleted.%3C%2FP%3E%0A%3CP%3EFilter%20column%20I%20for%20TRUE%2C%20delete%20all%20rows%20from%20row%202%20down%2C%20then%20remove%20the%20filter.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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
best response confirmed by Sara970 (New Contributor)
Solution

@Sara970 

See the attached version. I added a formula in column I that specifies the rows that can be deleted.

Filter column I for TRUE, delete all rows from row 2 down, then remove the filter.

I'm amazed. Thank you so so much!

@Hans Vogelaar  @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:

Yea_So_0-1634276550198.png

how did that solve your problem?

 

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

 

Yea_So_1-1634277766370.png

 

 

 

cheers