# Thread: If formula to extract data ignoring duplicates

1. ## If formula to extract data ignoring duplicates

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. This was cross posted at
http://www.ozgrid.com/forum/showthre...161#post646161

Originally Posted by MZING81
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. Can you post a sample workbook showing what you mean?

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
Originally Posted by NBVC
Can you post a sample workbook showing what you mean?

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

6. 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. 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.

Originally Posted by NBVC
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. 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.

9. Wow thank you that worked perfectly!!

Originally Posted by NBVC
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. 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
Originally Posted by MZING81
Wow thank you that worked perfectly!!

Page 1 of 2 1 2 Last

#### Posting Permissions

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