Identify and delete reciprocal pairs in Excel

okiedokie2017

New Member
Joined
Sep 14, 2017
Messages
14
Hello, let's say I have a 40 unique IDs (A,B,C,....)
I want to make pairs like AA,AB,AC...AZ, BA, BB, BC.... etc.

I believe there would be about 780 pairs (40*39/2).

Is there a formula that I can use to create these pairs automatically? Or do I just need to create a matrix and use CONCATENATE?

However, now I want to identify the reciprocal pairs (AB vs BA, ZE vs EZ....) and eliminate half of it as I consider AB and BA are duplicates.

How do I do since Excel doesnt recognize them as duplicates and delete them?

Many thanks
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,854
Office Version
  1. 365
Platform
  1. Windows
1. Are all your original 40 unique IDs single characters or might some of them be 2 or more characters long?

2. Are you looking for a
- formula/manual method
- macro
- either?
 

okiedokie2017

New Member
Joined
Sep 14, 2017
Messages
14
Hello,

Thank you for your reply.

I am looking for excel formula if thats possible. I am trying to create a spreadsheet easy for my coworkers to use.
Or is Excel VBA is only solution?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,854
Office Version
  1. 365
Platform
  1. Windows
I did ask two questions. ;)
 

okiedokie2017

New Member
Joined
Sep 14, 2017
Messages
14

ADVERTISEMENT

Hi,

Apologies for missing out your first question.

Yes, it would be multiple characters. For example :"Sydney", "Dubai", "Tokyo"... etc. And i need to create pairs like, Sydney-Tokyo, Sydney-Dubai etc etc.

How do i:

1st) Create the pairs, automatically. ( I assume I can just put the IDs vertically and horizontally and use CONCATENATE to create a matrix).

2nd) Identify reciprocal pairs as duplicates and hence delete the duplicates (Sydney-Tokio and Tokio-Sydney are the same)
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
An approach without formulas or code is to use a query (table). It is good for large datasets.

I'll assume the source data is a table with header 'Places' and simple (non-dynamic) named range 'tblNames'

save the file and make the query defined by
Code:
SELECT A.Places & '-' & B.Places AS [Results]
FROM tblNames A, tblNames B
WHERE A.Places < B.Places
ORDER BY 1

This can be set to refresh on file open - a property of the table, without VBA - or every n minutes.

To create, ALT-D-D-N & follow wizard, Excel files, Browse, etc, etc. At the last step take the option to edit in MS Query and via the SQL button replace what you see by the text above. OK to enter & OK to acknowledge any message en route about not being able to graphically represent if you see that. Via the 'open door' icon load to a worksheet. This is also refreshable like a pivot table.

regards,
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,854
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Yes, it would be multiple characters. For example :"Sydney", "Dubai", "Tokyo"... etc. And i need to create pairs like, Sydney-Tokyo, Sydney-Dubai etc etc.

How do i:

1st) Create the pairs, automatically.

2nd) Identify reciprocal pairs as duplicates and hence delete the duplicates
Here is a formula method that doesn't require your 2nd step above as it only creates each pair once.
I have used a smaller sample here so that my screen shot isn't too large. You would need to amend the B2 formula by changing all references to A$11 in my formula to whatever the last cell in your list of cities is before copying the formula down.

Excel Workbook
AB
1CitiesUnique Pairs
2City 1City 1-City 2
3City 2City 1-City 3
4City 3City 1-City 4
5City 4City 1-City 5
6City 5City 1-City 6
7City 6City 1-City 7
8City 7City 1-City 8
9City 8City 1-City 9
10City 9City 1-City 10
11City 10City 2-City 3
12City 2-City 4
13City 2-City 5
14City 2-City 6
15City 2-City 7
16City 2-City 8
17City 2-City 9
18City 2-City 10
19City 3-City 4
20City 3-City 5
21City 3-City 6
22City 3-City 7
23City 3-City 8
24City 3-City 9
25City 3-City 10
26City 4-City 5
27City 4-City 6
28City 4-City 7
29City 4-City 8
30City 4-City 9
31City 4-City 10
32City 5-City 6
33City 5-City 7
34City 5-City 8
35City 5-City 9
36City 5-City 10
37City 6-City 7
38City 6-City 8
39City 6-City 9
40City 6-City 10
41City 7-City 8
42City 7-City 9
43City 7-City 10
44City 8-City 9
45City 8-City 10
46City 9-City 10
47
Pairs
 

okiedokie2017

New Member
Joined
Sep 14, 2017
Messages
14
Hello Peter,

Thank you so so much.

This is beyond genius. So incredible!!! It works like magic. Thank you
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,854
Office Version
  1. 365
Platform
  1. Windows
Cheers, glad it worked so well for you. Thanks for letting us know. :)
 

Forum statistics

Threads
1,148,291
Messages
5,745,897
Members
423,983
Latest member
blackworx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top