Macro to show ONLY Duplicates on multiple sheets, and then calculate a field based on.....

STATUS_REPORT_2_AND_4_9_17.xlsx

Hello All!

I have attached a sheet with example data in some of the formats I might get it in. For this macro, I don't want the headings to have to match (or even the column data) necessarily. The point is to automate what I need to do.

Let me preface by saying that I am aware you can locate duplicates in excel many of ways, by using Conditional Formatting or any number of IF Statements but what I need to do does not seem to have an easy solution.

In both sheets of the attached workbook you will notice that they all contain a column with a Loan #, a Task #, and a Distribution Date (Dist Date). These are THE ONLY THREE columns I am looking at when I need to analyze the duplicates only and then use the DISTR_DATE to determine the days difference.

A couple things to note before looking at how to approach this--
1) I ONLY want to display the loan number (rows and all) that contain duplicates (have 2 or more of the same exact number)
--> The other data is now irrelevant to what I am trying to report for, it can be deleted if it would make things easier.
2) I want it sorted by Loan Number (Smallest to Largest) and Task number (Smallest to Largest) to ensure that their are no duplicates amongst duplicates (same loan number and same task number in two separate rows would indicate this. There SHOULD never be two of the same task number's for one individual loan.
3) For only those loans that are duplicates (Have 2 or more**important, sometimes there are 7-8** in the sample data) I want to start with the lowest task number and look at the number of days difference between the DISTRIBUTION DATES.

So if Loan 1234 Task 1 Has a Distribution Date of 8/17/2012
and Loan 1234 Task 2 Has a Distribution Date of 9/02/2012 , I want to show (in a new column) the number of days difference between the two tasks/dates (for X while x is the number of loans that have the same number)

The only date column I am concerned with (AT THE MOMENT THAT IS/VARIABLE TO CHANGE) is the distribution date.

If you have any questions please feel free to ask. I am sick of having to run this through access and run SQL then bring it back into excel, do CF'ing and then copy and paste a different formula for each loan number (because it does vary based on the # of duplicates).

You guys CONTINUE to be the most resourceful forum on the net, and I applaud that.

I'm stuck here

Dan

No takers yet? Surprising for this forum! I'm sure I will not be left high and dry!

Can someone please give this a shot... There are so many experienced people on this forum and NO ONE can take a stab at it for me?