Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2008
    Location
    New Orleans, Louisiana, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    clear cells based on a formula

    I am clearing cells manually based on the results of a formula and I know if I knew macros I could whip this out in a minute. Anyone care to help? I have a spreadsheet with range A1:S2294. In the A column, I have a formula to determine if (A1=A2, true, false) so I have identified the rows but I only want to clear (not delete) cells B:H where A is true. I'd appreciate any help.
    I did search the posts for an hour but have not been able to relate any of the answers to my particular situation. Thanks.

  2. #2
    4 Star Lounger
    Join Date
    May 2012
    Posts
    404
    Thanks
    0
    Thanked 49 Times in 39 Posts

  3. #3
    New Lounger
    Join Date
    Dec 2008
    Location
    New Orleans, Louisiana, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No, couldn't get it to work. I may be doing something wrong. Basically, I want to do what "remove duplicates" does but just clear the duplicate cells, not delete them.

  4. #4
    4 Star Lounger
    Join Date
    May 2012
    Posts
    404
    Thanks
    0
    Thanked 49 Times in 39 Posts
    Is this one any better? Delete duplicate rows from a list in Excel

    There are a few more here: Remove duplicate entries in cells

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    If you have identified the rows, then one solution, without requiring VBA or macros, is sort these rows together and then select this whole range in cols B:H to hit the [Delete] key to clear them.

    I would proceed as follows:
    First, you must convert your formulas to 'values' before sorting.
    Next, before sorting on your True/False column, add a new adjacent temporary column (I would give this a heading of Sort), and number these from 1 to whatever (you can use autofill etc - not a formula!). This is to allow you to return the data records to the original sort position.
    Next, sort on your true/false column.
    Now, all your records are grouped together, so select the relevant block in columns B:H and hit the [Delete] key.
    Then, resort on your Sort column.
    Now delete your temporary Sort column.
    Job done.

    zeddy

Posting Permissions

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