Good Day Everyone !
I have a requirement where I need to join two sheets of an excel file with a slight variation.
The result of the join tool in "J" and "L" parts need to be joined.
The Output of "J" has record ID's and duplicate groups. The Output of "L" has record ID's and duplicate groups.
The output for "J" has duplicate groups in pairs. Where pairs exist, no action is needed. Where pairs do not exist I need to extract the missing duplicate group from "L" and merge them together.
For example in the below data table 1, duplicate groups are in pair, for the ones that are not in a pair(some are highlighted in orange) should be identified in table 2 and merged with table 1.
table 1
| RecordID Duplicate File | Duplicate group |
| 145 | 187085 |
| 146 | 187085 |
| 281 | 187088 |
| 282 | 187088 |
| 259 | 187089 |
| 260 | 187089 |
| 269 | 187108 |
| 271 | 187111 |
| 379 | 187128 |
| 380 | 187128 |
| 183 | 187130 |
| 184 | 187130 |
| 353 | 187134 |
| 354 | 187134 |
| 221 | 187139 |
| 222 | 187139 |
| 225 | 187140 |
| 226 | 187140 |
| 247 | 187141 |
| 248 | 187141 |
| 253 | 187142 |
| 254 | 187142 |
| 275 | 187143 |
| 276 | 187143 |
| 397 | 187144 |
| 398 | 187144 |
| 405 | 187145 |
| 406 | 187145 |
| 363 | 187146 |
| 364 | 187146 |
| 249 | 187147 |
| 250 | 187147 |
| 355 | 187148 |
| 356 | 187148 |
| 99 | 187149 |
| 100 | 187149 |
| 373 | 187150 |
| 374 | 187150 |
| 127 | 187151 |
| 128 | 187151 |
| 205 | 187152 |
| 206 | 187152 |
| 381 | 187154 |
| 382 | 187154 |
| 237 | 187170 |
| 238 | 187170 |
| 191 | 187196 |
| 192 | 187196 |
| 235 | 187202 |
| 236 | 187202 |
| 119 | 187220 |
| 120 | 187220 |
| 41 | 187240 |
| 42 | 187240 |
| 401 | 187242 |
| 402 | 187242 |
| 455 | 187253 |
| 456 | 187253 |
| 22 | 187254 |
| 21 | 187254 |
| 3 | 187255 |
| 61 | 187259 |
| 62 | 187259 |
| 1 | 187260 |
| 59 | 187262 |
| 60 | 187262 |
| 632 | 187263 |
| 630 | 187264 |
| 11 | 187267 |
| 58 | 187268 |
| 57 | 187268 |
| 627 | 187269 |
| 56 | 187270 |
| 55 | 187270 |
| 626 | 187272 |
| 9 | 187273 |
| 624 | 187274 |
| 690 | 187275 |
| 35 | 187277 |
| 38 | 187279 |
| 621 | 187280 |
| 622 | 187280 |
| 20 | 187282 |
| 19 | 187282 |
| 619 | 187283 |
| 620 | 187283 |
| 53 | 187284 |
| 54 | 187284 |
| 83 | 187285 |
| 84 | 187285 |
| 52 | 187286 |
| 51 | 187286 |
| 618 | 187287 |
| 617 | 187287 |
| 27 | 187289 |
| 49 | 187291 |
| 50 | 187291 |
| 615 | 187293 |
| 616 | 187293 |
| 47 | 187294 |
| 48 | 187294 |
| 687 | 187295 |
| 688 | 187295 |
| 614 | 187298 |
| 613 | 187298 |
| 45 | 187300 |
| 46 | 187300 |
| 349 | 187301 |
| 75 | 187302 |
| 611 | 187303 |
| 612 | 187303 |
| 346 | 187304 |
| 344 | 187306 |
| 651 | 187307 |
| 686 | 187308 |
| 609 | 187311 |
| 610 | 187311 |
| 342 | 187312 |
| 608 | 187314 |
| 607 | 187314 |
| 340 | 187315 |
| 338 | 187316 |
| 294 | 187317 |
| 432 | 187318 |
| 431 | 187318 |
| 606 | 187319 |
| 604 | 187321 |
| 603 | 187321 |
| 460 | 187323 |
| 459 | 187323 |
| 602 | 187324 |
| 416 | 187328 |
| 597 | 187330 |
| 336 | 187332 |
| 334 | 187333 |
| 332 | 187334 |
| 292 | 187336 |
| 596 | 187337 |
| 446 | 187338 |
| 445 | 187338 |
| 593 | 187340 |
| 594 | 187340 |
| 590 | 187342 |
| 589 | 187342 |
| 587 | 187343 |
| 330 | 187344 |
| 585 | 187345 |
| 586 | 187345 |
| 583 | 187346 |
| 582 | 187347 |
| 328 | 187349 |
| 673 | 187350 |
| 326 | 187351 |
| 580 | 187352 |
| 324 | 187354 |
| 575 | 187355 |
| 322 | 187357 |
| 672 | 187359 |
| 571 | 187360 |
| 570 | 187361 |
| 569 | 187361 |
| 320 | 187362 |
| 567 | 187364 |
| 566 | 187366 |
| 458 | 187367 |
| 457 | 187367 |
| 563 | 187368 |
| 564 | 187368 |
| 562 | 187369 |
| 561 | 187369 |
| 557 | 187371 |
| 555 | 187373 |
| 551 | 187375 |
| 552 | 187375 |
| 550 | 187376 |
| 549 | 187376 |
| 546 | 187378 |
| 544 | 187381 |
| 543 | 187381 |
| 541 | 187382 |
| 290 | 187383 |
| 539 | 187384 |
| 537 | 187385 |
| 536 | 187386 |
| 533 | 187388 |
| 534 | 187388 |
| 532 | 187389 |
| 530 | 187390 |
| 527 | 187391 |
| 528 | 187391 |
| 524 | 187394 |
| 523 | 187394 |
| 288 | 187395 |
| 521 | 187396 |
| 520 | 187397 |
| 286 | 187398 |
| 518 | 187399 |
| 515 | 187400 |
| 512 | 187402 |
| 510 | 187403 |
| 643 | 187404 |
| 507 | 187405 |
| 508 | 187405 |
| 506 | 187406 |
| 503 | 187407 |
| 504 | 187407 |
| 501 | 187408 |
| 502 | 187408 |
| 435 | 187409 |
| 499 | 187410 |
| 497 | 187413 |
| 496 | 187414 |
| 493 | 187415 |
| 494 | 187415 |
| 491 | 187416 |
| 492 | 187416 |
| 490 | 187418 |
| 433 | 187419 |
| 488 | 187421 |
| 484 | 187423 |
| 483 | 187423 |
| 389 | 187425 |
| 284 | 187427 |
| 481 | 187428 |
| 280 | 187429 |
| 480 | 187430 |
| 479 | 187430 |
| 478 | 187431 |
| 475 | 187432 |
| 476 | 187432 |
| 474 | 187433 |
| 362 | 187434 |
| 471 | 187436 |
| 472 | 187436 |
| 470 | 187437 |
| 467 | 187438 |
| 468 | 187438 |
| 465 | 187439 |
| 466 | 187439 |
| 464 | 187440 |
| 463 | 187440 |
| 461 | 187441 |
| 462 | 187441 |
Table2:
| RecordID Duplicate File | Duplicate group |
| 123 | 187184 |
| 124 | 187184 |
| 125 | 187121 |
| 126 | 187121 |
| 73 | 187246 |
| 74 | 187246 |
| 691 | 187305 |
| 139 | 187221 |
| 140 | 187221 |
| 684 | 187320 |
| 434 | 187419 |
| 436 | 187409 |
| 644 | 187404 |
| 361 | 187434 |
| 309 | 187168 |
| 666 | 187313 |
| 652 | 187307 |
| 165 | 187214 |
| 166 | 187214 |
| 213 | 187198 |
| 214 | 187198 |
| 489 | 187418 |
| 513 | 187401 |
| 538 | 187385 |
| 548 | 187377 |
| 650 | 187327 |
| 251 | 187127 |
| 252 | 187127 |
| 675 | 187348 |
| 439 | 187119 |
| 440 | 187119 |
| 227 | 187219 |
| 228 | 187219 |
| 198 | 187200 |
| 133 | 187126 |
| 151 | 187188 |
| 152 | 187188 |
| 157 | 187195 |
| 158 | 187195 |
| 358 | 187424 |
| 5 | 187249 |
| 6 | 187249 |
| 2 | 187260 |
| 4 | 187255 |
| 10 | 187273 |
| 12 | 187267 |
| 28 | 187289 |
| 36 | 187277 |
| 311 | 187231 |
| 312 | 187231 |
| 87 | 187227 |
| 88 | 187227 |
| 441 | 187358 |
| 189 | 187171 |
| 190 | 187171 |
| 660 | 187392 |
| 662 | 187309 |
| 219 | 187203 |
| 220 | 187203 |
| 161 | 187224 |
| 162 | 187224 |
| 664 | 187104 |
| 359 | 187412 |
| 207 | 187411 |
| 413 | 187118 |
| 414 | 187118 |
| 153 | 187208 |
| 154 | 187208 |
| 169 | 187169 |
| 170 | 187169 |
| 67 | 187261 |
| 514 | 187401 |
| 576 | 187355 |
| 592 | 187341 |
| 646 | 187420 |
| 117 | 187192 |
| 118 | 187192 |
| 423 | 187241 |
| 424 | 187241 |
| 177 | 187213 |
| 178 | 187213 |
| 113 | 187207 |
| 114 | 187207 |
| 229 | 187209 |
| 230 | 187209 |
| 37 | 187279 |
| 681 | 187322 |
| 107 | 187180 |
| 108 | 187180 |
| 351 | 187258 |
| 285 | 187398 |
| 287 | 187395 |
| 289 | 187383 |
| 421 | 187132 |
| 422 | 187132 |
| 655 | 187100 |
| 663 | 187104 |
| 350 | 187301 |
| 352 | 187258 |
| 668 | 187339 |
| 665 | 187313 |
| 547 | 187377 |
| 581 | 187347 |
| 591 | 187341 |
| 601 | 187324 |
| 101 | 187173 |
| 102 | 187173 |
| 649 | 187327 |
| 134 | 187126 |
| 297 | 187443 |
| 298 | 187443 |
| 121 | 187217 |
| 122 | 187217 |
| 403 | 187417 |
| 485 | 187422 |
| 505 | 187406 |
| 261 | 187159 |
| 262 | 187159 |
| 103 | 187182 |
| 104 | 187182 |
| 171 | 187193 |
| 172 | 187193 |
| 659 | 187392 |
| 383 | 187426 |
| 63 | 187101 |
| 678 | 187335 |
| 109 | 187206 |
| 110 | 187206 |
| 393 | 187109 |
| 395 | 187105 |
| 347 | 187102 |
| 233 | 187205 |
| 234 | 187205 |
| 371 | 187292 |
| 77 | 187299 |
| 95 | 187210 |
| 96 | 187210 |
| 697 | 187444 |
| 698 | 187444 |
| 390 | 187425 |
| 685 | 187308 |
| 648 | 187387 |
| 303 | 187129 |
| 304 | 187129 |
| 357 | 187424 |
| 623 | 187274 |
| 375 | 187380 |
| 676 | 187348 |
| 679 | 187331 |
| 669 | 187363 |
| 310 | 187168 |
| 65 | 187281 |
| 453 | 187112 |
| 404 | 187417 |
| 277 | 187138 |
| 278 | 187138 |
| 689 | 187275 |
| 409 | 187326 |
| 437 | 187435 |
| 270 | 187108 |
| 272 | 187111 |
| 556 | 187373 |
| 243 | 187125 |
| 244 | 187125 |
| 187 | 187116 |
| 188 | 187116 |
| 215 | 187115 |
| 216 | 187115 |
| 255 | 187117 |
| 256 | 187117 |
| 267 | 187238 |
| 268 | 187238 |
| 694 | 187325 |
| 195 | 187194 |
| 196 | 187194 |
| 469 | 187437 |
| 473 | 187433 |
| 529 | 187390 |
| 545 | 187378 |
| 559 | 187370 |
| 197 | 187200 |
| 81 | 187257 |
| 193 | 187201 |
| 194 | 187201 |
| 43 | 187288 |
| 308 | 187256 |
| 85 | 187271 |
| 175 | 187199 |
| 176 | 187199 |
| 135 | 187230 |
| 136 | 187230 |
| 137 | 187124 |
| 138 | 187124 |
| 111 | 187189 |
| 112 | 187189 |
| 442 | 187358 |
| 415 | 187328 |
| 86 | 187271 |
| 257 | 187135 |
| 258 | 187135 |
| 68 | 187261 |
| 560 | 187370 |
| 307 | 187256 |
| 696 | 187365 |
| 115 | 187076 |
| 116 | 187076 |
| 443 | 187372 |
| 217 | 187204 |
| 218 | 187204 |
| 447 | 187379 |
| 91 | 187177 |
| 92 | 187177 |
| 477 | 187431 |
| 525 | 187393 |
| 565 | 187366 |
| 573 | 187356 |
| 578 | 187353 |
| 579 | 187352 |
| 449 | 187228 |
| 450 | 187228 |
| 317 | 187229 |
| 318 | 187229 |
| 279 | 187429 |
| 670 | 187363 |
| 231 | 187186 |
| 232 | 187186 |
| 454 | 187112 |
| 656 | 187100 |
| 33 | 187266 |
| 181 | 187179 |
| 182 | 187179 |
| 577 | 187353 |
| 595 | 187337 |
| 599 | 187329 |
| 605 | 187319 |
| 657 | 187245 |
| 658 | 187245 |
| 430 | 187442 |
| 208 | 187411 |
| 641 | 187106 |
| 653 | 187120 |
| 654 | 187120 |
| 143 | 187187 |
| 144 | 187187 |
| 173 | 187197 |
| 174 | 187197 |
| 223 | 187211 |
| 224 | 187211 |
| 633 | 187276 |
| 680 | 187331 |
| 44 | 187288 |
| 634 | 187276 |
| 147 | 187216 |
| 148 | 187216 |
| 7 | 187247 |
| 8 | 187247 |
| 185 | 187212 |
| 186 | 187212 |
| 89 | 187222 |
| 90 | 187222 |
| 368 | 187310 |
| 93 | 187226 |
| 94 | 187226 |
| 245 | 187137 |
| 246 | 187137 |
| 693 | 187325 |
| 661 | 187309 |
| 360 | 187412 |
| 448 | 187379 |
| 384 | 187426 |
| 639 | 187103 |
| 299 | 187122 |
| 300 | 187122 |
| 239 | 187225 |
| 240 | 187225 |
| 376 | 187380 |
| 79 | 187290 |
| 419 | 187244 |
| 420 | 187244 |
| 667 | 187339 |
| 394 | 187109 |
| 396 | 187105 |
| 156 | 187296 |
| 64 | 187101 |
| 444 | 187372 |
| 209 | 187191 |
| 210 | 187191 |
| 105 | 187181 |
| 106 | 187181 |
| 149 | 187178 |
| 150 | 187178 |
| 511 | 187402 |
| 517 | 187399 |
| 519 | 187397 |
| 531 | 187389 |
| 535 | 187386 |
| 554 | 187374 |
| 295 | 187107 |
| 348 | 187102 |
| 425 | 187251 |
| 426 | 187251 |
| 129 | 187185 |
| 130 | 187185 |
| 486 | 187422 |
| 97 | 187183 |
| 98 | 187183 |
| 179 | 187176 |
| 180 | 187176 |
| 674 | 187350 |
| 211 | 187175 |
| 212 | 187175 |
| 201 | 187215 |
| 202 | 187215 |
| 31 | 187278 |
| 692 | 187305 |
| 301 | 187133 |
| 302 | 187133 |
| 305 | 187161 |
| 306 | 187161 |
| 313 | 187164 |
| 314 | 187164 |
| 315 | 187163 |
| 316 | 187163 |
| 387 | 187167 |
| 388 | 187167 |
| 391 | 187162 |
| 392 | 187162 |
| 407 | 187165 |
| 408 | 187165 |
| 411 | 187166 |
| 412 | 187166 |
| 32 | 187278 |
| 34 | 187266 |
| 410 | 187326 |
| 428 | 187297 |
| 625 | 187272 |
| 628 | 187269 |
| 629 | 187264 |
| 631 | 187263 |
| 319 | 187362 |
| 321 | 187357 |
| 323 | 187354 |
| 325 | 187351 |
| 327 | 187349 |
| 329 | 187344 |
| 331 | 187334 |
| 333 | 187333 |
| 335 | 187332 |
| 337 | 187316 |
| 339 | 187315 |
| 341 | 187312 |
| 343 | 187306 |
| 345 | 187304 |
| 291 | 187336 |
| 293 | 187317 |
| 71 | 187243 |
| 72 | 187243 |
| 645 | 187420 |
| 683 | 187320 |
| 385 | 187153 |
| 386 | 187153 |
| 635 | 187252 |
| 636 | 187252 |
| 637 | 187250 |
| 638 | 187250 |
| 671 | 187359 |
| 640 | 187103 |
| 372 | 187292 |
| 367 | 187310 |
| 495 | 187414 |
| 500 | 187410 |
| 509 | 187403 |
| 516 | 187400 |
| 526 | 187393 |
| 540 | 187384 |
| 598 | 187330 |
| 682 | 187322 |
| 369 | 187136 |
| 370 | 187136 |
| 155 | 187296 |
| 399 | 187123 |
| 400 | 187123 |
| 296 | 187107 |
| 199 | 187190 |
| 200 | 187190 |
| 163 | 187174 |
| 164 | 187174 |
| 70 | 187265 |
| 553 | 187374 |
| 438 | 187435 |
| 265 | 187035 |
| 266 | 187035 |
| 677 | 187335 |
| 66 | 187281 |
| 487 | 187421 |
| 572 | 187360 |
| 584 | 187346 |
| 695 | 187365 |
| 13 | 187239 |
| 14 | 187239 |
| 15 | 187236 |
| 16 | 187236 |
| 17 | 187234 |
| 18 | 187234 |
| 23 | 187233 |
| 24 | 187233 |
| 25 | 187232 |
| 26 | 187232 |
| 29 | 187235 |
| 30 | 187235 |
| 39 | 187237 |
| 40 | 187237 |
| 365 | 187158 |
| 366 | 187158 |
| 377 | 187155 |
| 378 | 187155 |
| 417 | 187157 |
| 418 | 187157 |
| 451 | 187156 |
| 452 | 187156 |
| 76 | 187302 |
| 78 | 187299 |
| 80 | 187290 |
| 82 | 187257 |
| 241 | 187131 |
| 242 | 187131 |
| 283 | 187427 |
| 263 | 187160 |
| 264 | 187160 |
| 429 | 187442 |
| 159 | 187172 |
| 160 | 187172 |
| 647 | 187387 |
| 558 | 187371 |
| 167 | 187218 |
| 168 | 187218 |
| 131 | 187072 |
| 132 | 187072 |
| 141 | 187056 |
| 142 | 187056 |
| 642 | 187106 |
| 273 | 187248 |
| 274 | 187248 |
| 482 | 187428 |
| 498 | 187413 |
| 522 | 187396 |
| 542 | 187382 |
| 568 | 187364 |
| 574 | 187356 |
| 588 | 187343 |
| 600 | 187329 |
| 427 | 187297 |
| 69 | 187265 |
| 203 | 187223 |
| 204 | 187223 |
I hope I have made the question clear, please let me know if you have any questions. I most definitely appreciate any help on this.
Regards
Abhijeet
