Comparing two worksheets to find the match including the duplicates

nike

Board Regular
Joined
Feb 12, 2008
Messages
109
Office Version
  1. 365
Platform
  1. Windows
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
1ID #
20188370
30283750
40299950
50316740
60416500
70538620
80543610
90554670
100556410
110572240
120724270

<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

*ABCDEFGHIJKLMNOPQ
1ID #YRTOSBRCOTERMSTATUSWAGE 1WAGE 2WAGE_3TAX 1TAX 2YTD YTD 2YTD 3YTD 4YTD 5
20188370060000046S26 A33225.5022136.5000032503.0222136.50
30188370060000046A01A10.2010.2000025332253320
40188370060000047A02A2.502.5000023.223.20
50188370060000048A01A23202320000221122110
60188370060000045A01A212302123000022235222350
702837501333333BSJ0X A0000000000
802837501333333CSJ0X A0000000000
902999501433333729HQ A37961.53095000256.50037189.3995000
1003167401433333RWMVG A11538.42095000256.50011538.4295000
110416500143333346SCB A0000000000
1205386201300000A0DN3 FI0000000000
1305386201300000A0DN3 FI0000000000
1405386201300000A0DN3 FI0000000000
150543610130000070WWD A5949.32000000000
160543610130000070WWD A5949.32000000000
1705546701003300STNML6A0000000000
180556410110333372MSV A0000000000
190572240103333380AQD I00000*****
2007242701433333R2I34 A3333.3403333.34090003333.343333.340
210724610 * 1100333153ML A388389.810251990.8606803.7500394163.06251990.860
220724610 * * 143333315E2X A17561.48095000256.50017561.4895000
230724680 *1499999159O2 TA00000*****
24APPLFOR042546583 140333346FH2 A00000*****
25APPLFOR043103733 093000046C4C A15759000000000

<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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

nike

Board Regular
Joined
Feb 12, 2008
Messages
109
Office Version
  1. 365
Platform
  1. Windows
By the way, i'm using microsoft excel 2010 with Windows 7.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
nike,

Thanks for the Private Message.

Sheet1 name = IDS
Sheet2 name = TOAD QUERY

So that we can get it right the first time, can we have screenshots of what the results should look like (manually formatted by you) in worksheets Matched, and, No Dupe IDs?
 

nike

Board Regular
Joined
Feb 12, 2008
Messages
109
Office Version
  1. 365
Platform
  1. Windows
Thanks for the prompt reply hiker95! Here's the screenshots i've manually compared.


Excel Workbook
ABCDEFGHIJKLMNOPQR
1SHEET1 ID#ID #YRTOSBRCOTERMSTATUSWAGE 1WAGE 2WAGE_3TAX 1TAX 2YTDYTD 2YTD 3YTD 4YTD 5
21883701883706046S26A33225.5022136.5000032503.0222136.50
31883701883706046A01A10.2010.2000025332253320
41883701883706047A02A2.502.5000023.223.20
51883701883706048A01A23202320000221122110
61883701883706045A01A212302123000022235222350
72837502837501333333BSJ0XA0000000000
82837502837501333333CSJ0XA0000000000
92999502999501433333729HQA37961.53095000256.50037189.3995000
103167403167401433333RWMVGA11538.42095000256.50011538.4295000
11416500416500143333346SCBA0000000000
12538620538620130A0DN3FI0000000000
13538620538620130A0DN3FI0000000000
14538620538620130A0DN3FI0000000000
1554361054361013070WWDA5949.32000000000
1654361054361013070WWDA5949.32000000000
17554670554670103300STNML6A0000000000
1855641055641011333372MSVA0000000000
19572240572240103333380AQDI00000*****
207242707242701433333R2I34A3333.3403333.34090003333.343333.340
MATCHED WITH DUPS


Excel Workbook
ABCDEFGHIJKLMNOPQR
1SHEET1 ID#ID #YRTOSBRCOTERMSTATUSWAGE 1WAGE 2WAGE_3TAX 1TAX 2YTDYTD 2YTD 3YTD 4YTD 5
21883701883706046S26A33225.5022136.5000032503.0222136.50
32837502837501333333BSJ0XA0000000000
42999502999501433333729HQA37961.53095000256.50037189.3995000
53167403167401433333RWMVGA11538.42095000256.50011538.4295000
6416500416500143333346SCBA0000000000
7538620538620130A0DN3FI0000000000
854361054361013070WWDA5949.32000000000
9554670554670103300STNML6A0000000000
1055641055641011333372MSVA0000000000
11572240572240103333380AQDI00000*****
127242707242701433333R2I34A3333.3403333.34090003333.343333.340
MATCHED NO DUPS


Excel Workbook
ABCDEFGHIJKLMNOPQR
1SHEET1 ID#ID #YRTOSBRCOTERMSTATUSWAGE 1WAGE 2WAGE_3TAX 1TAX 2YTDYTD 2YTD 3YTD 4YTD 5
20724610 *11333153MLA388389.80251990.906803.7500394163.1251990.90
30724610 * *143333315E2XA17561.48095000256.50017561.4895000
40724680 *1499999159O2TA00000*****
5APPLFOR04254658314333346FH2A00000*****
6APPLFOR04310373393000046C4CA15759000000000
NO MATCH
 

nike

Board Regular
Joined
Feb 12, 2008
Messages
109
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Here's a better screenshot...


MATCHED WITH DUPS

*ABCDEFGHIJKLMNOPQR
1SHEET1 ID#ID #YRTOSBRCOTERMSTATUSWAGE 1WAGE 2WAGE_3TAX 1TAX 2YTDYTD 2YTD 3YTD 4YTD 5
21883701883706046S26*A33225.5022136.5000032503.0222136.50
31883701883706046A01*A10.2010.2000025332253320
41883701883706047A02*A2.502.5000023.223.20
51883701883706048A01*A23202320000221122110
61883701883706045A01*A212302123000022235222350
72837502837501333333BSJ0X*A0000000000
82837502837501333333CSJ0X*A0000000000
92999502999501433333729HQ*A37961.53095000256.50037189.3995000
103167403167401433333RWMVG*A11538.42095000256.50011538.4295000
11416500416500143333346SCB*A0000000000
12538620538620130A0DN3FI0000000000
13538620538620130A0DN3FI0000000000
14538620538620130A0DN3FI0000000000
1554361054361013070WWD*A5949.32000000000
1654361054361013070WWD*A5949.32000000000
17554670554670103300STNML6*A0000000000
1855641055641011333372MSV*A0000000000
19572240572240103333380AQD*I00000*****
207242707242701433333R2I34*A3333.3403333.34090003333.343333.340

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


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

MATCHED NO DUPS

*ABCDEFGHIJKLMNOPQR
1SHEET1 ID#ID #YRTOSBRCOTERMSTATUSWAGE 1WAGE 2WAGE_3TAX 1TAX 2YTDYTD 2YTD 3YTD 4YTD 5
21883701883706046S26*A33225.5022136.5000032503.0222136.50
32837502837501333333BSJ0X*A0000000000
42999502999501433333729HQ*A37961.53095000256.50037189.3995000
53167403167401433333RWMVG*A11538.42095000256.50011538.4295000
6416500416500143333346SCB*A0000000000
7538620538620130A0DN3FI0000000000
854361054361013070WWD*A5949.32000000000
9554670554670103300STNML6*A0000000000
1055641055641011333372MSV*A0000000000
11572240572240103333380AQD*I00000*****
127242707242701433333R2I34*A3333.3403333.34090003333.343333.340

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


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

NO MATCH

*ABCDEFGHIJKLMNOPQR
1SHEET1 ID#ID #YRTOSBRCOTERMSTATUSWAGE 1WAGE 2WAGE_3TAX 1TAX 2YTDYTD 2YTD 3YTD 4YTD 5
2*0724610 *11333153ML*A388389.80251990.906803.7500394163.1251990.90
3*0724610 * *143333315E2X*A17561.48095000256.50017561.4895000
4*0724680 *1499999159O2TA00000*****
5*APPLFOR04254658314333346FH2*A00000*****
6*APPLFOR04310373393000046C4C*A15759000000000

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


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

markmzz

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
Maybe this (with formulas):

Layout

ID #YRTOSBRCOTERMSTATUSWAGE 1WAGE 2WAGE_3TAX 1TAX 2YTDYTD 2YTD 3YTD 4YTD 5 ID # ID #YR ID #YR ID #YR
1883706046S26 A33225,5022136,5000032503,0222136,50 188370 1883706 1883706 0724610 *11
1883706046A01 A10,2010,2000025332253320 283750 1883706 28375013 0724610 * *14
1883706047A02 A2,502,5000023,223,20 299950 1883706 29995014 0724680 *14
1883706048A01 A23202320000221122110 316740 1883706 31674014 APPLFOR04254658314
1883706045A01 A212302123000022235222350 416500 1883706 41650014 APPLFOR0431037339
2837501333333BSJ0X A0000000000 538620 28375013 53862013
2837501333333CSJ0X A0000000000 543610 28375013 54361013
2999501433333729HQ A37961,53095000256,50037189,3995000 554670 29995014 55467010
3167401433333RWMVG A11538,42095000256,50011538,4295000 556410 31674014 55641011
416500143333346SCB A0000000000 572240 41650014 57224010
538620130A0DN3FI0000000000 724270 53862013 72427014
538620130A0DN3FI0000000000 53862013
538620130A0DN3FI0000000000 53862013
54361013070WWD A5949,32000000000 54361013
54361013070WWD A5949,32000000000 54361013
554670103300STNML6 A0000000000 55467010
55641011333372MSV A0000000000 55641011
572240103333380AQD I00000***** 57224010
7242701433333R2I34 A3333,3403333,34090003333,343333,340 72427014
0724610 *11333153ML A388389,810251990,8606803,7500394163,06251990,860
0724610 * *143333315E2X A17561,48095000256,50017561,4895000
0724680 *1499999159O2TA00000*****
APPLFOR04254658314333346FH2 A00000*****
APPLFOR04310373393000046C4C A15759000000000
*******************************************************************************************************************************************************************************************************
<colgroup><col width="108" style="width: 81pt; mso-width-source: userset; mso-width-alt: 3949;"> <col width="22" style="width: 17pt; mso-width-source: userset; mso-width-alt: 804;"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;" span="2"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;"> <col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 1170;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;" span="2"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;"> <col width="22" style="width: 17pt; mso-width-source: userset; mso-width-alt: 804;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;"> <col width="22" style="width: 17pt; mso-width-source: userset; mso-width-alt: 804;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="108" style="width: 81pt; mso-width-source: userset; mso-width-alt: 3949;"> <col width="22" style="width: 17pt; mso-width-source: userset; mso-width-alt: 804;"> <tbody> </tbody>

Formulas

Code:
In U2 - use Ctrl+Shift+Enter to enter the formula
 
=IFERROR(INDEX(A$2:A$25,SMALL(IF(COUNTIF($S$2:$S$12,$A$2:$A$25),ROW($A$2:$A$25)-ROW($A$2)+1),ROWS(U$2:U2))),"")

In X2 - use Ctrl+Shift+Enter to enter the formula

=IFERROR(INDEX(A$2:A$25,MATCH(0,COUNTIF($X$1:$X1,$A$2:$A$25)+(COUNTIF($S$2:$S$12,$A$2:$A$25)=0),0)),"")

In AA2 - use Ctrl+Shift+Enter to enter the formula

=IFERROR(INDEX(A$2:A$25,SMALL(IF(COUNTIF($S$2:$S$12,$A$2:$A$25)=0,ROW($A$2:$A$25)-ROW($A$2)+1),ROWS(AA$2:AA2))),"")

Note: all data in only one sheet - modify the formulas to your need.

Markmzz
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649

ADVERTISEMENT

nike,

Some of your screenshots are coming thru with ID numbers, like:
0188370, and, this 188370?????


Can you post your workbook with the five worksheets?

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.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
nike,

The following two worksheet names contain a trailing space character:
MATCHED NO DUP
NO MATCH

1. Will the workbook always have these two worksheet names with a single trailing space character?


And, your attached workbook cells are protected, which is causing a problem.

2. Can I have another workbook with the protection removed?
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
nike,

I was not able to use your attached workbook.

Make sure you read my reply #9.


So, I took your screenshots, and, was able to create the code to do the following:


Sample worksheets before the macro:

Excel Workbook
A
1ID #
2188370
3283750
4299950
5316740
6416500
7538620
8543610
9554670
10556410
11572240
12724270
13123456
14254689
15212542
16254686
17
IDS


Excel Workbook
ABCDEFGHIJKLMNOPQ
1ID #YRTOSBRCOTERMSTATUSWAGE 1WAGE 2WAGE_3TAX 1TAX 2YTDYTD 2YTD 3YTD 4YTD 5
21883706046S26A33225.5022136.5000032503.0222136.50
31883706046A01A10.2010.2000025332253320
41883706047A02A2.502.5000023.223.20
51883706048A01A23202320000221122110
61883706045A01A212302123000022235222350
72837501333333BSJ0XA0000000000
82837501333333CSJ0XA0000000000
92999501433333729HQA37961.53095000256.50037189.3995000
103167401433333RWMVGA11538.42095000256.50011538.4295000
11416500143333346SCBA0000000000
12538620130A0DN3FI0000000000
13538620130A0DN3FI0000000000
14538620130A0DN3FI0000000000
1554361013070WWDA5949.32000000000
1654361013070WWDA5949.32000000000
17554670103300STNML6A0000000000
1855641011333372MSVA0000000000
19572240103333380AQDI00000*****
207242701433333R2I34A3333.3403333.34090003333.343333.340
210724610 *11333153MLA388389.810251990.8606803.7500394163.06251990.860
220724610 * *143333315E2XA17561.48095000256.50017561.4895000
230724680 *1499999159O2TA00000*****
24APPLFOR04254658314333346FH2A00000*****
25APPLFOR04310373393000046C4CA15759000000000
26
TOAD QUERY


Excel Workbook
ABCDEFGHIJKLMNOPQR
1SHEET1 ID#ID #YRTOSBRCOTERMSTATUSWAGE 1WAGE 2WAGE_3TAX 1TAX 2YTDYTD 2YTD 3YTD 4YTD 5
2
3
4
5
6
7
8
9
10
11
12
13
14
MATCHED WITH DUPS


Excel Workbook
ABCDEFGHIJKLMNOPQR
1SHEET1 ID#ID #YRTOSBRCOTERMSTATUSWAGE 1WAGE 2WAGE_3TAX 1TAX 2YTDYTD 2YTD 3YTD 4YTD 5
2
3
4
5
6
7
8
9
MATCHED NO DUPS


Excel Workbook
ABCDEFGHIJKLMNOPQR
1SHEET1 ID#ID #YRTOSBRCOTERMSTATUSWAGE 1WAGE 2WAGE_3TAX 1TAX 2YTDYTD 2YTD 3YTD 4YTD 5
2
3
4
5
6
NO MATCH



And, after the macro:

Excel Workbook
ABCDEFGHIJKLMNOPQR
1SHEET1 ID#ID #YRTOSBRCOTERMSTATUSWAGE 1WAGE 2WAGE_3TAX 1TAX 2YTDYTD 2YTD 3YTD 4YTD 5
21883701883706046S26A33225.5022136.5000032503.0222136.50
31883701883706046A01A10.2010.2000025332253320
41883701883706047A02A2.502.5000023.223.20
51883701883706048A01A23202320000221122110
61883701883706045A01A212302123000022235222350
72837502837501333333BSJ0XA0000000000
82837502837501333333CSJ0XA0000000000
9538620538620130A0DN3FI0000000000
10538620538620130A0DN3FI0000000000
11538620538620130A0DN3FI0000000000
1254361054361013070WWDA5949.32000000000
1354361054361013070WWDA5949.32000000000
14
MATCHED WITH DUPS


Excel Workbook
ABCDEFGHIJKLMNOPQR
1SHEET1 ID#ID #YRTOSBRCOTERMSTATUSWAGE 1WAGE 2WAGE_3TAX 1TAX 2YTDYTD 2YTD 3YTD 4YTD 5
22999502999501433333729HQA37961.53095000256.50037189.3995000
33167403167401433333RWMVGA11538.42095000256.50011538.4295000
4416500416500143333346SCBA0000000000
5554670554670103300STNML6A0000000000
655641055641011333372MSVA0000000000
7572240572240103333380AQDI00000*****
87242707242701433333R2I34A3333.3403333.34090003333.343333.340
9
MATCHED NO DUPS


Excel Workbook
ABCDEFGHIJKLMNOPQR
1SHEET1 ID#ID #YRTOSBRCOTERMSTATUSWAGE 1WAGE 2WAGE_3TAX 1TAX 2YTDYTD 2YTD 3YTD 4YTD 5
2123456
3254689
4212542
5254686
6
NO MATCH
 

Forum statistics

Threads
1,140,925
Messages
5,703,205
Members
421,280
Latest member
Jaycee01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top