Joining 2 sheets with a variation

[Kallis]

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

[Kallis]

Can someone please help

[caltang]

If you need to combine both J and L, you can use a Union tool that can attach both J and L. 

[caltang]

Let me know if this is what you're looking for:

[CoG · Accepted solution]

It took me a while to get down here, due to the sheer size of the tables included XD, but here is a sample workflow that should accomplish what you're looking for.

[Kallis]

Thanks @CoG 

The solution works. Thanks a lot for your help

[Kallis]

Thanks @caltang 

However, I have already tried this method.

Really appreciate your help though.

[CoG]

Glad to be of help, please kindly mark as solution to help others find solutions more quickly!

[Kallis]

Hello @CoG 

I have just clicked on "accept as solution"

Thanks once again