Conditional formatting with containing

**TechnicGeek** · 06-22-2014, 10:08 AM

Hello. I have a list where there's one same domain repeated every entry has different suffix in URL. Example:

2014-06-22 16_32_49-Document1-formatting.jpg

Remove duplicates does not solve filtration problem because it looks on entry as a whole.
What I need is something that would find something common in all these URLs so I can do he
filtering or better yet, Excel will filter out and leave 1 unique entry.

I think I need at least conditional formatting but which looks on something common inside URLs
which in my case is domain itself.

**sktneer** · 06-22-2014, 10:25 AM

Assuming your domain names are in col. A and starts from A1.
If
A1 = http://www.TechnicGeek.com
A2 = http://www.TechnicGeek.com/contact
A3 = http://www.TechnicGeek.com/photos
A4 = http://www.TechnicGeek.com/about
and you want to keep only http://www.TechnicGeek.com.

If this is the case, in B1 place this formula and copy down.

Please Login or Register  to view this content.

So for pure domain names col. B will show True and for domain names with suffixes will show False and then you can filter col. B and delete all the False.

Is this something you can work with?

**TechnicGeek** · 06-23-2014, 01:34 AM

Originally Posted by sktneer

Is this something you can work with?

Thanks for replying but I had to mention that along with above mentioned domain, I have domains too.
I just tried this code with a list of different domains and all have FALSE near to them.
Apparently the code needs to be modified and I would modify it had I knew how it functions.

**sktneer** · 06-23-2014, 01:49 AM

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**TechnicGeek** · 06-23-2014, 05:34 AM

Hi sktneer

Attached is Excel file example with 2 work sheets.

work-sheet-conditional.xlsx

Work sheet "Conditional" is what I want to accomplish using formula you have provided.
Work sheet "Filtering" (less preferred option) is what I am trying to accomplish with
filtering leaving only 1 unique URL.

With work sheet "Conditional" I will be able to see and decide which URL to keep. The reason
is because I am not looking for domains which all point me to home page. URLs are preferred more
because I get to pick which page suits my purpose better.

For example: domain.com is less preferred than domain.com/about-us/ because my intention is to
find out more about company.

Thanks

**sktneer** · 06-23-2014, 02:02 PM

Based on what you have in col. A (Before) on Conditional sheet is very difficult what you want to get in col. F because I don't find any common logic behind it.
Would you please clarify that why F3 is True while F11 is False? Because that one is really creating some doubt in logic.

**newdoverman** · 06-23-2014, 03:48 PM

Maybe something like this will get you started: Enter in F2 and copy down. This will give the TRUE/FALSE

Formula:

Please Login or Register  to view this content.

If you just want the URL returned then this will do it:

Formula:

Please Login or Register  to view this content.

Terms can be added to the array constant in the SEARCH function in the formula as required.

	D	E	F	G	H	I	J	K
1		After	Condition
2		http://dailyinfographic.com/contact	TRUE		http://dailyinfographic.com/contact
3		http://www.easel.ly/	TRUE		http://www.easel.ly/
4		http://www.loveinfographics.com/submit-infographics	FALSE
5		http://www.coolinfographics.com/contact/	FALSE
6		http://infosthetics.com/information_aesthetics_suggest.html	FALSE
7		http://iinfographic.com/submit-brilliance/	FALSE
8		http://www.dailyinfographic.com/about	TRUE		http://www.dailyinfographic.com/about
9		http://easel.ly/	TRUE		http://easel.ly/
10		http://chartporn.org/about/	FALSE
11		http://visual.ly/	FALSE
12		http://www.infographicsarchive.com/infographic-submission-free/	FALSE
13		www.dailyinfographic.com	TRUE		www.dailyinfographic.com

**sktneer** · 06-23-2014, 04:20 PM

@newdoverman
That is amazing. I couldn't find that logic. Hope he has no other domain names.

**newdoverman** · 06-23-2014, 04:54 PM

There isn't any indication of what the real data is actually like. If there are a lot of domains, a different approach may become necessary. It took a bit of "starring" at the URL's until the pattern became visible to me. The OP mentions filtering and that bothers me with this solution.

If the OP wants to filter on "dailyinfographic", "easel" and other terms, something like this or some other formula might be necessary:

Formula:

Please Login or Register  to view this content.

**TechnicGeek** · 06-25-2014, 03:45 AM

Thanks guys. What would be the formula where there is no specific domain to include due to my list being big and having many different domains?

("dailyinfographic",A2,1)>0,"dailyinfographic")

Above would only help with specific domains but I have different domain on my list so I assume some kind of variable that
identifies duplicate domains would be appropriate? I need to emphasize root domains i.e. domain.com and not domain.com/about-us/

Thanks.

**newdoverman** · 06-25-2014, 09:06 AM

I think that a read of this explanation of domains is in order so that you know exactly what you are looking for:http://en.wikipedia.org/wiki/List_of...-level_domains

The basic list of allowed characters in a URL are: A-Z, a-z, 0-9, -, ., _, ~, :, /, ?, #, [, ], @, !, $, &, ', (, ), *, +, ,, ;, Several of these characters can be used as separators of the various elements of the URL so taking a URL apart could be very difficult.

The highest levels of domain names are: .com, .org, .net, .int, .edu, .gov, .mil

Then there are country .codes for just about every country on earth. This just covers what is to the immediate right of the "." To the left of the "." almost anything goes.

To go even further the real codes that are the basis for what we usually see as a URL are like this:
165.243.065.031
109.014.036.090
88.80.186.67
162.243.65.31
214.5.182.127
213.5.182.117
162.243.65.31

Are you trying to determine the basic URL of a website or just what is the aim of this?

**TechnicGeek** · 06-26-2014, 01:47 AM

I have a list of website I need to contact editors. What I am trying to do is determining best URL of 1 domain of all other URLs it has and keep that one. So next time I want to contact that editor, I have exact URL which I use to contact them. For example:

1. domain.com/about-us/
2. domain.com/contact-us/
3. domain.com/overview

From above 3 examples, /contact-us URL seems best for purpose, but what if it does not work? I can try /about-us where I usually can find email details of staff but what if I need to contact editor in chief and it can only be found on /overview ? In this case this is the URL I want to keep. But how to stay organized with many such domains? The convenient solution would be to have TRUE next to URLs that come from same domain name.

**newdoverman** · 06-26-2014, 08:31 AM

Websites change their organization like people change their minds. There isn't any guarantee that what you have as a URL for "contact us" will be any good 5 minutes from now. Even URL verifiers don't get it right a lot of the time.

I think that the best that you can do is to get the main page for the website in question. After that it is just taking a chance that it won't change.

**TechnicGeek** · 07-01-2014, 03:48 AM

In this case let's assume I want to identify below as TRUE:

1. domain.com/about-us/
2. domain.com/contact-us/
3. domain.com/overview
4. my-domain.com/about-us/
5. my-domain.com/contact-us/
6. my-domain.com/overview
7. my-website.net/about-us/
8. my-website.net/contact-us/

So I could only leave one of each unique root domain:

1. domain.com
2. my-domain.com
3. my-website.net

How to do that with conditional formatting?

**newdoverman** · 07-01-2014, 09:43 AM

I have come to the conclusion that there are just too many variations to deal with for you to achieve your objective. Companies are constantly changing their websites. What is good today could be obsolete tomorrow.

I recently ran a Bookmark checker (about 6 months after the last check) on my Bookmarks and about 20% of them were no longer good. The bookmarks were on home pages that one would think to be quite static.

I think that you would be further ahead to visit each website and find the contact email and make note of that.

Conditional formatting with containing

LinkBack

Thread Tools

Rate This Thread

Display

Conditional formatting with containing

Re: Conditional formatting with containing

Re: Conditional formatting with containing

Re: Conditional formatting with containing

Re: Conditional formatting with containing

Re: Conditional formatting with containing

Re: Conditional formatting with containing

Re: Conditional formatting with containing

Re: Conditional formatting with containing

Re: Conditional formatting with containing

Re: Conditional formatting with containing

Re: Conditional formatting with containing

Re: Conditional formatting with containing

Re: Conditional formatting with containing

Re: Conditional formatting with containing

Thread Information

Users Browsing this Thread

Similar Threads

Opening xlsm files with conditional formatting opens with removed conditional formatting

Conditional formatting based on conditional formatting of other cells?

[SOLVED] Conditional formatting: two different fields affect one cells formatting for a date.

Delete Conditional Formatting conditions but keep cell formatting - Excel 2010

Conditional Referencing Cell Ranges to avoid Conditional Formatting Cells Indvidually?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions