Identify duplicates

jsiason

Board Regular
Joined
Nov 9, 2005
Messages
72
Needing help..

In column F, id like to make a list of duplicate items found in columns C vs. D (not "per row" qualification). And in column G, id like to consolidate items in C and D but eliminating any duplicates.

Thank you!
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
jsiason.

i think this should work for you. let me know if you have any questions.

ben.
Code:
Sub HandlingDuplicates()

Dim List1 As Range, List2 As Range
Dim LoopList As Range, SearchList As Range
Dim DuplicateList As Range, UniqueList As Range
Dim StartRow As Long

StartRow = 1 '<--Change this to beginning of your lists

Set List1 = Range(Cells(StartRow, "C"), Cells(Rows.Count, "C").End(xlUp))
Set List2 = Range(Cells(StartRow, "D"), Cells(Rows.Count, "D").End(xlUp))
Set DuplicateList = Range("F1")
Set UniqueList = Range("G1")

DuplicateList.Value = "DUPLICATES"
UniqueList.Value = "UNIQUE"

If List1.Rows.Count > List2.Rows.Count Then
    Set LoopList = List2
    Set SearchList = List1
Else
    Set LoopList = List1
    Set SearchList = List2
End If

Application.ScreenUpdating = False

For Each c In LoopList.Cells
    If Application.WorksheetFunction.CountIf(SearchList, c.Value) >= 1 Then
        Set DuplicateList = DuplicateList.Offset(1, 0).Resize(1, 1)
        DuplicateList = c.Value
        'Set DuplicateList = DuplicateList.Resize(DuplicateList.Rows.Count + 1, 1)
    Else
       Set UniqueList = UniqueList.Offset(1, 0).Resize(1, 1)
       UniqueList = c.Value
    End If
Next c

Application.ScreenUpdating = True

End Sub
 

jsiason

Board Regular
Joined
Nov 9, 2005
Messages
72
hi ben, can this be done using function (and not using macros)?

easiest steps that will produce this output...

Also, in column G, i meant list unique values including those that appear in C and D, just that they will appear only once as one item in column G.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
jsiason said:
hi ben, can this be done using function (and not using macros)?

easiest steps that will produce this output...

Also, in column G, i meant list unique values including those that appear in C and D, just that they will appear only once as one item in column G.

Care to post a 10-row sample along with the expected output?
 

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657

ADVERTISEMENT

js.

is this what you mean?

ben.
(ignore &nsdf or whateveR)
book1
CDEFG
1LIST1LIST2COMBINEDUNIQUE
2aaaa
3bbab
4ccbc
5dxbd
6eyce
7fzcf
8gzdg
9ex
10fy
11gz
12x
13y
14z
15z
Sheet1
 

jsiason

Board Regular
Joined
Nov 9, 2005
Messages
72
C D E F G
a b e a
c d a b
e e i c
f g d
h a e
b i f
g
h
i


i hope i didnt confuse you.

If this can be done using a series of functions and without using macros, that will be great, ben.[/img]
 

jsiason

Board Regular
Joined
Nov 9, 2005
Messages
72

ADVERTISEMENT

oops, it didnt show correctly. hold on.
 

jsiason

Board Regular
Joined
Nov 9, 2005
Messages
72


how bout this?[/list]
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
If you download and install the free morefunc.xll add-in...
Book7
CDEFGH
1039
2List-1List-2IdxCommonListMerged Distinct List
3ab1aa
4cd eb
5ee2bc
6fg  d
7ha e
8bi3f
9g
10h
11i
12 
Sheet1


E1 must house a 0.

E3, copied down:

=IF(C3<>"",IF(ISNA(MATCH(C3,$C$2:C2,0))*ISNUMBER(MATCH(C3,$D$3:$D$8,0)),LOOKUP(9.99999999999999E+307,$E$1:E2)+1,""),"")

G1:

=LOOKUP(9.99999999999999E+307,E3:E8)

G3, copied down:

=IF(ROW()-ROW(G$3)+1<=$G$1,LOOKUP(ROW()-ROW(G$3)+1,$E$3:$E$8,$C$3:$C$8),"")

H1:

=COUNTDIFF(C3:D8)

Uses a function from morefunc.

H3, copied down:

=IF(ROW()-ROW($H$3)+1<=$H$1,INDEX(UNIQUEVALUES($C$3:$D$8,1),ROW()-ROW($H$3)+1),"")

Also uses a function from morefunc.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,455
Messages
5,572,235
Members
412,449
Latest member
mdvouf
Top