+ Reply to Thread
Results 1 to 2 of 2

Sorting or formula to help delete extra entries. Truncate at 20 events per animal.

  1. #1
    Registered User
    Join Date
    01-26-2021
    Location
    Arizona
    MS-Off Ver
    2016
    Posts
    1

    Sorting or formula to help delete extra entries. Truncate at 20 events per animal.

    Hello, looking for help with some of my work. Here is a description of the files.

    Version: Office 365

    There are just 9 columns in this order (Date, ID, Nose, Eye, Ear, Cough, Fecal, Navel, Joint). There are 22,289 rows of data.

    Date is the date of observation

    ID is a unique ID for the animal

    The rest are scored on a scale of 0 to 5. The vast majority of the spreadsheet is 0's. These animals were checked 2x per week until they were moved. They were moved between week 10 and week 12, so some have more checks than others. I want to truncate all the data at 10 weeks which would be 20 checks for each animal.



    How can I put in a formula so that I can sort by the first 20 checks, in order to remove the extra checks some animals had due to staying longer.



    I'll be here all day trying to come up with an idea, if you need more info let me know, anything helps.
    Attached Files Attached Files
    Last edited by mbpoindexter; 01-26-2021 at 01:10 PM.

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,808

    Re: Sorting or formula to help delete extra entries. Truncate at 20 events per animal.

    Well, since no one has answered yet, I'll give you mine, but it involves copying down a helper column. I'm sure there's a better, dynamic array formula, but I can't figure it out so far.

    So in J2 I added this formula:
    =IF(AND(B2=B1,J1<10),J1+1,IF(B2=B1,"",1))
    and then copied it down.

    In L2 I entered this dynamic array formula to get the results:
    =FILTER(A2:I87,ISNUMBER(J2:J87))

    Does this help?
    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] VB Code to find and delete and Truncate cell length
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-25-2020, 12:37 PM
  2. Replies: 5
    Last Post: 09-04-2012, 09:59 AM
  3. extra hours adding money for each extra hour worked FORMULA
    By cynthiamcastro in forum Excel General
    Replies: 3
    Last Post: 06-18-2012, 11:27 AM
  4. Merging multiple workbooks that contain identical tabs:Animal
    By Heather.Taylor in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 08-04-2011, 01:15 PM
  5. Range lookups with overlapping entries and extra check
    By mstegehu in forum Excel General
    Replies: 3
    Last Post: 07-05-2010, 09:02 AM
  6. Worksheet events and a little bit extra code
    By tinkerbellsmyhoe in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-27-2006, 10:00 AM
  7. Track entries and events spreadsheet,
    By ccoverne in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-28-2006, 01:50 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