+ Reply to Thread
Results 1 to 10 of 10

Apply formula based on the equality of adjacent cells

  1. #1
    Forum Contributor
    Join Date
    11-06-2021
    Location
    Tripoli
    MS-Off Ver
    Still using 2007 in 2023
    Posts
    291

    Wink Apply formula based on the equality of adjacent cells

    Hey guys,
    I hope you are well.
    There is a task where somebody will enter values in column A.
    The values are to be entered moving downward along column A starting from A2.
    I want only if the adjacent values in column A are equal to each other to return TRUE.
    Important case: If i was entering data in column A, the first instance of a new value for example letter B,
    so it shall return false in the first time, but when i provide afterwards in its adjacent below cell value B than all is to be returned true for both B values.
    Another example, in cell A13 when i entered letter C, it shall return false at its first instance, only when i enter afterwards letter C
    in A14 then both shall be true.
    Please refer to my excel sheet, because it is much clearer.
    Thanks a lot.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,426

    Re: Apply formula based on the equality of adjacent cells

    One way:
    =IF(A2="","",COUNTIF(A:A,A2)>1)
    Attached Files Attached Files
    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

  3. #3
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,465

    Re: Apply formula based on the equality of adjacent cells

    A way:

    Try in B2 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,668

    Re: Apply formula based on the equality of adjacent cells

    May be this one.
    In A2 copied down.

    =IF(A2="","",IF(ROW()=2,TRUE,IF(A2=A1,TRUE,FALSE)))
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  5. #5
    Forum Contributor
    Join Date
    11-06-2021
    Location
    Tripoli
    MS-Off Ver
    Still using 2007 in 2023
    Posts
    291

    Re: Apply formula based on the equality of adjacent cells

    All solutions worked great, but i have still only a last adjustment, in case i entered in cell A14 letter A, i shall return False and not True, because letter A has already recieved a true Score before.
    In that case it will works totally right for me, thanks a lot.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,426

    Re: Apply formula based on the equality of adjacent cells

    Question. In myour modified requirement, will rows 2-y, where column A contains A will REMAIN TRUE, even though there are 4 of them?

    Is the requirement that if A13 is NOT A and A14 IS A, then it should return FALSE>

  7. #7
    Forum Contributor
    Join Date
    11-06-2021
    Location
    Tripoli
    MS-Off Ver
    Still using 2007 in 2023
    Posts
    291

    Re: Apply formula based on the equality of adjacent cells

    Hey glen, yes, the past letters of A are ought to remain true, from cell A2 to cell A5, those letters would not change afterwards, but if an A has pop up in another cell that is not adjacent to the initial A's then it should return False, as in my case if A14 is A and A13 is not A, then A14 is false, thanks man

  8. #8
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,465

    Re: Apply formula based on the equality of adjacent cells

    Please try in B2 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,668

    Re: Apply formula based on the equality of adjacent cells

    Try this

    =IF(A2="","",IF(ROW()=2,TRUE,COUNTIF($A$2:$A2,$A2)<>1))
    Attached Files Attached Files

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,426

    Re: Apply formula based on the equality of adjacent cells

    I think Hans in NEARLY there. One tweak and one Q:

    =IF(A2="","",OR(A2=A1,A2=A3))

    copied down.

    the Q. What is the expected result in B6. I'm pretty sure it should be FALSE, but...
    Attached Files Attached Files

+ 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. [SOLVED] mark color for not matched items for adjacent cells based on another adjacent cells
    By tubrak in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-14-2022, 08:02 AM
  2. Using SUMIF to compare two cells for equality and then sum if there is a match
    By methuselah90 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-28-2014, 05:56 AM
  3. Macro to populate cells with formula based on adjacent cell
    By eoghanmolloy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-04-2014, 07:06 AM
  4. Replies: 5
    Last Post: 11-29-2010, 06:25 PM
  5. Formula based on contents of adjacent cells?
    By Finalfrontier1976 in forum Excel General
    Replies: 2
    Last Post: 05-07-2010, 07:38 PM
  6. How do i a apply range(autoformat) in non-adjacent cells?
    By Brent from the Bahamas in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 01-23-2006, 06:42 AM
  7. Apply cell shading based on adjacent cells in EXCEL
    By garywr in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-10-2005, 09:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1