+ Reply to Thread
Results 1 to 11 of 11

Searching Columns A and B for Specific Values and Returning '1' in Column C if 'True'

  1. #1
    Registered User
    Join Date
    01-25-2015
    Location
    Dublin
    MS-Off Ver
    2007
    Posts
    6

    Searching Columns A and B for Specific Values and Returning '1' in Column C if 'True'

    Hi all, long time lurker, first time poster. Thanking you in advance.

    I am attempting to do some analysis on calls into a call center and I have a large spreadsheet that contains a call category and call description. Essentially, what I want to do is search column A for 'Category X' and subsequently search the corresponding call description in Column B for a specific code. Then, if there is a match, I want to put the value '1' for a match in column C and '0' if not found.

    Lets say column A contains three categories: i.e. Operations, Claims, Sales.
    Lets say column B contains any random string of text: i.e. string of text is for code 0000, string of text is for code 0000, string of text is for the code 1111

    So, say I want to search all the text in column B for the code '0000' where the category in column A is 'Operations' and subsequently put the value 1 in the corresponding cell in column C.

    How would I go about doing this?Excel_Support.xls
    Last edited by Tomasome; 01-25-2015 at 12:01 PM.

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Searching Columns A and B for Specific Values and Returning '1' in Column C if 'True'

    Hi,
    Welcome to the Forum.

    Maybe this.....

    In C2
    Please Login or Register  to view this content.
    and then copy down.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Searching Columns A and B for Specific Values and Returning '1' in Column C if 'True'

    or

    =SUMPRODUCT((A2="Operations")*(RIGHT(B2,4)="0000"))
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Registered User
    Join Date
    01-25-2015
    Location
    Dublin
    MS-Off Ver
    2007
    Posts
    6

    Re: Searching Columns A and B for Specific Values and Returning '1' in Column C if 'True'

    Quote Originally Posted by sktneer View Post
    Hi,
    Welcome to the Forum.

    Maybe this.....

    In C2
    Please Login or Register  to view this content.
    and then copy down.
    Sktneer; many thanks for this. It worked perfectly.

  5. #5
    Registered User
    Join Date
    01-25-2015
    Location
    Dublin
    MS-Off Ver
    2007
    Posts
    6

    Re: Searching Columns A and B for Specific Values and Returning '1' in Column C if 'True'

    Quote Originally Posted by AlKey View Post
    or

    =SUMPRODUCT((A2="Operations")*(RIGHT(B2,4)="0000"))
    Hi AlKey, thanks for this. It worked a treat.

  6. #6
    Registered User
    Join Date
    01-25-2015
    Location
    Dublin
    MS-Off Ver
    2007
    Posts
    6

    Re: Searching Columns A and B for Specific Values and Returning '1' in Column C if 'True'

    Quote Originally Posted by AlKey View Post
    or

    =SUMPRODUCT((A2="Operations")*(RIGHT(B2,4)="0000"))
    Hi AlKey, thanks for this. It worked a treat.

  7. #7
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Searching Columns A and B for Specific Values and Returning '1' in Column C if 'True'

    You're welcome. Glad that we could help.

    If that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

  8. #8
    Registered User
    Join Date
    01-25-2015
    Location
    Dublin
    MS-Off Ver
    2007
    Posts
    6

    Re: Searching Columns A and B for Specific Values and Returning '1' in Column C if 'True'

    Quote Originally Posted by sktneer View Post
    You're welcome. Glad that we could help.

    If that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
    Have another little add on query to this. Now I need to search another column for another specific value. This column will now become A and the columns I have outlined above will obviously become B, C and D.

    So say the values in the new column (A) lists colours. I want to update the above formula so that I search values equal to 'Orange', 'Operations' and '0000'. Does the formula read:

    =AND(A2="Oranges", B2="Operations",ISNUMBER(SEARCH("0000",C2)))*1

  9. #9
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Searching Columns A and B for Specific Values and Returning '1' in Column C if 'True'

    Correct. Didn't you try this? Remember there is difference between Orange and Oranges.

  10. #10
    Registered User
    Join Date
    01-25-2015
    Location
    Dublin
    MS-Off Ver
    2007
    Posts
    6

    Re: Searching Columns A and B for Specific Values and Returning '1' in Column C if 'True'

    Quote Originally Posted by sktneer View Post
    Correct. Didn't you try this? Remember there is difference between Orange and Oranges.
    Yes, correct. It worked. Now, I want to search for any of the following values in the call description column '0000' or '1111' or '2222' and if any of these values are present - return a 1?

  11. #11
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Searching Columns A and B for Specific Values and Returning '1' in Column C if 'True'

    For formula looking at the code in Call Description column only, try this....
    Please Login or Register  to view this content.
    And for a formula looking at the code in Call Description column as well as at the other column for previous criteria, try this........
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 12-18-2014, 01:56 PM
  2. Searching Column and returning specific rows to UserForm
    By battdan1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-26-2013, 06:54 PM
  3. Replies: 4
    Last Post: 08-12-2011, 06:57 PM
  4. Replies: 3
    Last Post: 08-02-2010, 10:07 AM
  5. Searching Columns and Returning Row values
    By jasteinh in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-31-2010, 09:09 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1