• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with Macros. Identfying names in a large file

T

Tikvah

New Member
Hi,

I'm new to Macros and hope that someone can help me create one for a project that I have. I have a file with a large amount of customer names. Some are names. Some are companies. What I would like to do is create a Macro to find the company names and put an X in the column next to it. For example any company with LLC or INC in the name would get an X in the adjacent column.

Or maybe a Macro is not the best way to do this. Any suggestions are appreciated.

Thanks in advance for the help.
 
Tikvah said:
Hi,

I'm new to Macros and hope that someone can help me create one for a project that I have. I have a file with a large amount of customer names. Some are names. Some are companies. What I would like to do is create a Macro to find the company names and put an X in the column next to it. For example any company with LLC or INC in the name would get an X in the adjacent column.

Or maybe a Macro is not the best way to do this. Any suggestions are appreciated.

Thanks in advance for the help.
Click to expand...

Somendra, Here is an example of the file.
 

Attachments

Somendra, This is what I'm looking for thanks. However I can't get the formula to work in my spreadsheet. Will the formula work if I copy and paste it into my spreadsheet? Thanks.
 
In the spreadsheet that you entered the formula, it is pulling off column F which is correct. When I enter the formula in my spreadsheet, it still pulls off column F which is incorrect. Can you tell me how to adjust the formula so I can change the column that it is pulling the information from. Other than that it is working correctly. Thanks.
 
Go to Name Manager (through Formula Tab or Ctrl+F3 shortcut key) there you will see the named formula CompanyList, change the ref. in that from column F to your desired column.

Regards,
 
Hi,

I'm exploring excel and need help on to get a value in a cell when I change value in a range.

Say I have A1:A5 = Yes and I want B1 to show Yes if any of the value in A1:A5 are changed to Yes...how do I go change this or is there a different function to crack this?
Here is what I tried and it works for once cell.
=IF(A1="Yes","Yes","No")

Thanks
Krishnan
 
Hi Krishnan ,

Can you be a little more specific ?

Do you want to monitor a change or do you want to check if any of the values is YES ?

Monitoring a change means that there may already be one or more cells in the specified range which have YES values ; however , a cell which has a NO value may have its value changed to YES. This change can be detected and appropriate action can be taken ; this can only be done with VBA.

Checking if there is at least one YES value in the specified range can be done by a formula , such as the following array formula , to be entered using CTRL SHIFT ENTER :

=IF(OR(A1:A5="Yes"),"Yes","No")

Which do you want to do ?

Narayan
 
Somendra Misra said:
Go to Name Manager (through Formula Tab or Ctrl+F3 shortcut key) there you will see the named formula CompanyList, change the ref. in that from column F to your desired column.

Regards,
Click to expand...

Somendra, Thanks for your guidance and your patience. You truly are an Excel Ninja. When I change the reference to the desired column, it does not put and "X" in the box. Here is the string that is in the Name Manager.

=' Mailable 001_001PP (2)'!$F$2:INDEX(' Mailable 001_001PP (2)'!$F$2:$F$20,MATCH("zzzzz",' Mailable 001_001PP (2)'!$F$2:$F$20))

Can you change the references in the string so it works with the following:
It must look at the name in column B then check the list of names in column D. If the name in column D matches the name in column B, it puts an X in column C. Hope that makes sense. I attached a sample file for your reference. Thanks again.
 

Attachments

You can also try following non array formula with the data posted in #13
In cell C2 and copy down:
=IF(ISNUMBER(LOOKUP(2^15,SEARCH($D$2:$D$4,B2))),"X","")
 
Somendra Misra said:
In the file attached of comment #13, Its working fine. and the formula you posted above is not there in name manager.

Regards,
Click to expand...

Sorry for the confusion. The formula I posted is from the spreadsheet that I am trying to use for my project. Is it possible for fix the formula that I posted above so it works in my spreadsheet.
 
NARAYANK991 said:
Hi Krishnan ,

Can you be a little more specific ?

Do you want to monitor a change or do you want to check if any of the values is YES ?

Monitoring a change means that there may already be one or more cells in the specified range which have YES values ; however , a cell which has a NO value may have its value changed to YES. This change can be detected and appropriate action can be taken ; this can only be done with VBA.

Checking if there is at least one YES value in the specified range can be done by a formula , such as the following array formula , to be entered using CTRL SHIFT ENTER :

=IF(OR(A1:A5="Yes"),"Yes","No")

Which do you want to do ?

Narayan
Click to expand...
Hi,

I guess the VBA would be more appropriate...also possibly a IF(or) function may help...
 
NARAYANK991 said:
Hi Krishnan ,

Can you be a little more specific ?

Do you want to monitor a change or do you want to check if any of the values is YES ?

Monitoring a change means that there may already be one or more cells in the specified range which have YES values ; however , a cell which has a NO value may have its value changed to YES. This change can be detected and appropriate action can be taken ; this can only be done with VBA.

Checking if there is at least one YES value in the specified range can be done by a formula , such as the following array formula , to be entered using CTRL SHIFT ENTER :

=IF(OR(A1:A5="Yes"),"Yes","No")

Which do you want to do ?

Narayan
Click to expand...

Hi Narayan Sir,

Your formula does not require Ctrl+Shift+Enter and can be normally entered.

Regards,
 
You must log in or register to reply here.
Back
Top