+ Reply to Thread
Results 1 to 9 of 9

Deleting consecutive values only if they exceed a certain number of occurrences

  1. #1
    Registered User
    Join Date
    08-08-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016 16.0.10325.20082
    Posts
    3

    Deleting consecutive values only if they exceed a certain number of occurrences

    I'm working with a large spreadsheet. It consists of physical activity values, ranging from 0 to several thousand per minute.
    My task is to delete values of 'non-wear time', which means deleting consecutive values of 10 or more "0's". But not if there is 9 or less. Is there any function or macro that can do this automatically?

    I've added a photo as an example, showing a marking of cells that are to be deleted, while underneath there are cells that should not be deleted. Any help on the matter would save me a ton of hours.

    example.png

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,190

    Re: Deleting consecutive values only if they exceed a certain number of occurrences

    You will need VBA for this - I shall move the thread for you.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    9,863

    Re: Deleting consecutive values only if they exceed a certain number of occurrences

    Hello Charles_S and Welcome to Excel Forum.
    I am confused by the description and the picture. The description says 10 or more consecutive, as in the 14 zeros between 460 and 40 in column DP. The picture shows that even single zeros, like the one in the third cell from the top in column DJ, should be deleted.
    Also, should anything happen to the corresponding texts? For instance near the bottom of column DJ is a cell containing a zero marked for deletion. Beneath that cell is a cell containing 378. Corresponding cells in column DK display inaktiv and left respectively. When the zero is deleted should that cell remain blank and the corresponding cell in DK continue to display inaktiv, or should 378 and left be moved up?
    It might help if you could upload a small sample of the screen shot as an example of your "before" and then manually produce the desired outcome of the code. In that way we may be able to come up with a method that can automate the process.
    To upload a sample workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  4. #4
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,162

    Re: Deleting consecutive values only if they exceed a certain number of occurrences

    You can try this on a copy of your workbook.
    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,162

    Re: Deleting consecutive values only if they exceed a certain number of occurrences

    This might be better.
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    08-08-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016 16.0.10325.20082
    Posts
    3

    Re: Deleting consecutive values only if they exceed a certain number of occurrences

    Hi

    Thanks for the replies. I’m still having difficulties with the proposed methods, and they are too advanced for me to successfully debug (I’ve tried )

    So I will simplify the problem. In a single column of numbers, I need 10 or more consecutive 0’-s deleted. I’ve added two pictures showing before and after.

    It would be preferable if this operation could be performed on the selected cells only, but not crucial.

    1.png2.png

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    7,441

    Re: Deleting consecutive values only if they exceed a certain number of occurrences

    How about
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    08-08-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016 16.0.10325.20082
    Posts
    3

    Re: Deleting consecutive values only if they exceed a certain number of occurrences

    Perfect! The rest I can handle myself. Thanks a lot

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    7,441

    Re: Deleting consecutive values only if they exceed a certain number of occurrences

    Glad to help 7 thanks for the feedback

+ 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. Count the number of occurrences of consecutive absences
    By MissC in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-28-2017, 11:18 PM
  2. [SOLVED] Count number of occurrences but if there are consecutive matches count them as one
    By michaljireht in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-26-2013, 08:26 PM
  3. Count consecutive number of negative occurrences >=3
    By nbiggss in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-02-2012, 02:14 PM
  4. Count the number of consecutive occurrences
    By timmycl_7 in forum Excel General
    Replies: 12
    Last Post: 05-03-2012, 02:04 PM
  5. [SOLVED] Consecutive Numbers down a column not to Exceed 49
    By Bernie Deitrick in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 09-06-2005, 03:05 PM
  6. Consecutive Numbers down a column not to Exceed 49
    By Bernie Deitrick in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 04:05 AM
  7. [SOLVED] Consecutive Numbers down a column not to Exceed 49
    By Nelson in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 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