Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Formula To Search Cells/columns Containing Specific Text

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi,

I would like to search on 2 columns, to see if each cell in Column B contains text from any cell in Column A.

For example, IF the text in Column B (Email Address), Row 2 contains text from any cell in Column A (Domain) = True. Below is an example:

(A) DOMAIN NAME | (B)EMAIL ADDRESS | (C)TRUE OR FALSE?
hotmail.com | kara@web.com | TRUE
msn.com | mike@site.com | TRUE
web.com | jess@myaddy.com | FALSE
domain.com | sara@aol.com | TRUE
aol.com | ted@domain.com | TRUE
site.com | mark@domain.com | TRUE

I am currently using this formula:
=IF(ISNUMBER(SEARCH($A:$A,E2)),"TRUE","FALSE")

but it doesn't appear to be working. It only works if the matching rows are adjacent to each other. As you can see from the example above, the matching domains could be in any row, any cell.

Would anyone know the correct formula for this? I've searched everywhere Thank you!

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Determine a Cell's Color with this UDF - Outputs as Text or the Index Number in Excel
- This free Excel UDF allows you to output the color of a cell in text format or as that color's index number. Also note
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se

Similar Topics







Hi,

I would like to search on 2 columns, to see if each cell in Column B contains text from any cell in Column A.

For example, IF the text in Column B (Email Address), Row 2 contains text from any cell in Column A (Domain) = True. Below is an example:

(A) DOMAIN NAME | (B)EMAIL ADDRESS | (C)TRUE OR FALSE?
hotmail.com | kara@ web.com | TRUE
msn.com | mike@site.com | TRUE
web.com | jess@myaddy.com | FALSE
domain.com | sara@aol.com | TRUE
aol.com | ted@domain.com | TRUE
site.com | mark@domain.com | TRUE

I am currently using this formula:
=IF(ISNUMBER(SEARCH($A:$A,E2)),"TRUE","FALSE")

but it doesn't appear to be working. It only works if the matching rows are adjacent to each other. As you can see from the example above, the matching domains could be in any row, any cell.

Would anyone know the correct formula for this? I've searched everywhere Thank you!



How can I create a link that will send mail to multiple reciepients, with out
getting a pop-up telling me that the formula is too long?

Example:=HYPERLINK("mailto:name@domain.com,name@domain.com,name@domain.com,name@domain.com,name@doma in.com,name@domain.com,name@domain.com,name@domain.com,name@domain.com,name@domain.com,name@domain.c omname@domain.com,name@domain.com,name@domain.com,name@domain.com,name@domain.com,name@domain.com,na me@domain.com,name@domain.com,name@domain.com,name@domain.com,,"family")



I have been looking on forums and using Google searches for the better part of a day now and cannot seem to get this working. I have gotten close, but not quite what I need. What I need to do is compare a cell containing text with against a column that contains text on a different sheet.

For example in cell A1 I have text "hostname"

In another sheet I have a column of hostnames in format of "hostname.domain.com" or a variety of text tacked on to the text from A1. So I really need the formula to look for my text string anywhere in the text of the cell range.

I want the cell with the formula to give a true/false if the text in A1 is found in that other column. The search needs to be case insensitive.

The closest I got after trying to use MATCH, SEARCH, FIND was:

=VLookup(A1,'Sheet1'!$G$3:$G$3779,1,False)

But that is for an exact match which does not work as it isn't exact and doesn't really return true/false.

Any help would truly be appreciated. Thank you.




Hi guys

Using Excel 2010. Hopefully I will explain this OK!

I have a large list of websites (1000s) that I am trying to filter down based on various factors. One factor is that I wish to exclude domain names that contain certain words, e.g. "directory", "forum" etc (at the moment list of 'bad words' is 20ish).

So let's say I have the list of domain names in column A.

List of bad sites can be located wherever.

In column B I want to see if that domain name contains any of those terms, returning Yes/No or True/False, whatever. Once I've copied that formula down I can then delete entries which do.

So far have tried things I've found online eg

=LOOKUP(9.9E+307,SEARCH($C$1:$C$10,A1))

and

=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$5,A2)))>0

But to no avail (eg the sumproduct one always returns true no matter what)

If anyone knows how to solve this I would be one happy person!

thanks in advance

I have a bunch of domain names (including subdomains) in column B

In column A i need just the raw domain name...

example

a1 [domain.com]
b1 [domain.com]

a2 [domain.com]
b2 [mail.domain.com]

a3 [domain.com]
b3 [subdomain.domain.com]

etc...

thanks




Hi,

I'm trying to use SEARCH function to find out whether the email domain is a public domain or a corporate domain.

Column A has all the email ids
Column B is empty and i need to tag them as public domain / corporate domain -- Public domain as 1 and corporate as blank
Column C is empty and i need to tag the name of the public domain if it's corportate leave it as blank and if public - name it as yahoo/gmail/hotmail
Column D (D2:D10) has all the list of all the public domains like yahoo, gmail, hotmail etc

In column B - I'm trying to get the value as "1" if it's public domain or keep it as blank if its corporate
In column C - I'm trying to get the name of the public domain according to the value in column A - if it's corporate leave it as blank

I'm trying my luck with SEARCH function but unable to do so..

=IFERROR(SEARCH($D$2:$D$10,A:A),"")



Hi All,

I have a file which contains company and web. Here I need to find any of the company word matching in domain and it should return true or false.

Example:-

SimplexGrinnell LP www.simplexgrinnell.com True
CFT Corporation www.cftcorp.com True
Mapsco Inc www.Mapsco.com True
Lookout Mountain www.abc.com True

Even If single word matching in domain need to return true and also if all the word in the company matches in web needs to return true.

For more information. Please check the attachment.

Hello,

I am totally stuck on how to do this problem.

I have a sheet with a list of domain names in column "A" and the days since the domain was entered into the sheet in column "B" as well as colum C which has a yes / no selection.

What I want to be able to do is to have another area on the same worksheet which will show the domains that are over 4 days old and selection in column "c" is "no".

I have tried this but the following happens.

domain 1
0
0
domain 2
0
0
domain 3

Is there anyway to modify this so that it shows the following instead.

domain 1
domain 2
domain 3

Currently using the following formula

If(AND(B1>4,C1="N"),A1,0)

Ideally the results will be in a box further down the spreadsheet, also is there anyway to auto calculate the new results once some rows are deleted.

Thanks in advance

Ian


Hi everyone. I expect this query has been answered here before, but I am not sure of the right terminology to use so I haven't been able to find a solution via the search. My problem is this. I have a two column data base:

Property, Imagepath
A, domain.com/imageA1.jpg
A, domain.com/imageA2.jpg
A, domain.com/imageA3.jpg
B, domain.com/imageB1.jpg
B, domain.com/imageB2.jpg
B, domain.com/imageB3.jpg

What I want is to group it by property, so I get

Property, Image1, Image2, Image3
A, domain.com/imageA1.jpg, domain.com/imageA2.jpg, domain.com/imageA3.jpg
B, domain.com/imageB1.jpg, domain.com/imageB2.jpg, domain.com/imageB3.jpg

Is this possible in Excel?

Thanks in advance.


I am an amateur in programming. I will try to explain what I need help with. I have over 800 email addresses, many of which are from the same domain (@DomainName.com) but are preceded by a name or an abbreviation for a person's name. So, I deleted the duplicates from column B and now have 200 unique Domain Names. Check this out...

A B C
Name DomainName

1 Peter Pan NNL.com
2 P.Pan PB.com

...so on and so forth.

I need a formula that I can express the following function/argument...

IF B1="NNL", then C1="Never Never Land Inc." else if B1="PB.COM", then B1="Peanut Buter LLC"...and so on, 200 times...one ELSE IF PER DomainName...

Basically I want to run a check for all 200 domain names until the statement is true then I want it to return the respective CompanyName. I have been fiddling around for 5 hours...still stumped...


I am using the following spreadsheet. Column K (Grace Range) looks at Column B (GRC) and returns a TRUE or FALSE statement based on the following formula: =IF((B2>=45)*(B2=28 and FALSE TRUE FALSE 37 55 4 10 0 0 TRUE TRUE TRUE TRUE TRUE TRUE FALSE 10 17 5 10 0 0 TRUE TRUE TRUE TRUE FALSE TRUE FALSE 2 16 5 10 0 0 TRUE TRUE TRUE TRUE FALSE TRUE FALSE 19 34 5 10 0 0 TRUE TRUE TRUE TRUE FALSE TRUE FALSE 6 29 8 10 0 0 TRUE TRUE TRUE TRUE FALSE TRUE FALSE 32 8 10 0 0 TRUE TRUE TRUE TRUE FALSE TRUE FALSE 25 8 10 0 0 TRUE TRUE TRUE TRUE FALSE TRUE FALSE




First off, completely brand new to all of this. I have been given the task at work to break down a csv file that is a dump of multiple users address books, and sorts them out according to the owner of the address books email addresses. What I'm looking to do is create a macro that would be able to distinguish a change in email address, and make a new file when it notices the change. Example:

A B
user@domain.com Data
user@domain.com Data
user2@domain.com Data
user2@domain.com Data
user3@domain.com Data


Ideally, I would like to create a file with the user@domain.com rows only in one file, followed by user2@domain.com in another file, using the same macro if possible. Also, I'd like to try to name the newly created txt file the name of the email address in column A Not sure if this is even something that can be done, but I figured I'd take the shot considering the Excel file I have has over 300 address books and 6000 columns. I have been researching this for over the past 24 hours, and figured I'd finally wave the white flag and ask for help to see if it's even possible. Thanks in advance for any advice or suggestions!

I have a vendor list of over 3,000 email addresses. Some of the cells contain multiple email addresses that look like this:

John Doe ; Jane Doe jane.doe@domain.com;

I want to know if there is a macro/vbe code or formula that could separate the full names from the email address and span it over three columns like this:

Column 1 Column 2 Column 3
Doe, John Doe, J john.doe@domain.com

To make matters worse, some of vendors may have four different email addresses such as j.doe@domain.com; johndoe@domain.com; doejohn@doman.com, etc.

I would appreciate any suggestions. Thanks


Hi Everyone,

I have a load of cells that contain email addresses, and i need to split these addresses down, to show only the domain names.

Is there a quick way of doing this, or do i need to click into every cell and delete the 1st half of the address?

Many Thanks

James

Hi Everyone,

I have a load of cells that contain email addresses, and i need to split these addresses down, to show only the domain names.

Is there a quick way of doing this, or do i need to click into every cell and delete the 1st half of the address?

Many Thanks

James

James try text to column option in data tools to split the cells.

tek9step

Hi james_edstrom
try...
Code:

Option Explicit
Sub ptest()
    Dim rng As Range, p As Range
    Set rng = ActiveSheet.Range("B1:B12")
    With CreateObject("VBScript.RegExp")
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        .Pattern = "^([a-z0-9_\.\-]+)\@(([a-z0-9\-]+\.)+)([a-z]+)$"
        For Each p In rng
            If .Test(p) = True Then
                p.Offset(0, 1) = "Valid"
                p.Offset(0, 2) = .Replace(p, "$1")
                p.Offset(0, 3) = .Replace(p, "$2")
                p.Offset(0, 4) = .Replace(p, "$4")
            Else
                p.Offset(0, 1) = "InValid"
            End If
        Next
    End With
End Sub


adjust ranges to suit

Hi,

How about this:

=RIGHT(A1,LEN(A1)-FIND("@",A1))

or use data > text to columns > delimited > custom > @

oopsie, there's some double posting going on. Mods, please merge?

oopsie, there's some double posting going on. Mods, please merge?

Thanks everyone, very helpful!


Hi guys,

I am facing a challenge with a DB. One of the columns contains the email
addresses. I would like to find a way to delete the rows that contain in the
email address field a certain domain name. For example, in that column, there
are several email addresses containing the same domain name. And I need to
delete all the rows containing that domain name. Will I need to use a macro??

Many thanks,
Cristi



I was trying to help another board member with their question, but it led me to a question of my own. I am trying to get this array to work:
Code:

{=LARGE(ROW($A$1:$A$234)*NOT(ISERR(INDIRECT("Sheet1!Assets"&(ROW($A$1:$A$234))))),1)}


I named a range Assets234. I used ROW($A$1:$A$234) to return an array of numbers. That was then concatenated with "Sheet1!Assets". The INDIRECT function would then turn these into references. Since some of these aren't legitimate references I received a #REF! error. I then used the ISERR function to determine which of the references in the array evaluated to an error. I needed to multiply ROW($A$1:$A$234) against the results and ISERR had the opposite results I needed so I used NOT() to change the TRUEs to FALSEs and vice versa. Knowing that Excel sees False as 0 and True as any other number I multiplied the two arrays. Since everything was 0 except for the last value the LARGE() function should have returned 234. Instead it returns 0. Does anyone know why I get these results? Here is the formula evaluated one step at a time (I did my best to wrap them to the screen so you wouldn't have to browse to the right):

Code:

{=LARGE(ROW($A$1:$A$234)*NOT(ISERR(INDIRECT("Sheet1!Assets" &(ROW($A$1:$A$234))))),1)}


Code:

=LARGE(ROW($A$1:$A$234)*NOT(ISERR({#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;  #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;2})),1)


Code:

=LARGE(ROW($A$1:$A$234)*NOT({TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; FALSE}),1)


Code:

=LARGE(ROW($A$1:$A$234)*{FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE},1)


Code:

=LARGE({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21; 22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42; 43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63; 64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84; 85;86;87;88;89;90;91;92;93;94;95;96;97;98;99;100;101;102;103; 104;105;106;107;108;109;110;111;112;113;114;115;116;117;118; 119;120;121;122;123;124;125;126;127;128;129;130;131;132;133; 134;135;136;137;138;139;140;141;142;143;144;145;146;147;148; 149;150;151;152;153;154;155;156;157;158;159;160;161;162;163; 164;165;166;167;168;169;170;171;172;173;174;175;176;177;178; 179;180;181;182;183;184;185;186;187;188;189;190;191;192;193; 194;195;196;197;198;199;200;201;202;203;204;205;206;207;208; 209;210;211;212;213;214;215;216;217;218;219;220;221;222;223; 224;225;226;227;228;229;230;231;232;233;234}*{FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;TRUE},1)


Code:

=LARGE({0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0; 0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0; 0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0; 0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0; 0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0; 0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0; 0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;234},1)


Code:

=234





Hi there,

I'm currently using Windows XP and Excel 2007 and was wondering if anyone can help me with the following filtering issue?

Essentially, in my spreadsheet, I have the following data:

domain.com
domain.com/
domain.com/domain
domain.com/domain/games
a-domain.com
a-domain.com/

However, I'd only like to count the first reference to domain.com and a-domain.com, and get Excel to filter the rest.

Is there anything I can use to automatically remove "/sub-folder/sub-page" references from my spreadsheet, so after filtering, I'd be left with the following?:

domain.com
a-domain.com

Any help you can offer on this issue would be much appreciated - If you require any more information from me, then just let me know!


I have been manually using Excel to manage my Outlook blocked senders
list but it's gotten so large that I want to automate this task. I
thought that I could learn enough VBA to do this on my own but I can't
manage it. If someone would be willing to help, here's what I want to
accomplish:

Given
Worksheet with two columns
Each row represents an email address
Col A has the part of the email address before the @
Col B has the domain
An empty cell in Col A designates the entire domain
Worksheet is sorted in domain order

Goals
Where this worksheet contains multiple addresses from the same domain,
delete duplicate rows and blank col A for the remaining row.

After deleting the dups, I concatenate Col A + "@" + Col B and import
back into Outlook's blocked senders list.

--
Bob Simon
remove both "x"s from domain for private replies



I know this is possible, I just can't get my mind around it. What I'm trying to do is concatenate any values in the List column that have a corresponding value of TRUE in the PivotTable column. Below is the table:

List CheckBoxes PivotTable All FALSE FALSE CORP FALSE FALSE DATA TRUE TRUE DP FALSE FALSE DWL FALSE FALSE FI TRUE TRUE IC FALSE FALSE IT FALSE FALSE MDM TRUE TRUE MFG FALSE FALSE OM FALSE FALSE OTC FALSE FALSE PTP FALSE FALSE PTPDIR FALSE FALSE PTPIND FALSE FALSE SCP FALSE FALSE SNP FALSE FALSE Titan FALSE FALSE Error-Name FALSE FALSE

Now I was thinking this could be done with some twisted combination of MATCH, ROW, ROWS, COLUMN, COLUMNS, ADDRESS, INDIRECT, and perhaps even an OFFSET to move down to the next value. Any help would be greatly appreciated.


Hello All,

I am trying to think of the best way to implement this process. I believe a LEFT SEARCH will be best but please let me know if this is possible.

In column A I have a few different web addresses that end in .com, .edu, .org etc. After the domain extension is a really long web address. Is there any way I can have a formula in Column B that looks at the web address in column A and shortens the URL to that domain extension like .com, .edu etc?

Hi

I would like to make a spread sheet where i enter the following text


FREE DOMAIN NAME
FREE DOMAIN NAME
FREE DOMAIN NAME
FREE DOMAIN NAME
FREE DOMAIN NAME
FREE DOMAIN NAME
FREE DOMAIN NAME

Which scools down about 100+ but then i would like the spread sheet to be able to add the letter 1 after then the number 2 and so on like this

FREE DOMAIN NAME 1
FREE DOMAIN NAME 2
FREE DOMAIN NAME 3
FREE DOMAIN NAME 4
FREE DOMAIN NAME 5
FREE DOMAIN NAME 6
FREE DOMAIN NAME 7

And so on upto about 4000, is there a way i can do this as i don want to enter each number in manually

Thanks

Kerr


Hi,

I need to count the number of cells which is matching with my criteria form two columns. But my requirement is, if it is matching with the criteria of 1st cell of 1st column, then only it should match the criteria for 1st cell of 2nd column.

For exp:

Tom True
Can False
Can False
Tom True
Tom False
Tom False

So I need to check how many times, "Tom" is having "True" values. In this case, the value is "2".

I found one formula but it is not working:

=SUM(IF((A1:A6="Tom")-(B1:B6="True"),1,0))

It is giving result as 4, instead of 2.

Can anyone give me any suggestion to get the result.

Thanks & Regards,
Pramod


Hello,

Column 'A' I have a list of domains names with column B information connected to each domain.

Column 'F' I also have a list of domain names and next to these domain names are information about them in columns G, H and I.

I want to Match the domain name in column A (and its partner in column B) next to the same domain name in column F without moving F, G, H and I columns.

As there are more domains in column F than column A the final result would show many blank rows between domains in column A and B.

*** Some domains in A are not found in F at all. How will this affect things?

Hope this makes sense! See attached part list.

Thanks for any help. I use Excel 2007.

Regards
james


Dear Friends,

Looking for a bit of assistance with a formula that ought (i think) to look something like this:

=IF(ISNUMBER(SEARCH("Action Adventure", D2)), 0, IF(ISNUMBER(SEARCH("Action RPG", D2)), 0, IF(ISNUMBER(SEARCH("Action", D2)),1,0)))

What I want here is a query of the text in cell D2 such that the complete phrases "Action Adventure" and "Action RPG" yield a FALSE value of 0 while the term "Action" alone dings TRUE, resulting in a 1.

I've tried a lot of permutations on this; hopefully someone can help me debug and set me straight. I'm open to other solutions using LOOKUP and such, of course, but I'm pretty curious whether this approach was valid at all. I thought maybe having a TRUE/FALSE condition that writes 0 no matter the results for "Action RPG" and "Action Adventure" might have been the problem, but replacing the 0/1 with differing text responses in quotes doesn't solve it.

Thanks!

This should not be that hard. :x

I'm trying to write an error catching formulas read reads TRUEs and FALSEs from 4 columns. You can't have a TRUE in the next column until there is a TRUE in the first. For example...

TRUE FALSE FALSE FALSE = OK
FALSE TRUE FALSE FALSE = Bad
TRUE TRUE FALSE TRUE = Bad
TRUE TRUE FALSE FALSE - OK etc. etc.

Figured a straight forward formaula with AND s and ORs with an IF at the beginning would work fine. But noooooo, I can't get it to work. Could somebody tell me what I'm doing wrong? If I had done it correctly, rows 5,6,7, and 9 should have come back with 'ERROR'. The last row has a much simpler formula to test and it doesn't even work. Arg.

******** ******************** ************************************************************************> Microsoft Excel - ModelTrack.xls ___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout J8 J9 J10 J11 J12 J13 J14 J15 J16 =
F G H I J 7 Preliminary Vendor
Information Verified IFC Error 8 TRUE TRUE TRUE TRUE
9 TRUE TRUE TRUE FALSE
10 TRUE TRUE FALSE FALSE
11 TRUE FALSE FALSE FALSE
12 TRUE FALSE FALSE FALSE
13 TRUE FALSE TRUE FALSE
14 FALSE TRUE TRUE TRUE
15 TRUE FALSE FALSE FALSE
16 FALSE TRUE FALSE TRUE
Equipment
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.