Duplicates

[Karti]

Hi all,

I have a worksheet with 3 tabs:

DataOne - DataTwo - Duplicates

I am trying to copy duplicates from worksheet one and worksheet two to the duplicates worksheet.

If there are duplicates I would like them both transferred to the Duplicates worksheet so both lines can be compared. As part of this I would like the the whole rows to be copied across from Data One and Two

Any ideas are warmly welcomed

Regards

K

 

[doublej_jj]

Karti:

There has to be a lot of assumptions in your question as there isn't a lot of detail. However, I don't mind stepping out on a limb to see if I can help. Caution - This could get lengthy.

Assumption 1 - There is something in column A that would denote a duplicate.


Step 1 - On the DataOne tab (Out past your data) in row 2, try
=IF(ISERROR(MATCH(A2,DataTwo!$A$2:$A$1000,0)),"","Duplicate")
Copy down to all rows - Note the 1,000 is flexible, it needs to go to the bottom of your data.

Effect - The formula looks in Column A of the DataTwo tab for the value in the cell of column A on the DataOne tab. If it doesn't find a match, nothing happens (""), but if it does find a match, it marks it with a duplicate.

Step 2 - Next to the Duplicate column (assumed to be column J) created in Step 1, add a counting row
=if(J2="Duplicate",K1+1,K1)

Effect - Puts the next integer beside each new duplicate

Step 3 - On your duplicate tab, set up to import your duplicates
Column A is numbers from 1 to whatever you think you have duplicates to
Column B beside the 1 and then copied down
=INDEX(DataOne!$A$2:$A$1000,MATCH(A7,DataOne!$E$2:$E$1000,0),1)

Effect - In the DataOne Column A, it counts down to the row that was the integer beside the new duplicate.

Step 4 - Import the rest of the data using VLookup
In Column C - Bring DataTwo column A
=Vlookup($A2,DataTwo!$A$2:$I$1000,1,0)
In Column D - Bring DataOne column B
=Vlookup($A2,DataOne!$A$2:$I$1000,2,0)
In Column E - Bring DataTwo column B
=Vlookup($A2,DataTwo!$A$2:$I$1000,2,0)

Continue the Vlookups as far as you'd like.

Effect - Brings over the values from DataOne and DataTwo for comparison purposes.

So with that rambling, you have a solution. Depending on your experience, this may be very foreign. Try putting the formulas in to see them work and then strive to understand them. On the other hand, if all this makes sense right out of the gate, good luck!

Jeff

 

[Karti]

Jeff,

Many thanks for assisting. To give a better idea I have added a link to explain more:

I have the first tab with 15 records. The second tab has a futher set of records. I need to transfer the duplicates from DataOne and from DataTwo to Duplicates so that we can compare all the data in the duplicate range.

This is to see if mistakes have been found during an audit. By having different colours in the main tabs it allows us to view it in a more simpler mannor. I hope that helps a bit more.

Thanks for the help so far.

Regards

K

 

[scottylad2]

little bit of initial set up required but this works as per your sample data. Create the list of dups based on the formulas in K column, then create the vlookups based on the results

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Column1	Column2	Column3	Column4		Column1	Column2	Column3	Column4		Dups	Vlookups
2	1	a	b	c		5	a	b	c		5	5	a	b	c
3	2	a	b	c		6	a	b	c		6	5	a	b	c
4	3	a	b	c		7	a	b	c		7	6	a	b	c
5	4	a	b	c		8	a	b	c		8	6	a	b	c
6	5	a	b	c		9	a	b	c		9	7	a	b	c
7	6	a	b	c		10	a	b	c		10	7	a	b	c
8	7	a	b	c		11	x	y	z		11	8	a	b	c
9	8	a	b	c		12	x	y	z		12	8	a	b	c
10	9	a	b	c		13	x	y	z		13	9	a	b	c
11	10	a	b	c		14	x	y	z		14	9	a	b	c
12	11	a	b	c		15	x	y	z		15	10	a	b	c
13	12	a	b	c		16	x	y	z			10	a	b	c
14	13	a	b	c		17	x	y	z			11	a	b	c
15	14	a	b	c		18	x	y	z			11	x	y	z
16	15	a	b	c		19	x	y	z			12	a	b	c
17												12	x	y	z
18												13	a	b	c
19												13	x	y	z
20												14	a	b	c
21												14	x	y	z
22												15	a	b	c
23												15	x	y	z
Sheet3

Excel Workbook
	M
2	a
Sheet3

Excel Workbook
	M
3	a
Sheet3

hope you can make sense of the formulas, I've had to make them all on the same sheet for display purposes but you get the idea