find duplicates in an array

[montecarlo2012]

Hello, I have a dynamic array 1 at (B1:F6845) and array 2 at (I6809:M6845), both are integers only; I would like to compare the array two against array one and highlight the duplicates in the array 2. and at the end of array two the amount of duplicates founded. like in N6846 the total of duplicates founded.


thanks.

 

[hiker95]

montecarlo2012,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Because of the number of rows, screenshots will not do.

So that we can get it right on the first try:

Can we see your workbook/worksheet with just the two ranges/arrays?

3. Are there columns to the immediate right of each array that I can use for a work area?

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[montecarlo2012]

Thanks, I am using excel 2010 on a pc. and after my first array there are two columns free G and H , any way I can move the second array anywhere if you want, the first one is too big to be moved. thanks. hiker95.

 

[hiker95]

montecarlo2012,

Thanks for the feedback. You are welcome.

there are two columns free G and H

That is great.

I can move the second array anywhere if you want, the first one is too big to be moved.

In order to assist you, and, so that we can get it right on the first try, can we see your workbook/worksheet with just the two ranges/arrays, in their correct/normal column position(s)?


You can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.

 

[montecarlo2012]

Sheet1

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
6827	12/2/2014	1	11	14	25	35
6828	12/3/2014	2	5	8	32	35
6829	12/4/2014	1	2	5	34	36			1	2	5	34	36	duplicate found=	4
6830	12/5/2014	9	11	12	18	28			9	11	12	18	28
6831	12/6/2014	10	15	23	29	31			10	15	23	29	31
6832	12/7/2014	1	11	14	16	21			1	11	14	16	21
6833	12/8/2014	5	10	14	26	27			6	10	14	26	27
6834	12/9/2014	3	9	14	16	18			7	9	14	16	18
6835	12/10/2014	1	8	10	30	34			8	8	10	30	34
6836	12/11/2014	5	10	11	20	36			9	10	11	20	36
6837	12/12/2014	3	11	18	29	31			10	11	18	29	31
6838	12/13/2014	7	21	23	27	28
6839	12/14/2014	4	14	15	25	26
6840	12/15/2014	1	13	18	27	34
6841	12/16/2014	1	5	22	26	30
6842	12/17/2014	3	14	17	23	26
6843	12/18/2014	2	10	16	22	31
6844	12/19/2014	3	6	21	29	31
6845	12/20/2014	4	6	7	20	30
6846	12/21/2014	7	10	16	23	24
6847

<colgroup><col style="width: 30px;"><col style="width: 114px;"><col style="width: 30px;"><col style="width: 30px;"><col style="width: 30px;"><col style="width: 30px;"><col style="width: 30px;"><col style="width: 64px;"><col style="width: 72px;"><col style="width: 30px;"><col style="width: 30px;"><col style="width: 30px;"><col style="width: 30px;"><col style="width: 30px;"><col style="width: 113px;"><col style="width: 30px;"><col style="width: 75px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[hiker95]

montecarlo2012,

The following is based on the raw data that you have displayed.

Sample raw data:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
6826
6827	12/2/2014	1	11	14	25	35
6828	12/3/2014	2	5	8	32	35
6829	12/4/2014	1	2	5	34	36			1	2	5	34	36	duplicate found=
6830	12/5/2014	9	11	12	18	28			9	11	12	18	28
6831	12/6/2014	10	15	23	29	31			10	15	23	29	31
6832	12/7/2014	1	11	14	16	21			1	11	14	16	21
6833	12/8/2014	5	10	14	26	27			6	10	14	26	27
6834	12/9/2014	3	9	14	16	18			7	9	14	16	18
6835	12/10/2014	1	8	10	30	34			8	8	10	30	34
6836	12/11/2014	5	10	11	20	36			9	10	11	20	36
6837	12/12/2014	3	11	18	29	31			10	11	18	29	31
6838	12/13/2014	7	21	23	27	28
6839	12/14/2014	4	14	15	25	26
6840	12/15/2014	1	13	18	27	34
6841	12/16/2014	1	5	22	26	30
6842	12/17/2014	3	14	17	23	26
6843	12/18/2014	2	10	16	22	31
6844	12/19/2014	3	6	21	29	31
6845	12/20/2014	4	6	7	20	30
6846	12/21/2014	7	10	16	23	24
6847
Sheet1

After the macro:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
6826
6827	12/2/2014	1	11	14	25	35
6828	12/3/2014	2	5	8	32	35
6829	12/4/2014	1	2	5	34	36			1	2	5	34	36	duplicate found=	4
6830	12/5/2014	9	11	12	18	28			9	11	12	18	28
6831	12/6/2014	10	15	23	29	31			10	15	23	29	31
6832	12/7/2014	1	11	14	16	21			1	11	14	16	21
6833	12/8/2014	5	10	14	26	27			6	10	14	26	27
6834	12/9/2014	3	9	14	16	18			7	9	14	16	18
6835	12/10/2014	1	8	10	30	34			8	8	10	30	34
6836	12/11/2014	5	10	11	20	36			9	10	11	20	36
6837	12/12/2014	3	11	18	29	31			10	11	18	29	31
6838	12/13/2014	7	21	23	27	28
6839	12/14/2014	4	14	15	25	26
6840	12/15/2014	1	13	18	27	34
6841	12/16/2014	1	5	22	26	30
6842	12/17/2014	3	14	17	23	26
6843	12/18/2014	2	10	16	22	31
6844	12/19/2014	3	6	21	29	31
6845	12/20/2014	4	6	7	20	30
6846	12/21/2014	7	10	16	23	24
6847
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub FindDuplicates()
' hiker95, 12/22/2014, ME825417
Dim lrb As Long, lri As Long
Dim c As Range, g As Range, n As Long
Application.ScreenUpdating = False
lrb = Cells(Rows.Count, "B").End(xlUp).Row
lri = Cells(Rows.Count, "I").End(xlUp).Row
With Range("G6827:G" & lrb)
  .Formula = "=B6827&C6827&D6827&E6827&F6827"
  .Value = .Value
End With
With Range("H6829:H" & lri)
  .Formula = "=I6829&J6829&K6829&L6829&M6829"
  .Value = .Value
End With
For Each c In Range("H6829:H" & lri)
  Set g = Range("G6827:G" & lrb).Find(c.Value, LookAt:=xlWhole)
  If Not g Is Nothing Then
    n = n + 1
    Range("I" & c.Row).Resize(, 5).Interior.Color = vbYellow
  End If
Next c
With Range("O6829")
  .Value = n
  .Interior.Color = vbYellow
End With
Range("G6827:G" & lrb).ClearContents
Range("H6829:H" & lri).ClearContents
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindDuplicates macro.

 

[montecarlo2012]

thank you hiker95
work perfect, I would like to find somebody like you, to get tutorials.
MARRY CHRISTMAS

 

[montecarlo2012]

Sorry about this, but I forgot the main point of this, work in that range of the example, but I really tried is to compare array1 (B2:to the end) against array2 , this second array is dynamic also, could be (I2: to the end) or could be just small as the example. in the same spat ofcourse. thanks.

 

[Peter_SSs]

work perfect,..

Are you sure?
With sample data as laid out in post #6, add the following values in row 6838 then run the code again

Col: Value
I: 11
J: 1
K: 14
L: 16
M: 21

Sorry about this, but I forgot the main point of this, work in that range of the example, but I really tried is to compare array1 (B2:to the end) against array2 , this second array is dynamic also, could be (I2: to the end) or could be just small as the example. in the same spat ofcourse. thanks.

Your earlier post seemed to indicate that the second array could go anywhere, and this one indicates data starting in row 2 so I'm assuming headings in row 1, at least in columns I:M

If that is the case, with the second array at the top of columns I:M as shown below, try this macro instead. The count of duplicate rows is shown in cell O1 but that could be anywhere.

Sub Find_Dupe_Rows()
  Dim lr As Long
  
  Application.ScreenUpdating = False
  lr = Range("B" & Rows.Count).End(xlUp).Row
  With Range("I1", Range("M" & Rows.Count).End(xlUp))
    .Cells(3, -1).Formula = Replace("=COUNTIFS(B$2:B$#,I2,C$2:C$#,J2,D$2:D$#,K2,E$2:E$#,L2,F$2:F$#,M2)", "#", lr)
    .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Cells(2, -1).Resize(2), Unique:=False
    .SpecialCells(xlVisible).Interior.ColorIndex = 27
    .Rows(1).Interior.ColorIndex = 0
    .Cells(1, 7).Value = .Columns(1).SpecialCells(xlVisible).Count - 1
    .Parent.ShowAllData
    .Cells(3, -1).ClearContents
  End With
  Application.ScreenUpdating = True
End Sub

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Col 2	Col 3	Col 4	Col 5	Col 6			Col 9	Col 10	Col 11	Col 12	Col 13
2	1	11	14	25	35			1	2	5	34	36
3	2	5	8	32	35			9	11	12	18	28
4	1	2	5	34	36			10	15	23	29	31
5	9	11	12	18	28			1	11	14	16	21
6	10	15	23	29	31			6	10	14	26	27
7	1	11	14	16	21			7	9	14	16	18
8	5	10	14	26	27			8	8	10	30	34
9	3	9	14	16	18			9	10	11	20	36
10	1	8	10	30	34			10	11	18	29	31
11	5	10	11	20	36			11	1	14	16	21
12	3	11	18	29	31			4	6	7	20	30
13	7	21	23	27	28
14	4	14	15	25	26
15	1	13	18	27	34
16	1	5	22	26	30
17	3	14	17	23	26
18	2	10	16	22	31
19	3	6	21	29	31
20	4	6	7	20	30
21	7	10	16	23	24
22
Before Code

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Col 2	Col 3	Col 4	Col 5	Col 6			Col 9	Col 10	Col 11	Col 12	Col 13		5
2	1	11	14	25	35			1	2	5	34	36
3	2	5	8	32	35			9	11	12	18	28
4	1	2	5	34	36			10	15	23	29	31
5	9	11	12	18	28			1	11	14	16	21
6	10	15	23	29	31			6	10	14	26	27
7	1	11	14	16	21			7	9	14	16	18
8	5	10	14	26	27			8	8	10	30	34
9	3	9	14	16	18			9	10	11	20	36
10	1	8	10	30	34			10	11	18	29	31
11	5	10	11	20	36			11	1	14	16	21
12	3	11	18	29	31			4	6	7	20	30
13	7	21	23	27	28
14	4	14	15	25	26
15	1	13	18	27	34
16	1	5	22	26	30
17	3	14	17	23	26
18	2	10	16	22	31
19	3	6	21	29	31
20	4	6	7	20	30
21	7	10	16	23	24
22
After Code

 

[hiker95]

montecarlo2012,

thank you hiker95
work perfect, MARRY CHRISTMAS

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

And, Happy Holidays, Merry Christmas, and, a Happy New Year.

I would like to find somebody like you, to get tutorials.

See if something, in the below link, of my most up to date list will help you:

Training / Books / Sites as of 11/22/2014

http://www.mrexcel.com/forum/genera...ns/820920-how-we-learn-excel.html#post4006402