cell pairs and reversed cell pairs

[josh83]

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 A	ColumnB
Entity 1	Entity 2
Entity 2	Entity 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.

 

[West Man]

A quick and dirty way: In an unused column enter

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

 

[West Man]

Formula for above is

=IF(A2 < B2,A2&"|"&B2,B2&"|"&A2)

without the spaces

 

[josh83]

Formula for above is

=IF(A2 < B2,A2&"|"&B2,B2&"|"&A2)

without the spaces

thanks west man

could you explain the logic of the equation above? in particular the role of the pipe symbols?

THanks again

 

[West Man]

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.

 

[josh83]

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!

 

[josh83]

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 A	Column B
Entity 1	Entity 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 A	Column B
Entity1	Entity 2
Entity 3	Entity 1

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

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

 

[West Man]

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

 

[josh83]

I see what you mean...


Back to the drawing board!