cell pairs and reversed cell pairs

josh83

New Member
Joined
Aug 27, 2014
Messages
5
Hi

My data denotes simple relationships between entities. I have taken it from a matrix and ended up with a table where particular relationships are duplicated, back to front. I want to delete these duplications so I am left with only one row for each relationship pair (order is irrelevant as relationships are bi-directional).

Column AColumnB
Entity 1Entity 2
Entity 2Entity 1

<tbody>
</tbody>

edit - I should mention that the entity numbers are not sequential - I.e. there are lots of missing numbers. some entities have multiple relationships, e.g. entity 1 is also paired with entity 3, etc.

any suggestions appreciated, have searched high and low. :confused:
 
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
A quick and dirty way: In an unused column enter
HTML:
=IF(A2<B2,A2&"|"&B2,B2&"|"&A2)
Copy and paste values, then eliminate duplicates (in 2003 used advanced filter with copy to another location and unique records - in later versions on thedata ribbon choose remove duplicates). Then separate them using text to columns<b2,a2&"|"&b2,b2&"|"&a2) and="" copy="" down.="" paste="" values,="" then="" eliminate="" duplicates="" (in="" 2003="" use="" advanced="" filter,="" to="" another="" location,="" unique="" records="" only="" -="" in="" later="" versions="" on="" the="" data="" ribbon="" choose="" duplicates).="" separate="" them="" using="" text="" columns.<="" html=""></b2,a2&"|"&b2,b2&"|"&a2)>
 
Last edited:
Upvote 0

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
Formula for above is
Code:
=IF(A2 < B2,A2&"|"&B2,B2&"|"&A2)
without the spaces
 
Upvote 0

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
The if statement just insures that "duplicate pairs" are created so that the smallest one is left of the pipe. The pipe is just a delimiter that is unlikely to be part of any entity name and can then be used to separate the pairs into different cells easily with text to columns. Delimiters are also handy when concatenating to insure that non-duplicates do not appear to be duplicates. For example if you were concatenating first and last names without a delimiter TAMIE RIXON ans TAMI ERIXON would appear to be the same.
 
Upvote 0

josh83

New Member
Joined
Aug 27, 2014
Messages
5
I see... so we're just reordering them based on size with IF logic.

Thanks so much for this - you got me out of a real pickle! :biggrin:
 
Upvote 0

josh83

New Member
Joined
Aug 27, 2014
Messages
5
Another question, if I may - let me know if this warrants a new thread but I think it's an extension of this issue.

I have now removed duplicates of the type:


Column AColumn B
Entity 1Entity 2
<strike>Entity 2</strike><strike>Entity 1</strike>

<colgroup><col><col></colgroup><tbody>
</tbody>


Because the data holds information about multiple relationships for the same entity, what I now need to do is sort the pairs so that all iterations of a particular entity fall in the same column (again, it is arbitrary which column).

So for the example below I'd want all iterations of Entity 1 in the same column.


Column AColumn B
Entity1Entity 2
Entity 3Entity 1

<colgroup><col><col></colgroup><tbody>
</tbody>

The Entity data is in the format ZZ*1* - 2-3 text characters followed by number characters.
 
Upvote 0

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
Glad the solution worked for you. Your second request is more difficult and may not even be possible.
What if you had the following pairs 1 & 4, 1 & 5, 4 & 5. The 3rd pair can not meet your requirement
 
Upvote 0

Forum statistics

Threads
1,190,558
Messages
5,981,686
Members
439,729
Latest member
purna

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