1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel 2007 Check for duplicate in several columns and return a value

Discussion in 'Business Applications' started by Gram123, Jan 27, 2011.

Thread Status:
Not open for further replies.
  1. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    On a monthly basis, we will receive a new data file.
    The user will copy the data from this file and paste it into a blank template file that I've set up. When the user pastes the new data into cell A2, he will then Save As, so as to preserve the original template file in its blank condition for use the following month.

    The template file basically consists of an Excel Table, so that the data range can expand, formuale within boilerplate columns will automatically calculate, and the range used by pivot tables in the workbook will all resize nicely.
    All going swimmingly so far.

    However, I've come across a stumbling block. Certain records have duplicate values in multiple (though not all) columns. Where this is the case, I need to display only one instance of the value in a new column.

    So, I have the following columns:

    A: Job ID
    B: Staff member
    C: Work Area
    E: Work Started
    F: Work Finished
    Q: Time Taken
    R: <my formuale result>

    with some other data in columns D and G thru P.

    I need either a formulae or VB code that will enter the following in column R:

    - For records (rows) where any of these values are non-duplicates, return the Time Taken.
    - For records where the values in all of those columns (A AND B AND C AND E AND F) are duplicated, only return the Time Taken for the first instance.

    In other words, say I had the following data:

    _____A_____B_____C_____E_____F____Q_____R
    1____ID_____NAME___AREA__STRT___END__TIME___CALC
    2____abc____JON____A01___10am___11am__1hr____1hr
    3____abc____JON____A01___10am___11am__1hr____
    4____abc____JON____A01___10am___11am__1hr____
    5____abc____JON____B01___10am___11am__1hr____1hr



    Then in column R, the value 1hr should be displayed in cell R2 (the first instance of the "duplicated" field).
    R3 and R4 should be blank (the second and third instances of the "duplicated" field).

    But if the values in any one of the columns A, B, C, E and F differ from other records in the data, then that's not considered a duplicate, so should display the Time Taken value.
    So R5 in the above example contains the value 1hr, because the (e.g.) Work Area for that row differs.


    Jeez, I hope this makes sense.
    Anyway, I've tried messing about with nested IFs, COUNTIFs and AND functions, soemthing like:

    =IF(AND(COUNTIF(A:A,A2)>1,(COUNTIF(B:B,B2)>1,(COUNTIF(C:C,C2)>1,(COUNTIF(E:E,E2)>1,(COUNTIF(F:F,F2)>1,"",Q2))
    But not that, cos it doesn't work...

    I don't mind if there's a VB code solution to this rather than complex nested IF formuale. The formulae cells (K thru R) are locked and the worksheet protected, so the user can only mess with the data side of it, not the calcs.

    Thanks in advance!
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Howdy stranger! :D

    So if you used:

    =A2&B2&C2&E2&F2

    in (say) S2 and down, you could then use:

    =IF(COUNTIF(S$2:S2,S2)=1,1/24,0)

    in R2 and down?


    Edit: 2nd should be =IF(COUNTIF(S$2:S2,S2)=1,G2,0)
     
  3. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Ah, spot on, mate! Why didn't I think of just concatenating the cells?! Excellent solution.

    I amended the duplicate checker line slightly, to return blanks instead of zeros:

    =IF(COUNTIF(S$2:S2,S2)=1,G2,"")

    Thank you!
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/977307

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice