+ Reply to Thread
Results 1 to 12 of 12

case sensitive forumals

  1. 07-10-2015, 09:19 AM #1
    bjbboats
    bjbboats is offline
    Registered User
    Join Date
    02-19-2015
    Location
    Newcastle, England
    MS-Off Ver
    2013
    Posts
    98

    case sensitive forumals

    hi

    I have some fairly complex formulas to compare 2015 data to 2014 data and work out the differences.

    =IFERROR(F16-VLOOKUP(E16,$P$16:$Y$758,2,0),"")

    Is the formula for 23015 - 2014

    The issue I have is that it is using a keyword term to look at the data but is not case sensitive. However some of the terms have capital letters and some don't so the data is being compared to the wrong field.

    is there something I can add to the formula so it matches the exact text including capital or lower case letters?

    Thanks for any help!
    Reply With QuoteRegister To Reply
  2. 07-10-2015, 09:22 AM #2
    ranman256
    ranman256 is offline
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,176

    Re: case sensitive forumals

    case doesnt matter. There's no reason it should.
    Reply With QuoteRegister To Reply
  3. 07-10-2015, 09:26 AM #3
    bjbboats
    bjbboats is offline
    Registered User
    Join Date
    02-19-2015
    Location
    Newcastle, England
    MS-Off Ver
    2013
    Posts
    98

    Re: case sensitive forumals

    Not sure what you mean. The case does make a difference here

    I have some terms which are the same text but different cases so the formula pulls the first term rather than matching the exact term including case sensitivity.
    Reply With QuoteRegister To Reply
  4. 07-10-2015, 09:30 AM #4
    berlan
    berlan is offline
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, UK
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: case sensitive forumals

    If you want it case sensitive, without seeing the exact structure, try (untested):

    =iferror(F16-index($Q$16:$Q$758,match(true,exact($P$16:$P$758,E16),0)),"")

    Best,
    berlan

    EDIT: array formula, confirm by Ctrl+Shift+Enter
    Last edited by berlan; 07-10-2015 at 09:39 AM.
    Reply With QuoteRegister To Reply
  5. 07-10-2015, 09:55 AM #5
    bjbboats
    bjbboats is offline
    Registered User
    Join Date
    02-19-2015
    Location
    Newcastle, England
    MS-Off Ver
    2013
    Posts
    98

    Re: case sensitive forumals

    Attached an example which will hopefully help explain
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 07-10-2015, 10:08 AM #6
    berlan
    berlan is offline
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, UK
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: case sensitive forumals

    Not sure exactly, but see highlighted cells (in yellow) in the attached, it that helps.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 07-10-2015, 10:08 AM #7
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,025

    Re: case sensitive forumals

    Berlan's array formula should do it (with the correct ranges, subsequently supplied)

    =IFERROR(F16-INDEX($Q$3:$Q$745,MATCH(TRUE,EXACT($P$3:$P$745,E16),0)),"")
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  8. 07-10-2015, 10:17 AM #8
    bjbboats
    bjbboats is offline
    Registered User
    Join Date
    02-19-2015
    Location
    Newcastle, England
    MS-Off Ver
    2013
    Posts
    98

    Re: case sensitive forumals

    I think that's worked thanks!

    Can something similar be done for the percentage difference column?
    Reply With QuoteRegister To Reply
  9. 07-10-2015, 11:02 AM #9
    bjbboats
    bjbboats is offline
    Registered User
    Join Date
    02-19-2015
    Location
    Newcastle, England
    MS-Off Ver
    2013
    Posts
    98

    Re: case sensitive forumals

    And the rank column too
    Reply With QuoteRegister To Reply
  10. 07-10-2015, 11:35 AM #10
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,025

    Re: case sensitive forumals

    Berlan's already done the % differenc ecolumn. What do you want done regarding the ranking? The exisiting formula refers out to a group of empty cells in X.
    Reply With QuoteRegister To Reply
  11. 07-10-2015, 11:44 AM #11
    bjbboats
    bjbboats is offline
    Registered User
    Join Date
    02-19-2015
    Location
    Newcastle, England
    MS-Off Ver
    2013
    Posts
    98

    Re: case sensitive forumals

    hadn't noticed thanks!

    The rank column shows the position it is in 2015 and 2014' position in brackets. Looking at conversion columns.
    Reply With QuoteRegister To Reply
  12. 07-14-2015, 04:39 AM #12
    bjbboats
    bjbboats is offline
    Registered User
    Join Date
    02-19-2015
    Location
    Newcastle, England
    MS-Off Ver
    2013
    Posts
    98

    Re: case sensitive forumals

    Could anyone help with that please?
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Case Sensitive Countif?
    By jessbr in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-06-2014, 11:35 AM
  2. [SOLVED] SUMPRODUCT Or SUMIF - In case of Case sensitive???
    By lifeisaspreadsheet in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-28-2012, 07:57 AM
  3. [SOLVED] Case Sensitive w/ IF
    By jeffP in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-05-2006, 09:20 PM
  4. .Name case sensitive
    By CinqueTerra in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-16-2006, 05:00 PM
  5. Case Sensitive
    By hemants in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-09-2005, 04:20 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1