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
 

MZING81

New member
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
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
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Can you post a sample workbook showing what you mean?
 

MZING81

New member
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
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?
 

MZING81

New member
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
the formula in the intial post has been updated, and is completely different.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 

MZING81

New member
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
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.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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:

MZING81

New member
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
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.
 

MZING81

New member
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
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!!
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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?
 

MZING81

New member
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
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?
 
Top