+ Reply to Thread
Results 1 to 8 of 8

Excel How to prevent a cell from duplicating cell above

  1. #1
    Forum Contributor
    Join Date
    09-05-2013
    Location
    Spring, Tx
    MS-Off Ver
    Excel 365
    Posts
    122

    Excel How to prevent a cell from duplicating cell above

    I have an excel document that has a row that can contain duplicates but not one cell on top the other
    Fine Not Fine
    10 10
    20 20
    30 20
    40 30

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Excel How to prevent a cell from duplicating cell above

    In the second cell use a custom data validation rule:

    =A2<>A1

    This assumes your data starts in A1.

    Then copy that cell, and Paste Special/Validation to all the cells below it that you want to restrict.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor
    Join Date
    09-05-2013
    Location
    Spring, Tx
    MS-Off Ver
    Excel 365
    Posts
    122

    Re: Excel How to prevent a cell from duplicating cell above

    6StringJazzer thanks for your reply. Either I am not understanding fully or it is not working as I would like

  4. #4
    Forum Contributor
    Join Date
    09-05-2013
    Location
    Spring, Tx
    MS-Off Ver
    Excel 365
    Posts
    122

    Re: Excel How to prevent a cell from duplicating cell above

    Here is an example of what i am wanting it to look like
    Attached Files Attached Files

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Excel How to prevent a cell from duplicating cell above

    Can you clarify your question? Your title says you want to prevent it. Do you want to prevent it, or just turn it red if it happens?

    I have added two sheets.

    The first one uses a conditional formatting rule to turn a cell red if it duplicates the cell in the previous row.
    =AND(A1<>"",A1=OFFSET(A1,-1,0))

    The second one uses data validation so it is not possible to enter a duplicate:
    =A2<>A1
    Try to enter a 20 in either column.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    09-05-2013
    Location
    Spring, Tx
    MS-Off Ver
    Excel 365
    Posts
    122

    Re: Excel How to prevent a cell from duplicating cell above

    6StringJazzer you are awesome. Sorry about the confusion. I did want to prevent it from happening. When you sent your first response I noticed it turned the cells red. So I went with that scenario.
    I really like this one User not allowed to enter dupe. Thanks for everything

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Excel How to prevent a cell from duplicating cell above

    Quote Originally Posted by zhead View Post
    When you sent your first response I noticed it turned the cells red.
    Perhaps you tried to use it as a conditional formatting rule, instead of a data validation rule. Glad all is good now!

  8. #8
    Forum Contributor
    Join Date
    09-05-2013
    Location
    Spring, Tx
    MS-Off Ver
    Excel 365
    Posts
    122

    Re: Excel How to prevent a cell from duplicating cell above

    Yes you are correct I did try it as conditional formatting and not data validation

+ 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: 10
    Last Post: 11-28-2020, 04:02 PM
  2. Help duplicating rows within Excel based upon cell info.
    By jnowell in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-14-2015, 04:19 AM
  3. [SOLVED] VBA to prevent saving of excel if cell is blank
    By anuj_sethi1043 in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 08-14-2014, 06:37 PM
  4. Prevent excel to take me to another sheet when clicking on a locked cell.
    By susie86 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-11-2009, 08:17 PM
  5. [SOLVED] prevent blank cell in excel worksheet
    By jatman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-29-2006, 10:08 AM
  6. [SOLVED] How do I prevent Excel from plotting a cell w/ a formula as a "ze.
    By Hatchet_Jack in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-09-2005, 07:06 PM

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