Identify duplicates

[jsiason]

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!

 

[sweater_vests_rock]

jsiason.

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

ben.

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]

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]

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]

js.

is this what you mean?

ben.
(ignore &nsdf or whateveR)

book1
	C	D	E	F	G
1	LIST1	LIST2		COMBINED	UNIQUE
2	a	a		a	a
3	b	b		a	b
4	c	c		b	c
5	d	x		b	d
6	e	y		c	e
7	f	z		c	f
8	g	z		d	g
9				e	x
10				f	y
11				g	z
12				x
13				y
14				z
15				z
Sheet1

 

[jsiason]

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]

oops, it didnt show correctly. hold on.

 

[jsiason]

how bout this?[/list]

 

[Aladin Akyurek]

If you download and install the free morefunc.xll add-in...

Book7
	C	D	E	F	G	H
1			0		3	9
2	List-1	List-2	Idx		CommonList	Merged Distinct List
3	a	b	1		a	a
4	c	d			e	b
5	e	e	2		b	c
6	f	g				d
7	h	a				e
8	b	i	3			f
9						g
10						h
11						i
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.