+ Reply to Thread
Results 1 to 5 of 5

Count single records when duplicate records exist

  1. #1
    Registered User
    Join Date
    01-10-2013
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Count single records when duplicate records exist

    Hi All! Need help on my excel sheet.. I have an excel sheet contains list of projects.. where column A contains the job number, column B is the category, column C is the percentage of completion. Can somebody suggest a formula which will count the number of latest project proposals wherein it will only count the latest for a duplicate entry. (e.a. BI-10-01902). Tried using countifs but realize there were duplicate entries and cannot remove duplicate entries as I need for history purpsoses.

    BI-10-01906 DBSP - Draft Review 22-Jan-14 20-Feb-14
    BI-10-01901 DBSP - Draft Review 6-Feb-14 26-Feb-14
    BI-10-01902 Project Proposal 60% eReview 24-Feb-14 16-Mar-14
    BI-10-01904 Project Proposal 60% eReview 9-Mar-14 30-Mar-14
    BI-10-01902 Project Proposal 90% eReview 9-Jan-14 30-May-14
    BI-28-10014 Detail Design 60% eReview 29-Jan-14 25-Feb-14
    BI-10-01902 Detail Design 60% eReview 27-Mar-14 16-Apr-14
    BI-10-00886 DBSP - Approval 13-Apr-14 1-May-14
    BI-10-01906 DBSP - Approval 22-Apr-14 13-May-14
    BI-25-00010 Project Proposal 60% eReview 21-Apr-14 15-May-14
    BI-10-01906 Project Proposal 60% eReview 27-Apr-14 8-May-14
    BI-10-01257 Detail Design 90% eReview 28-Jan-14 24-Feb-14
    BI-10-01427 Project Proposal 90% eReview 16-Apr-14 6-May-14
    BI-10-01246 Project Proposal 30% eReview 15-Jan-14 13-Feb-14
    BI-10-00996 DBSP - Approval 26-Dec-13 22-Jan-14
    BI-10-01427 Detail Design 60% eReview 12-Mar-14 1-Apr-14
    BI-10-00982 Detail Design 60% eReview 20-Jan-14 9-Feb-14
    BI-10-01823 DBSP - Draft Review 11-May-14 29-May-14
    BI-10-01823 DBSP - Approval 6-Feb-14 4-Mar-14
    BI-10-01638 Project Proposal 90% eReview 4-Feb-14 25-Feb-14
    BI-10-01824 DBSP - Draft Review 25-May-14 12-Jun-14
    BI-10-01823 Project Proposal 60% eReview 25-May-14 12-Jun-14
    BI-10-01467 Project Proposal 30% eReview 6-Feb-14 4-Mar-14

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,786

    Re: Count single records when duplicate records exist

    Hi,

    you could try:


    Please Login or Register  to view this content.
    Formula to be confirmed with control+shift+enter.

    Regards
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    01-10-2013
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Count single records when duplicate records exist

    Sorry Canapone.. your suggestion works like a charm but I should have provided more details on what I need. Each project goes thru certain categories with the initial stage as DBSP, 2nd is project proposal and lastly detail design. I need to break breakdown the list into number of DBSPs, # of project proposals and # of detail designs wherein the project should not be counted as DBSP if it has already a project proposal or a should not be counted as project proposal if it already has a detail design. Should I use the Ereview end date as additional criteria? I attached a copy of the sheet to give you a better view.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,786

    Re: Count single records when duplicate records exist

    Hi,

    in B32 confirmed with control+shift +enter (maybe)


    =SUM(IF(FREQUENCY(IF(B2:B24="DBSP",IF(D2:D24="Approval",MATCH(A2:A24,A2:A24,0))),ROW(A1:A23)),1))

    I have not undestood filter I have to add to formula in C32

  5. #5
    Registered User
    Join Date
    01-10-2013
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Count single records when duplicate records exist

    Hi canapone, been on vacation , seems the formula you suggested counts the DBSP on approval stage but if you filter the list to BI-10-1823 you will notice that is on proposal stage already hence should no longer be counted on the DBSP column but on the project proposal column.. same goes for project proposal, it should no longer be counted on the project proposal if it has a detail design already.. the latest stage of the BI should be counted.. Little bit tricky when you also consider the cycle stage, 30, 60 & 90%...Thank you.

+ 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] VBA to rearrange 11000 records into 550 rows (20 records combined into a single row)
    By Wedge120 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-27-2014, 11:56 PM
  2. Replies: 1
    Last Post: 04-19-2012, 11:43 AM
  3. Merge duplicate records in single record
    By pethaa1791 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-17-2008, 02:34 PM
  4. Merging records / creating new columns based on duplicate records
    By duklaprague in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-19-2007, 10:32 AM
  5. Replies: 0
    Last Post: 07-19-2007, 02:58 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