How do I find duplicates between worksheets?

[OM2]

Say I have 20 worksheets in one Excel file.

I want to run a command that goes through all contents and tells me which items are repeated in more than one worksheet + tell me what worksheets these are.

Would I need VBA code to do this?
Is there anything built into Excel?

Thanks.


OM

[tuph]

Wow! This would be really useful to me also. Thanks for raising it OM.

[Iain170]

Have you discovered how to do this?

[FlameRetired]

OK.

This is intriguing. I had to jump in.

I limited the upload to 4 data sheets and a Summary sheet.

In all data sheets A1:E7 random numbers 100-300.

In the Summary sheet first table D4:G7 this formula

Please Login or Register  to view this content.

It outputs below.

	C	D	E	F	G
3		Sheet1	Sheet2	Sheet3	Sheet4
4	Sheet1		7	5	5
5	Sheet2	7		7	8
6	Sheet3	5	7		5
7	Sheet4	5	8	5

I only completed the first 3 sheet cross references to all 4 sheets in J4:X32.

The formula needs to be edited for absolute and relative addresses when copied and pasted into each sheet sub-section.

The formula to get the matching row numbers is array-entered in J4 and filled down to J12. It produces blanks for the first sub-section because Sheet1 intersects Sheet1. The formula is the same for all others.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

The next formula (K4:K12) returns the column numbers. It is array-entered also.

Please Login or Register  to view this content.

The next is not array entered. It returns the items. In L4:L12

Please Login or Register  to view this content.

I exceed the character limit posting a partial layout. I will post it in the following post.

[FlameRetired]

Pleases disregard this post and my previous. The "solution" is impractical for 20 sheets and very large ranges.


Here is that partial layout.


A partial layout looks like this.

Row\Col	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
2		Row #s	Column #s	Numbers		Row #s	Column #s	Numbers		Row #s	Column #s	Numbers		Row #s	Column #s	Numbers
3		Sheet1				Sheet2				Sheet3				Sheet4
4	Sheet1					1	2	250		1	4	153		1	5	107
5						1	5	107		2	4	179		2	5	123
6						2	2	210		5	1	286		3	3	112
7						3	2	142		6	1	281		4	4	165
8						4	5	127						6	5	157
9						7	5	136
10
11
12
13		Sheet1				Sheet2				Sheet3				Sheet4
14	Sheet2	1	2	188						2	3	279		1	5	243
15		3	1	105						2	4	249		2	4	249
16		3	2	142						3	1	105		3	1	105
17		4	5	127						4	2	262		4	5	127
18		5	2	208						4	4	186		5	1	166
19		5	5	291						4	5	127		6	1	160
20		6	2	265						7	1	206		6	3	230
21
22

[FlameRetired]

OM2,

How large is your largest range likely to be ... in numbers ... rows / columns?

[Iain170]

OM2,

How large is your largest range likely to be ... in numbers ... rows / columns?

My largest record has 14,212 rows

[FlameRetired]

Yikes !!!

Yup. I think someone is going to have to come up with an awesome VBA solution. That's out of my league.

[zbor]

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[snb]

I thought:

Please Login or Register  to view this content.

[Doc.AElstein]

Here is an additional solution along the lines of snb ideas..

A simplified code version
Sub AlanDupesSHimpfGlified()
is given here:
http://www.excelforum.com/showthread...t=#post4473229

A more detailed code version
Sub AlanDupesOverviewOverKill_GetTheDupesBackToYou()
is given here :
http://www.excelforum.com/showthread...t=#post4473231

_..................

Here, Post #11, and in next Post, # 12, is a description of the code, following the line numbers in the codes.

What this code actually does is give you a list out of any cells values anywhere in a Workbook which appear more than once along with the String reference to those Cells.

As a simplified example, consider a Workbook of three Worksheets , named “Sheet1” , Tabelle1” and “overview”

Using this sample data, _.......

Using Excel 2007 32 bit

Row\Col	A
1	a
2	b
3	d

Sheet1

Using Excel 2007 32 bit

Row\Col	A
1	a
2	b

Tabelle1

_...... running of the codes will get the following List back to you, ( in the worksheet with name “overview”) , the following concatenation indicating the Cell values which occur at least twice, and the String references to those cells:

Using Excel 2007 32 bit

Row\Col	A	B
1	The value "a" is in Cells _Sheet1!$A$1_Tabelle1!$A$1
2	The value "b" is in Cells _Sheet1!$A$2_Tabelle1!$A$2
3

overview

The basic Code description is given in next post:

[Doc.AElstein]

Description of codes referenced in last post:

Line 50 Rem 2) A Microsoft Scripting Runtime Dictionary, (MSRD), Object is used which delivers us at the end of this code section an Array, Itms(), looking basically of this form:
_Sheet1!$A$1_Tabelle1!$A$1
_Sheet1!$A$2_Tabelle1!$A$2
_Sheet1!$A$3
This Array is produced as follows:

Lines 70 and 80 Each Worksheet is considered ( 70 ), and for that Worksheet each Cell ( 80 ) in the Used Range in the worksheet is considered.

Line 120 Here we are at the central point of the loop and are considering, one at a time in each Loop, every non Empty cell from all Used ranges . A simple method is used to produce through this looping a set of values corresponding to unique cell values. That is to say a list of all cell values except duplicated values, the duplicates being ignored, that is to say only the first of Duplicated values are put in the List. The method uses a “By Product” of a MSRD Object . This Object will create a Key ( the identifying “part number” , as it were ) if you attempt to get at the Item via its Key value when, in fact that Key value does not yet exist. If that Key did exist, then it would not be made again. Hence in the Loop , line 120 simply tries to reference an Item by the Key value which is the Cell value. This is done for all non empty Cells consideredWe then end up with a set of Keys for each Unique Cell Value, that is to say the final list of the Keys, Keys(), ( which we may view via line 195 ) is a list of all cell values except duplicated values. In our case it produces a list like this
a b d
(_.... See here ( 5.3 method =.Item() )
http://www.snb-vba.eu/VBA_Dictionary_en.html#L_5
And here:
http://www.excelforum.com/showthread...15#post4423826
_....)

Line 130 We are still at the central part of the loop, considering each Cell ( in all Used Ranges ). The Item which has the Key value of the current cell value has the String Reference to that cell added ( concatenated ) to it. Hence when the Looping is finished the Item with each unique Key, a b and d, will have an Item which is a String containing each Cell reference ( with a “_” before it). So for the Key “a” , for example, we will have this form:
_Sheet1!$A$1_Tabelle1!$A$1

Line 190 The looping is complete and we produce a list ( Array, Itms() ) from the .Items() Property available to us from the MSRD Object.

Line 210 Rem 3) The VBA Collections Object
(_... http://www.snb-vba.eu/VBA_Collection_en.html ....._)
Is used to produce a simple list ( Array, arrout() ), which includes the concatenated String Cell reference List along with the unique cell value ( unique Key ) for those references. This is done as follows:

Loop in Lines 250 – 290 The basic idea is to simply loop through each of our Array Itms(), and only add a String Item of concatenated references to the collection ( along with the unique Cell value ( unique Key ) to which the references refer ) if there are two or more references for the cell value ( unique Key ) . This is done within this Loop as follows:
Line 260 using the VBA Strings Collection Split Method, such_....
Split(Itm, "_", -1)
_.... returns us a 1 Dimensional Array based on splitting a String such as _.......
_Sheet1!$A$1_Tabelle1!$A$1
_......... with _ as the separator, which has the form
Array(0) = ___ ( Empty ) , Array(1) = Sheet1!$A$1 , and Array(2) = Tabelle1!$A$1
(_..... http://www.mrexcel.com/forum/general...stops-%94.html _.....)

Line 260 This checks for more than one reference, which from the above example can be seen to be when the Upper Bound ( last Array indicia ) is greater than or equal to 2. If this is the case Then
Line 270 will add to the collection a String of the concatenated references, - ( the current Itm under consideration) BUT also add , at the beginning of that String , the Unique Key ( cell value ) , ( which for the above would be “a” .
To get, ( in this case “a” ), we can use either the reference in Array(1) or Array(2). ( we use in this case Array(1) for no particular reason ). _.......
_.....Explaining how the reference is used to obtain the actual Value is a bit tricky as we need to consider three different things as follows:
_1 ) A .Name Object for a Range
_2) A .Name Property of that .Name Object
_3) The String referrence to a Range , which is in some cases returned instead of the .Name Object, when we refer to that Object in a certain way.
So:......
We may reference a Range Object using .Range( STRING ) , where here .Range takes as its argument, STRING , a String. Usually we would use, for example to get at the first cell in a Worksheet
Range(“A1”) ( A1 is how we write in a Spreadsheet cell the reference to another cell. A “ “ pair is generally used in VBA to indicate that a text string is “coming” )
In fact we are referring here to a Named Range Object , which has the .Name Property of A1 ( as written in a cell ). When referenced, however, directly or where a String is expected, then a String Reference to the Range Object is returned , rather than an Object .
( An Object would be returned if we Set a variable via Set X = Range.Name, where X was declared by Dim X As Object.
Correspondingly then , X.Name or Range.Name.Name returns us the String Name .
Now the tricky bit: using the pair Dim Y As String: Let Y = Range.Name returns the string reference to the Range ( Not the String Name of that Name Object ) rather than the Name Object).
In our case , for A1 in the first Worksheet this bit “A1” returns not the object, but the String reference, by virtue of the " " pair which co oerces, as it were, a String, working in VBA similarly to Dim ___As String ( so we get the string reference to the cell, not the Name Property ) , in this example of this form:
“Sheet1!$A$1”
(_.... http://www.excelforum.com/showthread...t=#post4404807
http://www.excelforum.com/excel-prog...ml#post4440438
_....)
So we can use this directly instead of “A1”. For our case, :
= Range(“Sheet1!$A$1”)
= Range(Array(1))
= Range(Split(Itm, "_")(1))
We have now our Range Object. the value ( "a" in this example ), is retuned finally from the .Value property. Our Final complete part to obtain the value ( “a” in this case ), is Range(Split(Itm, "_")(1)).Value


Lines 300 – 360 '3b) We simply loop through each of our Collected Strings, ( conveniently referencing through the Collection Object Index Number ) , and add them to an Array , arrOut(), which is sized and orientated conveniently to allow us to _.......

_.... Line 380 Paste out the String Values in the Array , ( arrOut() ), directly in one go to the “overview” worksheet.

_............................

Alan
P.s. It may be wise to change the Separator I use ( _ ) to any character or character combination that you are sure you will never have as part of a cell value. You could for example use some obscure character,
http://www.mrexcel.com/forum/excel-q...ml#post4214083

[snb]

@Doc

You are right. I amended the code; especially: .Item(it.value).
Now it does what it should do.

[Doc.AElstein]

@Doc

You are right. I amended the code; especially: .Item(it.value).
Now it does what it should do.

Thanks snb.
I got the general idea anyway of what you were doing, which I found very neat, and which was the basis of my solution. I learnt some useful stuff here.
Alan