Deleting Rows Containing Reversed Names

[AndyPaff]

I have a worksheet in which one column of data contains pairs of competitor names which also appear reversed in the same column. E.g.


Alpha v Bravo
Alpha v Delta
Alpha v Echo
Bravo v Tango
Foxtrot v Delta
Delta v Alpha
Echo v Alpha
etc.


I require only one instance (row) of each pair of names (order of pair doesn't matter) and I would like to delete the surplus rows. How can I do this?


One way that springs to mind is to create an adjacent row with the names reversed (no problem). Then loop though this column one row at a time top to bottom looking for a match in the first column (nested loop of row +1 to bottom row) and deleting the row if matched. Would this work and what is the VBA I would need?

 

[Peter_SSs]

Perhaps you are definitely looking for a vba solution and that can certainly be done, but here is a fairly straight-forward manual way roughly based on the idea you were thinking of.

1. Copy this formula down column B
2. Use AutoFilter on column B & remove the tick from #N/A leaving just the numbered rows
3. Delete the visible rows below the headers.
4. Remove the AutoFilter

Excel Workbook
	A	B
1	Data	Check
2	Alpha v Bravo	#N/A
3	Alpha v Delta	#N/A
4	Alpha v Echo	#N/A
5	Bravo v Tango	#N/A
6	Foxtrot v Delta	#N/A
7	Delta v Alpha	3
8	Echo v Alpha	4
Check matches

 

[AndyPaff]

Thank you for this Peter.


VBA isn't essential. Your solution takes seconds and can be automated using a macro I already have to insert formulas in other columns.

Vey much appreciated.


Andrew

 

[Peter_SSs]

Thank you for this Peter.

You are very welcome.

VBA isn't essential. Your solution takes seconds and can be automated using a macro I already have to insert formulas in other columns.

Sounds like the way to go then.