Hi,
I have the following information in two lists. List A changes each week and List B is like the Master List.
Problem is when I receive list A each week there are duplicate ID's in list A ( there are more than 10,000 ID's in the list) and also there are unique ID's appearing each week which are not in List B ( which I have to update)
Can you please help me with a formula (NO Macro's please) which can identify and list duplicates in List A and to find and identify the unique ID's in List A compared to List B?
I have the following information in two lists. List A changes each week and List B is like the Master List.
Problem is when I receive list A each week there are duplicate ID's in list A ( there are more than 10,000 ID's in the list) and also there are unique ID's appearing each week which are not in List B ( which I have to update)
Can you please help me with a formula (NO Macro's please) which can identify and list duplicates in List A and to find and identify the unique ID's in List A compared to List B?
Excel Workbook | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
2 | 1. to find and list the duplicates from the range B4:F38 (List A) | |||||||||||||||
3 | List A | List B | Duplicates List | |||||||||||||
4 | This Week | Week1 | Week2 | Week3 | ID No's | ID No's | ID No's | This Week | Week1 | Week2 | Week3 | |||||
5 | 119 | 320 | 257 | 341 | 4380 | 4380 | 22032 | 381 | 292 | 97 | 487 | |||||
6 | 466 | 174 | 132 | 64 | 4878 | 4878 | 22032 | 279 | 490 | 347 | 303 | |||||
7 | 288 | 448 | 197 | 86 | 13488 | 6393 | 35629 | 198 | 76 | 492 | 239 | |||||
8 | 105 | 119 | 215 | 224 | 14003 | 8930 | 35629 | 422 | 230 | 348 | 26 | |||||
9 | 381 | 292 | 97 | 487 | 22032 | 9101 | 57550 | 224 | 28 | 23 | 121 | |||||
10 | 279 | 490 | 347 | 303 | 22032 | 11628 | 57550 | 18 | 59 | 271 | 271 | |||||
11 | 292 | 462 | 365 | 468 | 25995 | 13488 | 65605 | 342 | 217 | 13 | 35 | |||||
12 | 350 | 478 | 62 | 465 | 31658 | 14003 | 65605 | 77 | 421 | 452 | 12 | |||||
13 | 499 | 98 | 114 | 21 | 34769 | 22032 | 81703 | 300 | 234 | 55 | 364 | |||||
14 | 198 | 76 | 492 | 239 | 35629 | 25995 | 81703 | 35 | 197 | 494 | 178 | |||||
15 | 422 | 230 | 348 | 26 | 35629 | 26340 | 56897 | 32 | 304 | 130 | 405 | |||||
16 | 190 | 29 | 207 | 300 | 37844 | 31559 | 56897 | 37 | 304 | 145 | 857 | |||||
17 | 403 | 223 | 345 | 202 | 49628 | 31658 | ||||||||||
18 | 459 | 235 | 135 | 66 | 57070 | 34769 | ||||||||||
19 | 224 | 28 | 23 | 121 | 57550 | 35629 | ||||||||||
20 | 18 | 59 | 271 | 271 | 57550 | 37844 | ||||||||||
21 | 371 | 450 | 186 | 455 | 60869 | 40622 | ||||||||||
22 | 187 | 302 | 344 | 324 | 65450 | 45280 | ||||||||||
23 | 342 | 217 | 13 | 35 | 65605 | 49628 | ||||||||||
24 | 77 | 421 | 452 | 12 | 65605 | 57070 | ||||||||||
25 | 412 | 19 | 413 | 377 | 66610 | 57550 | ||||||||||
26 | 485 | 206 | 77 | 124 | 71800 | 60869 | 2. to compare List A, ID No's against List B ID no's and list the unique ID's in List A. (there could be duplicates here as well) | |||||||||
27 | 498 | 409 | 471 | 459 | 73732 | 65450 | ||||||||||
28 | 32 | 304 | 130 | 405 | 79510 | 65605 | ||||||||||
29 | 300 | 234 | 55 | 364 | 81703 | 66610 | Unique ID Numbers | |||||||||
30 | 35 | 197 | 494 | 178 | 81703 | 71800 | ID No's | This Week | Week1 | Week2 | Week3 | |||||
31 | 498 | 409 | 471 | 459 | 89998 | 73732 | 89998 | 498 | 409 | 471 | 459 | |||||
32 | 32 | 304 | 130 | 405 | 56897 | 79510 | 56897 | 32 | 304 | 130 | 405 | |||||
33 | 37 | 304 | 145 | 857 | 56897 | 81703 | 56897 | 37 | 304 | 145 | 857 | |||||
Sheet1 |