# Identify and delete reciprocal pairs in Excel

#### okiedokie2017

##### New Member
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
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
Hello,

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?

#### okiedokie2017

##### New Member

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
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

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
Hello Peter,

Thank you so so much.

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

#### Peter_SSs

##### MrExcel MVP, Moderator
Cheers, glad it worked so well for you. Thanks for letting us know.

Replies
9
Views
371
Replies
2
Views
78
Replies
2
Views
99
Replies
6
Views
281
Replies
4
Views
136

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.

### Which adblocker are you using?

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

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