+ Reply to Thread
Results 1 to 7 of 7

Removing Duplicate Returns on Calculations (Not Duplicate Lines)

  1. #1
    Registered User
    Join Date
    07-13-2018
    Location
    Columbus, OH
    MS-Off Ver
    Office 365
    Posts
    19

    Removing Duplicate Returns on Calculations (Not Duplicate Lines)

    I am working on a file that shows status of items. As items progress through steps of the process, they move to a new status. I need to calculate the time that an items spends in each step.

    Unfortunately my data also shows, as a new line, if updates were made in a step before it moves to the next step. Ex: While in the "test" step for item ID AAA, I updated a note to indicate progress was made. The data returns a line for this update, but the step remains unchanged and the entry date to and subsequent exit date from this step also remain unchanged.

    I would like to return "0" for instances that just show updates in steps, with no movement. In these instances, the ID, process step, entered queue date, exit queue date are all the same, however the notes are different.

    I have attached a file which is representative of my data file.

    Thank you for your help.

    TeamO
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,435

    Re: Removing Duplicate Returns on Calculations (Not Duplicate Lines)

    Is this what you want, or do you want a breakout for each occurrence? For example AAA his been in and out of Complete twice: once for 2 days and again for 32 days for a total of 34 days.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,435

    Re: Removing Duplicate Returns on Calculations (Not Duplicate Lines)

    Here is a version showing the expanded
    Attached Files Attached Files

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,435

    Re: Removing Duplicate Returns on Calculations (Not Duplicate Lines)

    Revision: I noticed that a couple items "doubled up" so I added a helper column to fix the issue.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-13-2018
    Location
    Columbus, OH
    MS-Off Ver
    Office 365
    Posts
    19

    Re: Removing Duplicate Returns on Calculations (Not Duplicate Lines)

    Hi dlfak, thank you for the response. While I think this would satisfy the visualization of the data, I'm wondering how to create a calculation that returns "0" for the 2 occurrences of duplicate status (defined by same unique ID, same status, same entry and exit time).

    I do want to count multiple occurrences in a queue, however I want eliminate those that meet the duplicate parameters I have defined.


    Perhaps I missed it in your attachments, if so I apologize.

  6. #6
    Valued Forum Contributor jtakw's Avatar
    Join Date
    05-05-2018
    Location
    CA, USA
    MS-Off Ver
    2016
    Posts
    668

    Re: Removing Duplicate Returns on Calculations (Not Duplicate Lines)

    Hi,

    I think this is what you're after:

    Formula in G2 copied down:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See attached sample.
    Attached Files Attached Files

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,435

    Re: Removing Duplicate Returns on Calculations (Not Duplicate Lines)

    Here is an alternative using helper columns.

    It is the combination of item, process step, entered queue and exit queue that makes an item unique. So I made a composite "key" by concatenating these items together.

    Then I used the formula: =MATCH([@Composite],[Composite],0)=ROW()-1 to determine duplicates. This formula yields TRUE for the first occurrence of an item and FALSE for all subsequent occurrences of the item.

    So the adjusted time in queue is =IF([@Use],[@[Time in Queue]],0).

    The helper columns could probably be replaced with an array formula. That would involve nesting 4 if statements for starters.
    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] Removing Duplicate value from each row
    By m.zaeim in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-30-2016, 12:40 PM
  2. vlookup: returns a duplicate of the value in the cell above instead of NA
    By JPN5804 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-21-2014, 01:27 PM
  3. [SOLVED] Removing Duplicate Lines.
    By Trevasaurus in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-09-2013, 05:25 PM
  4. removing unwanted duplicate lines
    By dperry in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-22-2009, 12:11 AM
  5. duplicate lines
    By davidan17 in forum Excel General
    Replies: 9
    Last Post: 01-01-2008, 08:03 PM
  6. Duplicate Lines
    By Mrbanner in forum Excel General
    Replies: 5
    Last Post: 11-11-2005, 12:45 AM
  7. [SOLVED] VLOOKUP with duplicate returns
    By dandigger in forum Excel General
    Replies: 4
    Last Post: 01-28-2005, 04:06 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