Excel: Comparing two worksheets to find the match including the duplicates


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
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
Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4


This question generated 23 answers. To proceed to the answers, click here.

This thread is current as of April 18, 2014.


For more resources for Microsoft Excel