Comparing two worksheets to find the match including the duplicates

[nike]

Hi,

I have two worksheets that i'd like to compare the IDs to find the match including the duplicates. IDs in Sheet1 will be compared to a queried data with IDs in Sheet2. If it's possible if there's a code that can make this compare, create a tab labeled Matched including the duplicates, another tab labeled No match IDs from Sheet1 that didn't match to Sheet2, and lastly a tab that matched the IDs but has no duplicate IDs. Thank you!

Here's an example of the worksheets.

SHEET1
IDS

*	A
1	ID #
2	0188370
3	0283750
4	0299950
5	0316740
6	0416500
7	0538620
8	0543610
9	0554670
10	0556410
11	0572240
12	0724270

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

SHEET2

TOAD QUERY

*	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ID #	YR	TOS	BR	CO	TERM	STATUS	WAGE 1	WAGE 2	WAGE_3	TAX 1	TAX 2	YTD	YTD 2	YTD 3	YTD 4	YTD 5
2	0188370	06	00000	46	S26		A	33225.5	0	22136.5	0	0	0	0	32503.02	22136.5	0
3	0188370	06	00000	46	A01		A	10.2	0	10.2	0	0	0	0	25332	25332	0
4	0188370	06	00000	47	A02		A	2.5	0	2.5	0	0	0	0	23.2	23.2	0
5	0188370	06	00000	48	A01		A	232	0	232	0	0	0	0	2211	2211	0
6	0188370	06	00000	45	A01		A	2123	0	2123	0	0	0	0	22235	22235	0
7	0283750	13	33333	BS	J0X		A	0	0	0	0	0	0	0	0	0	0
8	0283750	13	33333	CS	J0X		A	0	0	0	0	0	0	0	0	0	0
9	0299950	14	33333	72	9HQ		A	37961.53	0	9500	0	256.5	0	0	37189.39	9500	0
10	0316740	14	33333	RW	MVG		A	11538.42	0	9500	0	256.5	0	0	11538.42	9500	0
11	0416500	14	33333	46	SCB		A	0	0	0	0	0	0	0	0	0	0
12	0538620	13	00000	A0	DN3	F	I	0	0	0	0	0	0	0	0	0	0
13	0538620	13	00000	A0	DN3	F	I	0	0	0	0	0	0	0	0	0	0
14	0538620	13	00000	A0	DN3	F	I	0	0	0	0	0	0	0	0	0	0
15	0543610	13	00000	70	WWD		A	5949.32	0	0	0	0	0	0	0	0	0
16	0543610	13	00000	70	WWD		A	5949.32	0	0	0	0	0	0	0	0	0
17	0554670	10	03300	ST	NML6		A	0	0	0	0	0	0	0	0	0	0
18	0556410	11	03333	72	MSV		A	0	0	0	0	0	0	0	0	0	0
19	0572240	10	33333	80	AQD		I	0	0	0	0	0	*	*	*	*	*
20	0724270	14	33333	R2	I34		A	3333.34	0	3333.34	0	90	0	0	3333.34	3333.34	0
21	0724610 *	11	00333	15	3ML		A	388389.81	0	251990.86	0	6803.75	0	0	394163.06	251990.86	0
22	0724610 * *	14	33333	15	E2X		A	17561.48	0	9500	0	256.5	0	0	17561.48	9500	0
23	0724680 *	14	99999	15	9O2	T	A	0	0	0	0	0	*	*	*	*	*
24	APPLFOR042546583	14	03333	46	FH2		A	0	0	0	0	0	*	*	*	*	*
25	APPLFOR043103733	09	30000	46	C4C		A	15759	0	0	0	0	0	0	0	0	0

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 62px"><COL style="WIDTH: 23px"><COL style="WIDTH: 47px"><COL style="WIDTH: 28px"><COL style="WIDTH: 51px"><COL style="WIDTH: 41px"><COL style="WIDTH: 64px"><COL style="WIDTH: 81px"><COL style="WIDTH: 67px"><COL style="WIDTH: 81px"><COL style="WIDTH: 41px"><COL style="WIDTH: 74px"><COL style="WIDTH: 35px"><COL style="WIDTH: 46px"><COL style="WIDTH: 79px"><COL style="WIDTH: 79px"><COL style="WIDTH: 41px"></COLGROUP><TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

 

[nike]

Hi hiker95,

I uploaded an unprotected workbook (i hope i did it right) https://app.box.com/files/0/f/0 The worksheet names won't have a single trailing space character, i kind put it there by accident when renaming the tab. Also this workbook will always have these tab names. Thanks!

 

[hiker95]

nike,

Your link is asking for a user name/e-mail and password.

Please try again.

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.

 

[nike]

Hi hiker95,

Here's the new link https://app.box.com/s/vlc2hqy8qzsrfy9j55r0 sorry for the trouble, it was a long day for me

 

[hiker95]

nike,

I can not work with your latest workbook:
1. locked cells
2. two worksheet names contain a trailing space character

So, I copied raw data from your earlies screenshots, and, used the new worksheets naming convention.


Sample two raw data wowrksheets:

Excel Workbook
	A
1	ID #
2	188370
3	283750
4	299950
5	316740
6	416500
7	538620
8	543610
9	554670
10	556410
11	572240
12	724270
13	123456
14	254689
15	212542
16	254686
17
IDS

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ID #	YR	TOS	BR	CO	TERM	STATUS	WAGE 1	WAGE 2	WAGE_3	TAX 1	TAX 2	YTD	YTD 2	YTD 3	YTD 4	YTD 5
2	188370	6	0	46	S26		A	33225.5	0	22136.5	0	0	0	0	32503.02	22136.5	0
3	188370	6	0	46	A01		A	10.2	0	10.2	0	0	0	0	25332	25332	0
4	188370	6	0	47	A02		A	2.5	0	2.5	0	0	0	0	23.2	23.2	0
5	188370	6	0	48	A01		A	232	0	232	0	0	0	0	2211	2211	0
6	188370	6	0	45	A01		A	2123	0	2123	0	0	0	0	22235	22235	0
7	283750	13	33333	BS	J0X		A	0	0	0	0	0	0	0	0	0	0
8	283750	13	33333	CS	J0X		A	0	0	0	0	0	0	0	0	0	0
9	299950	14	33333	72	9HQ		A	37961.53	0	9500	0	256.5	0	0	37189.39	9500	0
10	316740	14	33333	RW	MVG		A	11538.42	0	9500	0	256.5	0	0	11538.42	9500	0
11	416500	14	33333	46	SCB		A	0	0	0	0	0	0	0	0	0	0
12	538620	13	0	A0	DN3	F	I	0	0	0	0	0	0	0	0	0	0
13	538620	13	0	A0	DN3	F	I	0	0	0	0	0	0	0	0	0	0
14	538620	13	0	A0	DN3	F	I	0	0	0	0	0	0	0	0	0	0
15	543610	13	0	70	WWD		A	5949.32	0	0	0	0	0	0	0	0	0
16	543610	13	0	70	WWD		A	5949.32	0	0	0	0	0	0	0	0	0
17	554670	10	3300	ST	NML6		A	0	0	0	0	0	0	0	0	0	0
18	556410	11	3333	72	MSV		A	0	0	0	0	0	0	0	0	0	0
19	572240	10	33333	80	AQD		I	0	0	0	0	0	*	*	*	*	*
20	724270	14	33333	R2	I34		A	3333.34	0	3333.34	0	90	0	0	3333.34	3333.34	0
21	0724610 *	11	333	15	3ML		A	388389.81	0	251990.86	0	6803.75	0	0	394163.06	251990.86	0
22	0724610 * *	14	33333	15	E2X		A	17561.48	0	9500	0	256.5	0	0	17561.48	9500	0
23	0724680 *	14	99999	15	9O2	T	A	0	0	0	0	0	*	*	*	*	*
24	APPLFOR042546583	14	3333	46	FH2		A	0	0	0	0	0	*	*	*	*	*
25	APPLFOR043103733	9	30000	46	C4C		A	15759	0	0	0	0	0	0	0	0	0
26
TOAD QUERY

After the macro in the three other worksheets:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	SHEET1 ID#	ID #	YR	TOS	BR	CO	TERM	STATUS	WAGE 1	WAGE 2	WAGE_3	TAX 1	TAX 2	YTD	YTD 2	YTD 3	YTD 4	YTD 5
2	188370	188370	6	0	46	S26		A	33225.5	0	22136.5	0	0	0	0	32503.02	22136.5	0
3	188370	188370	6	0	46	A01		A	10.2	0	10.2	0	0	0	0	25332	25332	0
4	188370	188370	6	0	47	A02		A	2.5	0	2.5	0	0	0	0	23.2	23.2	0
5	188370	188370	6	0	48	A01		A	232	0	232	0	0	0	0	2211	2211	0
6	188370	188370	6	0	45	A01		A	2123	0	2123	0	0	0	0	22235	22235	0
7	283750	283750	13	33333	BS	J0X		A	0	0	0	0	0	0	0	0	0	0
8	283750	283750	13	33333	CS	J0X		A	0	0	0	0	0	0	0	0	0	0
9	538620	538620	13	0	A0	DN3	F	I	0	0	0	0	0	0	0	0	0	0
10	538620	538620	13	0	A0	DN3	F	I	0	0	0	0	0	0	0	0	0	0
11	538620	538620	13	0	A0	DN3	F	I	0	0	0	0	0	0	0	0	0	0
12	543610	543610	13	0	70	WWD		A	5949.32	0	0	0	0	0	0	0	0	0
13	543610	543610	13	0	70	WWD		A	5949.32	0	0	0	0	0	0	0	0	0
14
MATCHED ALL

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	SHEET1 ID#	ID #	YR	TOS	BR	CO	TERM	STATUS	WAGE 1	WAGE 2	WAGE_3	TAX 1	TAX 2	YTD	YTD 2	YTD 3	YTD 4	YTD 5
2	299950	299950	14	33333	72	9HQ		A	37961.53	0	9500	0	256.5	0	0	37189.39	9500	0
3	316740	316740	14	33333	RW	MVG		A	11538.42	0	9500	0	256.5	0	0	11538.42	9500	0
4	416500	416500	14	33333	46	SCB		A	0	0	0	0	0	0	0	0	0	0
5	554670	554670	10	3300	ST	NML6		A	0	0	0	0	0	0	0	0	0	0
6	556410	556410	11	3333	72	MSV		A	0	0	0	0	0	0	0	0	0	0
7	572240	572240	10	33333	80	AQD		I	0	0	0	0	0	*	*	*	*	*
8	724270	724270	14	33333	R2	I34		A	3333.34	0	3333.34	0	90	0	0	3333.34	3333.34	0
9
MATCHED NO DUP

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	SHEET1 ID#	ID #	YR	TOS	BR	CO	TERM	STATUS	WAGE 1	WAGE 2	WAGE_3	TAX 1	TAX 2	YTD	YTD 2	YTD 3	YTD 4	YTD 5
2	123456
3	254689
4	212542
5	254686
6
NO MATCH

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 CompareIDS()
' hiker95, 04/19/2014, ME772155
Dim wids As Worksheet, wtq As Worksheet, wma As Worksheet, wmnd As Worksheet, wnm As Worksheet
Dim c As Range, irng As Range, n As Long, nr As Long
Application.ScreenUpdating = False
Set wids = Sheets("IDS")
Set wtq = Sheets("TOAD QUERY")
Set wma = Sheets("MATCHED ALL")
Set wmnd = Sheets("MATCHED NO DUP")
Set wnm = Sheets("NO MATCH")

With wids
  For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    
    '*** "TOAD QUERY" = wtq
    Set irng = wtq.Columns(1).Find(c, LookAt:=xlWhole)
    If irng Is Nothing Then
    
      '*** "NO MATCH" = wnm
      nr = wnm.Cells(wnm.Rows.Count, "A").End(xlUp).Row + 1
      c.Copy wnm.Range("A" & nr)
    
    ElseIf Not irng Is Nothing Then
      n = Application.CountIf(wtq.Columns(1), c)
      
      '*** "MATCHED NO DUP" = wmnd
      If n = 1 Then
        nr = wmnd.Cells(wnm.Rows.Count, "A").End(xlUp).Row + 1
        c.Copy wmnd.Range("A" & nr)
        wtq.Range("A" & irng.Row).Resize(, 17).Copy wmnd.Range("B" & nr).Resize(, 17)
        Application.CutCopyMode = False
        
      '*** "MATCHED ALL" = wma
      ElseIf n > 1 Then
        nr = wma.Cells(wnm.Rows.Count, "A").End(xlUp).Row + 1
        c.Copy wma.Range("A" & nr).Resize(n)
        wtq.Range("A" & irng.Row).Resize(n, 17).Copy wma.Range("B" & nr).Resize(n, 17)
        Application.CutCopyMode = False
      End If
    End If
    Set irng = Nothing
  Next c
End With
wma.Columns.AutoFit
wmnd.Columns.AutoFit
wnm.Columns.AutoFit
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 CompareIDS macro.

 

[hiker95]

nike,

So, I copied raw data from your earlies screenshots, and, used the new worksheets naming convention - without trailing space characters.


Here is a screenshot showing where all the data came from, and, where it was copied to:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	IDS		TOAD QUERY		MATCHED ALL		MATCHED NO DUP		NO MATCH
2	ID #		ID #		SHEET1 ID#		SHEET1 ID#		SHEET1 ID#
3	188370		188370		188370
4			188370		188370
5			188370		188370
6			188370		188370
7			188370		188370
8	283750		283750		283750
9			283750		283750
10	299950		299950				299950
11	316740		316740				316740
12	416500		416500				416500
13	538620		538620		538620
14			538620		538620
15			538620		538620
16	543610		543610		543610
17			543610		543610
18	554670		554670				554670
19	556410		556410				556410
20	572240		572240				572240
21	724270		724270				724270
22	123456								123456
23	254689								254689
24	212542								212542
25	254686								254686
26			0724610 *
27			0724610 * *
28			0724680 *
29			APPLFOR042546583
30			APPLFOR043103733
31
Results

 

[nike]

Hi hiker95,

Thank you so much for taking the time to help me with this! I just have one issue with the macro. I've tested it and it works great, but for "MATCHED ALL" tab, it's not including the IDs that only has one match, basically it's those IDs that's on tab "MATCHED NO DUP". If possible if even it has only one match to include in the "MATCHED ALL" tab. I really appreciate your help!

 

[hiker95]

nike,

basically it's those IDs that's on tab "MATCHED NO DUP". If possible if even it has only one match to include in the "MATCHED ALL" tab.

Then why do you have a "MATCHED NO DUP" worksheet?

 

[nike]

Hi,

It's because the "MATCHED ALL" will all have to be totaled their dollar amount including the IDs that matched and it's duplicates. The "MATCHED NO DUP" will go to another department to be added to their database, which has to be the only unique Ids that matched, the reason why i need to be separate them.

 

[hiker95]

nike,

One last try.

What is your logic for putting information into worksheet "MATCHED NO DUP"?

 

[nike]

Hi,

For "MATCHED NO DUP", (this is basically for the other department) The logic is to have a unique ID that matched my department's query. It doesn't really matter the information that is attached to this ID. I've asked this question too to the other department, but i was told they need only the unique ID that matched or it'll be alot of manual work sorting out only the unique ID. This report that i'm working on has thousands of IDs and it's monthly report. Basically for my department all i need is the Matched all (including all the duplicates and unique Ids that matched) and No match. If it's too much work, we can just eliminate the "MATCHED NO DUP". Sorry for all the troubles.