Reconciliation of two tables with Join tool (resolve duplicate records)

[Anjankumar2021]

please assist on the below . i have used join tool **join by specific records but it created duplicated rows and when we apply unique tool its deleting 3000 with 25775 (table A)  instead of 34058. its giving invalid difference of 8283.

output received :

Product ID	Quantity	Amount	Distributor	Right_Product ID	Transaction ID	Right_Quantity	sale Amount	Distributor/Agent	Difference
6719	10000	146843	LG	6719	110089	10000	146843	LGEC	0
6719	11000	25260	SAMSUNG	6719	123456	11000	25260	SAMSUNGEC	0
6719	3000	34058	LG	6719	220569	3000	25775	LGEC	8283
6719	3000	34058	LG	6719	231589	3000	34058	LGEC	0
6719	6000	597	LG	6719	718956	6000	597	LGEC	0

input:

Table A
Product ID	Quantity	Amount	Distributor
6719	11,000	25,260	SAMSUNG
6719	3,000	34,058	LG
6719	10,000	146,843	LG
6719	3,000	25,775	LG
6719	6,000	597	LG

Table B
Product ID	Transaction ID	Quantity	sale Amount	Distributor/Agent
6719	123456	11000	25260	SAMSUNGEC
6719	718956	6000	597	LGEC
6719	110089	10000	146843	LGEC
6719	231589	3000	34058	LGEC
6719	220569	3000	25775	LGEC

Required Output: Difference = Amount - Sale Amount

Product ID	Quantity	Amount	Distributor	Product ID	Transaction ID	Quantity	sale Amount	Distributor/Agent	Difference
6719	11,000	25,260	SAMSUNG	6719	123456	11000	25260	SAMSUNGEC	0
6719	3,000	34,058	LG	6719	231589	3000	34058	LGEC	0
6719	10,000	146,843	LG	6719	110089	10000	146843	LGEC	0
6719	3,000	25,775	LG	6719	220569	3000	25775	LGEC	0
6719	6,000	597	LG	6719	718956	6000	597	LGEC	0

[flying008]

Hi, @Anjankumar2021 

FYI.

[binuacs · Accepted solution]

@Anjankumar2021 

[Anjankumar2021]

thank you so much for your help :)

[Anjankumar2021]

@binuacs  workflow unable to reconcile for the below .  we got invalid break of 2491 and 2489. could you pls advise in this case.

1	6719	11000	25260	SAMSUNG	123456	11000	25260	SAMSUNGEC	0
2	6719	3000	34058	LG	231589	3000	34058	LGEC	0
3	6719	10000	146843	LG	110089	10000	146843	LGEC	0
4	6719	3000	25775	LG	220569	3000	25775	LGEC	0
5	6719	6000	597	LG	718956	6000	597	LGEC	0
6	6732	2000	402	SAMSUNG	171115420	2000	2891	LGEC	-2491
7	6732	2000	2891	LG	171166590	2000	402	LGEC	2489
8	6732	3000	831	LG	169975342	3000	831	SAMSUNGEC	0

input : 

table 1

Product ID	Quantity	Amount	Distributor
6719	11,000	25,260	SAMSUNG
6719	3,000	34,058	LG
6719	10,000	146,843	LG
6719	3,000	25,775	LG
6719	6,000	597	LG
6732	2,000	400	SAMSUNG
6732	2,000	2,891	LG
6732	3,000	831	LG

table2:

Product ID	Transaction ID	Quantity	sale Amount	Distributor/Agent
6719	123456	11000	25260	SAMSUNGEC
6719	718956	6000	597	LGEC
6719	110089	10000	146843	LGEC
6719	231589	3000	34058	LGEC
6719	220569	3000	25775	LGEC
6732	169975342	3000	831	SAMSUNGEC
6732	171115420	2000	2891	LGEC
6732	171166590	2000	402	LGEC

required output:

RecordID	Product ID	Quantity	Amount	Distributor	Transaction ID	Right_Quantity	sale Amount	Distributor/Agent	diff
1	6719	11000	25260	SAMSUNG	123456	11000	25260	SAMSUNGEC	0
2	6719	3000	34058	LG	231589	3000	34058	LGEC	0
3	6719	10000	146843	LG	110089	10000	146843	LGEC	0
4	6719	3000	25775	LG	220569	3000	25775	LGEC	0
5	6719	6000	597	LG	718956	6000	597	LGEC	0
6	6732	2000	400	SAMSUNG	171115420	2000	402	LGEC	2
7	6732	2000	2891	LG	171166590	2000	2891	LGEC	0
8	6732	3000	831	LG	169975342	3000	831	SAMSUNGEC	0

[binuacs · Accepted solution]

@Anjankumar2021 

[Anjankumar2021]

Amazing its working good...:)

[Anjankumar2021]

@binuacs Kindly assist on the below exception.

workflow working fine and giving 98% accuracy. but got below exception with Distributor/Agent.

when reconciling Distributor or agent name should match with Product id , Qty, amount / sales amount.

workflow is able to match all above fields except distributor / agent.

LG Vs. SAMSUNG

SAMSUNG Vs LG

Input files:

Table A					Table B
Product ID	Quantity	Amount	Distributor		Product ID	Transaction ID	Quantity	sale Amount	Distributor/Agent
9999	2000	538	LG		9999	13267154	105000	300	TCL
9999	7000	1541	LG		9999	81562938	20000	2200	Onida
9999	3000	605	LG		9999	18205350	12000	850	Onida
9999	4000	531	LG		9999	18229595	2000	80	Onida
9999	9000	513	LG		9999	18236437	5000	170	Onida
9999	2000	92	LG		9999	11557439	86000	300	TCL
9999	26000	1421	LG		9999	18167945	2000	565	LG
9999	1000	30	LG		9999	171861997	7000	1565	LG
9999	5000	510	Sansui		9999	18225328	3000	622	LG
9999	2000	97	Samsung		9999	18291976	4000	550	LG
9999	2000	88	Samsung		9999	18313208	9000	520	LG
9999	11000	277	Samsung		9999	13137908	2000	100	LG
9999	20000	2198	Onida		9999	83212521	26000	1426	LG
9999	12000	845	Onida		9999	83477386	1000	31	LG
9999	2000	69	Onida		9999	81685008	5000	536	Sansui
9999	5000	174	Onida		9999	18275687	2000	97	Samsung
9999	86000	303	TCL		9999	12886183	2000	88	Samsung
9999	105000	300	TCL		9999	73273257	11000	277	Samsung

we got output as per below.

RecordID	Product ID	Quantity	Amount	Distributor	Transaction ID	Right_Quantity	sale Amount	Distributor/Agent	diff
1	9999	2000	538	LG	18167945	2000	565	LG	-27
2	9999	7000	1541	LG	171861997	7000	1565	LG	-24
3	9999	3000	605	LG	18225328	3000	622	LG	-17
4	9999	4000	531	LG	18291976	4000	550	LG	-19
5	9999	9000	513	LG	18313208	9000	520	LG	-7
6	9999	2000	92	LG	18275687	2000	97	Samsung	-5
7	9999	26000	1421	LG	83212521	26000	1426	LG	-5
8	9999	1000	30	LG	83477386	1000	31	LG	-1
9	9999	5000	510	Sansui	81685008	5000	536	Sansui	-26
10	9999	2000	97	Samsung	13137908	2000	100	LG	-3
11	9999	2000	88	Samsung	12886183	2000	88	Samsung	0
12	9999	11000	277	Samsung	73273257	11000	277	Samsung	0
13	9999	20000	2198	Onida	81562938	20000	2200	Onida	-2
14	9999	12000	845	Onida	18205350	12000	850	Onida	-5
15	9999	2000	69	Onida	18229595	2000	80	Onida	-11
16	9999	5000	174	Onida	18236437	5000	170	Onida	4
17	9999	86000	303	TCL	11557439	86000	300	TCL	3
18	9999	105000	300	TCL	13267154	105000	300	TCL	0

Required output :

RecordID	Product ID	Quantity	Amount	Distributor	Transaction ID	Right_Quantity	sale Amount	Distributor/Agent	diff
1	9999	2000	538	LG	18167945	2000	565	LG	-27
2	9999	7000	1541	LG	171861997	7000	1565	LG	-24
3	9999	3000	605	LG	18225328	3000	622	LG	-17
4	9999	4000	531	LG	18291976	4000	550	LG	-19
5	9999	9000	513	LG	18313208	9000	520	LG	-7
6	9999	2000	92	LG	13137908	2000	100	LG	-8
7	9999	26000	1421	LG	83212521	26000	1426	LG	-5
8	9999	1000	30	LG	83477386	1000	31	LG	-1
9	9999	5000	510	Sansui	81685008	5000	536	Sansui	-26
10	9999	2000	97	Samsung	18275687	2000	97	Samsung	0
11	9999	2000	88	Samsung	12886183	2000	88	Samsung	0
12	9999	11000	277	Samsung	73273257	11000	277	Samsung	0
13	9999	20000	2198	Onida	81562938	20000	2200	Onida	-2
14	9999	12000	845	Onida	18205350	12000	850	Onida	-5
15	9999	2000	69	Onida	18229595	2000	80	Onida	-11
16	9999	5000	174	Onida	18236437	5000	170	Onida	4
17	9999	86000	303	TCL	11557439	86000	300	TCL	3
18	9999	105000	300	TCL	13267154	105000	300	TCL	0

[binuacs · Accepted solution]

@Anjankumar2021 I had to add the agent also in the logic to match your output