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
Bobji
Solved! Go to Solution.
Can someone please help
If you need to combine both J and L, you can use a Union tool that can attach both J and L.
Thanks @CoG
The solution works. Thanks a lot for your help
Glad to be of help, please kindly mark as solution to help others find solutions more quickly!