Formula to Remove Duplicates

[Kandy1084]

Is there a formula that I can cover a large portion of a sheet in, that will return data from another sheet, minus rows containing duplicate values in columns A and B?
I know how to use the "remove duplicates" and this does achieve the same end result, however for the purpose I am needing a formula.
I've attached an example of the data (columns A:C) and the desired results (columns E:G).

Duplicates.xlsx

Thanks in advance!

[humdingaling]

by far the easiest way is to chuck it in a pivot table

secondary way of doing it would be with CSE formula for the first column and then just vlookup for the 2nd and 3rd
http://www.get-digital-help.com/2009...om-one-column/

Please Login or Register  to view this content.

[AlKey]

Enter this formula in E3 and copy it down

=IFERROR(INDEX(A$3:A$10,MATCH(0,INDEX(COUNTIF(E$2:E2,A$3:A$10),,),)),"")

Enter formula in F3 and pull formula to the right and then down. Format last column as Date.

=IFERROR(VLOOKUP($E3,$A$3:$C$10,COLUMNS($A:B),0),"")

[Kandy1084]

I should've mentioned that column A could contain a duplicate several times with a unique value in column B.
I would want each with a unique in B to be brought forward. Therefore I need something to verify that column A and B both are duplicates, to omit.
Please see below for a slightly better example of my data and desired result.
Thanks for the help


ABC123 J01000 1/1/2015 ABC123 J01000 1/1/15
ABC125 J01001 2/1/2015 ABC125 J01001 2/1/15
ABC125 J01004 3/1/2015 ABC125 J01004 3/1/15
ABC125 J01004 3/1/2015 ABC126 J01005 3/1/15
ABC126 J01005 3/1/2015 ABC127 J01002 4/1/15
ABC127 J01002 4/1/2015 ABC128 J01003 8/1/15
ABC127 J01002 4/1/2015
ABC128 J01003 8/1/2015

[AlKey]

Maybe this one with a helper column

in D3 and copy down

=COUNTIF(A$3:B3,B3)

Enter formula in E3 and pull formula to the right and then down. Format last column as Date.

=IFERROR(INDEX(A$3:A$10,SMALL(IF($D$3:$D$10=1,ROW($D$3:$D$10)-ROW($A$3)+1),ROWS(E$3:E3))),"")

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

Data Range

	A	B	C	D	E	F	G
1		Data				Result
2
3	ABC123	J01000	1/1/2015	1	ABC123	J01000	1/1/2015
4	ABC125	J01001	2/1/2015	1	ABC125	J01001	2/1/2015
5	ABC125	J01004	3/1/2015	1	ABC125	J01004	3/1/2015
6	ABC125	J01004	3/1/2015	2	ABC126	J01005	3/1/2015
7	ABC126	J01005	3/1/2015	1	ABC127	J01002	4/1/2015
8	ABC127	J01002	4/1/2015	1	ABC128	J01003	8/1/2015
9	ABC127	J01002	4/1/2015	2
10	ABC128	J01003	8/1/2015	1

[Czeslaw]

Could be so.

[Kandy1084]

Thanks AlKey this does work, any way to get around the helper column though?
I've attached a better example for ease.

DUPLICATES 2.xlsx

[Czeslaw]

And if such an option?