If A, B, & C are the same in any order across any row, and D = "text" then copy to new table.

[Mandalyn]

I have data exported to Excel from Sharepoint showing projects that are dependent on each other, the type of dependency and which is is dependent on which.


[TABLE="width: 784"]
<TBODY>[TR]
[TD]Project with Dependency</SPAN>
[/TD]
[TD]Project it has dependency with</SPAN>
[/TD]
[TD]Type of dependency</SPAN>
[/TD]
[TD]Relationship</SPAN>
[/TD]
[/TR]
[TR]
[TD]Logility</SPAN>
[/TD]
[TD]Ergo</SPAN>
[/TD]
[TD]Int</SPAN>
[/TD]
[TD]My project is dependent on this project</SPAN>
[/TD]
[/TR]
[TR]
[TD]Logility</SPAN>
[/TD]
[TD]Ergo</SPAN>
[/TD]
[TD]SAP</SPAN>
[/TD]
[TD]Both projects have work that can be shared</SPAN>
[/TD]
[/TR]
[TR]
[TD]Logility</SPAN>
[/TD]
[TD]Mercury</SPAN>
[/TD]
[TD]SAP</SPAN>
[/TD]
[TD]The other project is dependent on my project</SPAN>
[/TD]
[/TR]
[TR]
[TD]Ergo</SPAN>
[/TD]
[TD]Logility</SPAN>
[/TD]
[TD]Int</SPAN>
[/TD]
[TD]The other project is dependent on my project</SPAN>
[/TD]
[/TR]
[TR]
[TD]Ergo</SPAN>
[/TD]
[TD]Logility</SPAN>
[/TD]
[TD]SAP</SPAN>
[/TD]
[TD]Both projects have work that can be shared</SPAN>
[/TD]
[/TR]
[TR]
[TD]Mercury</SPAN>
[/TD]
[TD]Logility</SPAN>
[/TD]
[TD]SAP</SPAN>
[/TD]
[TD]My project is dependent on this project</SPAN>
[/TD]
[/TR]
[TR]
[TD]Mercury</SPAN>
[/TD]
[TD]Ergo</SPAN>
[/TD]
[TD]BI</SPAN>
[/TD]
[TD]My project is dependent on this project</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


Can I do an equation that sorts it so that it only shows the primary project in Column A, the dependent project in column B, type of dependency in Column C, and then whether it is shared or dependent in D.

Here's what I'm thinking it will do:

[TABLE="width: 375"]
<TBODY>[TR]
[TD]If column 1 and 2 both contain the same projects and type of dependency in either order AND both say they share work THEN one should be deleted.</SPAN>
[/TD]
[/TR]
[TR]
[TD]If column 1 and 2 both contain the same projects and type of dependency in either order AND they say opposite dependencies, then the one with the secondary project in the first column should be deleted.</SPAN>
[/TD]
[/TR]
[TR]
[TD]If the Relationship shows that both projects are shared, it should keep both.</SPAN>
[/TD]
[/TR]
[TR]
[TD]If none of these scenarios exist for a row (no duplicates) it should be called out in red because the projects should list each other.</SPAN>
[/TD]
[/TR]
[TR]
[TD]AND if the primary project is in the second column, they should be swapped.</SPAN>
[/TD]
[/TR]
[TR]
[TD]A new table should show the results and reword the relationship to say only dependent or shared.</SPAN>
[/TD]
[/TR]
[TR]
[TD]It should sort A-Z by column 1, then column 2.</SPAN>
[/TD]
[/TR]
[TR]
[TD]The result should be:</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="width: 784"]
<TBODY>[TR]
[TD]Primary Project</SPAN>
[/TD]
[TD]Dependent Project</SPAN>
[/TD]
[TD]Type of dependency</SPAN>
[/TD]
[TD]Relationship</SPAN>
[/TD]
[/TR]
[TR]
[TD]Ergo</SPAN>
[/TD]
[TD]Logility</SPAN>
[/TD]
[TD]Int</SPAN>
[/TD]
[TD]Dependent</SPAN>
[/TD]
[/TR]
[TR]
[TD]Ergo</SPAN>
[/TD]
[TD]Logility</SPAN>
[/TD]
[TD]SAP</SPAN>
[/TD]
[TD]Shared</SPAN>
[/TD]
[/TR]
[TR]
[TD]Ergo (red)</SPAN>
[/TD]
[TD]Mercury</SPAN>
[/TD]
[TD]BI</SPAN>
[/TD]
[TD]Dependent</SPAN>
[/TD]
[/TR]
[TR]
[TD]Logility</SPAN>
[/TD]
[TD]Mercury</SPAN>
[/TD]
[TD]SAP</SPAN>
[/TD]
[TD]Dependent</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


I'm getting a headache thinking about it.
Can I make it check to see if A, B, and C contain the same words, and then check to see if D contains specific text, and then copy that entire line to a new table on a new sheet? - as a first step? I don't know how!!! Can you help?