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

Mandalyn

New Member
Joined
Jan 22, 2014
Messages
1
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.


Project with Dependency</SPAN>
Project it has dependency with</SPAN>
Type of dependency</SPAN>
Relationship</SPAN>
Logility</SPAN>
Ergo</SPAN>
Int</SPAN>
My project is dependent on this project</SPAN>
Logility</SPAN>
Ergo</SPAN>
SAP</SPAN>
Both projects have work that can be shared</SPAN>
Logility</SPAN>
Mercury</SPAN>
SAP</SPAN>
The other project is dependent on my project</SPAN>
Ergo</SPAN>
Logility</SPAN>
Int</SPAN>
The other project is dependent on my project</SPAN>
Ergo</SPAN>
Logility</SPAN>
SAP</SPAN>
Both projects have work that can be shared</SPAN>
Mercury</SPAN>
Logility</SPAN>
SAP</SPAN>
My project is dependent on this project</SPAN>
Mercury</SPAN>
Ergo</SPAN>
BI</SPAN>
My project is dependent on this project</SPAN>

<TBODY>
</TBODY>


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:

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>
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>
If the Relationship shows that both projects are shared, it should keep both.</SPAN>
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>
AND if the primary project is in the second column, they should be swapped.</SPAN>
A new table should show the results and reword the relationship to say only dependent or shared.</SPAN>
It should sort A-Z by column 1, then column 2.</SPAN>
The result should be:</SPAN>

<TBODY>
</TBODY>

Primary Project</SPAN>
Dependent Project</SPAN>
Type of dependency</SPAN>
Relationship</SPAN>
Ergo</SPAN>
Logility</SPAN>
Int</SPAN>
Dependent</SPAN>
Ergo</SPAN>
Logility</SPAN>
SAP</SPAN>
Shared</SPAN>
Ergo (red)</SPAN>
Mercury</SPAN>
BI</SPAN>
Dependent</SPAN>
Logility</SPAN>
Mercury</SPAN>
SAP</SPAN>
Dependent</SPAN>

<TBODY>
</TBODY>


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?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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