Validate 2 worksheets against 1 and produce deltas.

[Raisedwell]

Ok, here is a challenge as I need to be able to accomplish several things.

I work for a parent company with two subsidiaries. I am trying to validate two worksheets (each contains URLs for each subsidiary) against one worksheet. All have data only in column A of their worksheet.

Two of the worksheets contains web URLs that we discovered during a security scan of company web for URLs that are listed on our web server. The other two in the DNS worksheet and if not create a list of the ones that are not listed for each company.

So basically, I am looking for a macro that would do several things:


1. Basically strip each line of the filename for Company1 and Company2 leaving only the URL path.

Ex.
http://www.fakecompany.com/public/images/yellow_arrow.gif INTO
http://www.fakecompany.com/public/images/

Note: If the URL path does not have a filename, simply leave as is.

Ex.
https://www.abc.fakecompany.com/public/images/ LEAVE AS
https://www.abc.fakecompany.com/public/images/


2. Validate the URL list located in column A of Company1 against the URLs listed in the DNS worksheet.

3. The same for Company2 against the URLs listed in the DNS worksheet.

Finally,

4. Some how be able to identify which URLs from both Company1 and Company2 that are not listed in the DNS worksheet. (Either by creating a new worksheet containing the listed deltas for each company or colorcoding some how so that they could be sorted and separated.)

Ultimately, I am trying to be able to produce a true count of URLs for both Company1 and Company2 as well as list the URLs not found on the DNS worksheet.

Any help anyone can give would simply be a life saver. (I have attached a sample workbook saved in 2003 format for your review.)

Thanks in advance.

[Raisedwell]

Hello? Anyone there?

[Raisedwell]

Hello?????

[Raisedwell]

Hello????

Is anyone out there?