If formula to extract data ignoring duplicates

MZING81

New member
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
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
 
This was cross posted at
http://www.ozgrid.com/forum/showthread.php?t=174312&p=646161#post646161

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


View attachment Book1.xlsx
Can you post a sample workbook showing what you mean?
 
the formula in the intial post has been updated, and is completely different.
 
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.
 
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.

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.
 
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:
Wow thank you that worked perfectly!!

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



View attachment Book1.xlsx
Wow thank you that worked perfectly!!
 
Change formula in P18 to:

=IF(H18="","",LEFT(H18,5)+0)

and in Q18:

=IF(H18="","",RIGHT(H18,5)+0)

I am not 100% sure what you are counting in the M2:M14 formulas?
 
Thank you this was a great help!

Change formula in P18 to:

=IF(H18="","",LEFT(H18,5)+0)

and in Q18:

=IF(H18="","",RIGHT(H18,5)+0)

I am not 100% sure what you are counting in the M2:M14 formulas?
 
Back
Top