Identify and delete reciprocal pairs in Excel

[okiedokie2017]

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

 

[Peter_SSs]

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]

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]

I did ask two questions.

 

[okiedokie2017]

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]

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

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]

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
	A	B
1	Cities	Unique Pairs
2	City 1	City 1-City 2
3	City 2	City 1-City 3
4	City 3	City 1-City 4
5	City 4	City 1-City 5
6	City 5	City 1-City 6
7	City 6	City 1-City 7
8	City 7	City 1-City 8
9	City 8	City 1-City 9
10	City 9	City 1-City 10
11	City 10	City 2-City 3
12		City 2-City 4
13		City 2-City 5
14		City 2-City 6
15		City 2-City 7
16		City 2-City 8
17		City 2-City 9
18		City 2-City 10
19		City 3-City 4
20		City 3-City 5
21		City 3-City 6
22		City 3-City 7
23		City 3-City 8
24		City 3-City 9
25		City 3-City 10
26		City 4-City 5
27		City 4-City 6
28		City 4-City 7
29		City 4-City 8
30		City 4-City 9
31		City 4-City 10
32		City 5-City 6
33		City 5-City 7
34		City 5-City 8
35		City 5-City 9
36		City 5-City 10
37		City 6-City 7
38		City 6-City 8
39		City 6-City 9
40		City 6-City 10
41		City 7-City 8
42		City 7-City 9
43		City 7-City 10
44		City 8-City 9
45		City 8-City 10
46		City 9-City 10
47
Pairs

 

[okiedokie2017]

Hello Peter,

Thank you so so much.

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

 

[Peter_SSs]

Cheers, glad it worked so well for you. Thanks for letting us know.