Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: If formula to extract data ignoring duplicates

  1. #1

    If formula to extract data ignoring duplicates



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello Everyone,

    I have formula that works decently, I have it extracting across the row for about 15 columns. It extracts data from another sheet that has about 1200 rows, and some rows are duplicates.

    =IF(ISNUMBER(SEARCH($J$29,'Sheet1'!$S:$S)),'Sheet1'!A:A,"")

    But the problem is that it pulls completely duplicate rows which is what I'm trying to avoid. I want it to ignore duplicate rows only if it meets two match criteria's. The person's name is in column H and the shift is column J. I kinda hit a wall, and cant figure it out. I guess I may need < or > symbols possibly but not sure

    Any help would be greatly appreciated.


    MZING81

  2. #2
    This was cross posted at
    http://www.ozgrid.com/forum/showthre...161#post646161

    Quote Originally Posted by MZING81 View Post
    Hello Everyone,

    I have formula that works decently, I have it extracting across the row for about 15 columns. It extracts data from another sheet that has about 1200 rows, and some rows are duplicates.

    =IF(ISNUMBER(SEARCH($J$29,'Sheet1'!$S:$S)),'Sheet1'!A:A,"")

    But the problem is that it pulls completely duplicate rows which is what I'm trying to avoid. I want it to ignore duplicate rows only if it meets two match criteria's. The person's name is in column H and the shift is column J. I kinda hit a wall, and cant figure it out. I guess I may need < or > symbols possibly but not sure

    Any help would be greatly appreciated.


    MZING81

  3. #3
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    Can you post a sample workbook showing what you mean?


  4. #4
    I essentially just need to update the formula for unique data extraction, and base that uniqueness on criteria from two columns. There are some rows that are exactly the dame that I want to ignore, and some that are almost the same which I still need.
    Thank you

    hopefully the attachment can clarify things


    Book1.xlsx
    Quote Originally Posted by NBVC View Post
    Can you post a sample workbook showing what you mean?

  5. #5
    the formula in the intial post has been updated, and is completely different.

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    Try this.

    In E16:

    =IFERROR(INDEX(INDIRECT(E$15),MATCH(1,IF(LOCATION=$D$8,IF(ISNA(MATCH(INDIRECT(E$15),E$15:E15,0)),1)),0)),"")

    confirmed with cTRL+SHIFT+ENTER and copied down.

    Then in F16:

    =IF($E16="","",INDEX(INDIRECT(H$15),MATCH($E16,EMPLOYEE,0)))

    confirmed with ENTER only, then copied down and across the remaining columns.


  7. #7
    Thank you it almost worked. The only issue is that i have the same employee working two different shifts, and that is lost with the formula. Unless I missed something. For example take a look at employee 1. I used an aggregate formula, but it had multiple criteria, multiple data validations. Maybe I have to add another data validation that is hidden, and always has a selection of "*" visible.

    Quote Originally Posted by NBVC View Post
    Try this.

    In E16:

    =IFERROR(INDEX(INDIRECT(E$15),MATCH(1,IF(LOCATION=$D$8,IF(ISNA(MATCH(INDIRECT(E$15),E$15:E15,0)),1)),0)),"")

    confirmed with cTRL+SHIFT+ENTER and copied down.

    Then in F16:

    =IF($E16="","",INDEX(INDIRECT(H$15),MATCH($E16,EMPLOYEE,0)))

    confirmed with ENTER only, then copied down and across the remaining columns.

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    ok, let's add a helper column to Sheet1, to help segregate the information.

    in L12:

    Code:
    =IF(COUNTIFS(A$12:A12,A12,D$12:D12,D12,E$12:E12,E12,I$12:I12,I12,J$12:J12,J12,K$12:K12,K12)=1,K12&"_"&COUNTIF(L$11:L11,K12&"*")+1,"")
    copied down

    this identifies the location and cumulative count of unique information

    Then in Sheet2, E16:

    Code:
    =IFERROR(INDEX(INDIRECT(E$15),MATCH($D$8&"_"&ROWS(E$16:E16),Sheet1!$L$12:$L$69,0)),"")
    this is a regular formula, not an Array formula, so just confirm with ENTER

    then copy down and across the table.

    Note: I used the CODE tags here because my : D12 was resulting in a smiley face emoticon showing up.
    Last edited by NBVC; 2013-02-05 at 02:59 PM.


  9. #9
    Wow thank you that worked perfectly!!

    Quote Originally Posted by NBVC View Post
    ok, let's add a helper column to Sheet1, to help segregate the information.

    in L12:

    Code:
    =IF(COUNTIFS(A$12:A12,A12,D$12:D12,D12,E$12:E12,E12,I$12:I12,I12,J$12:J12,J12,K$12:K12,K12)=1,K12&"_"&COUNTIF(L$11:L11,K12&"*")+1,"")
    copied down

    this identifies the location and cumulative count of unique information

    Then in Sheet2, E16:

    Code:
    =IFERROR(INDEX(INDIRECT(E$15),MATCH($D$8&"_"&ROWS(E$16:E16),Sheet1!$L$12:$L$69,0)),"")
    this is a regular formula, not an Array formula, so just confirm with ENTER

    then copy down and across the table.

    Note: I used the CODE tags here because my : D12 was resulting in a smiley face emoticon showing up.

  10. #10
    I posted what that sheet looks like now. If its not too much trouble do mind taking a look at the addition I made to for time calculations. From reason the formula in M2:M14 is not working properly. I think it may be a formatting issue. I'm might just be overlooking something small.

    Thank you and anyone that can help.



    Book1.xlsx
    Quote Originally Posted by MZING81 View Post
    Wow thank you that worked perfectly!!

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •