Excel Formula help

[manoj18071965]

The data on the left are the invoices Data on right side are the receipts I need to allocate the receipt against invoices and where there is partial payment the adjustment will come from second payment Secondly I also need to identify the invoices are adjusted against which receipt so each receipt number will have a unique number that has to be infront of invoices

Internal ID	Date	RM Inv	Customer #	Amount	Receipt#	Allocation			Customer#	Receipt#	Receipt Amt
15727	5/6/2011	100030-0001	C1014	3,500					C1044	50001	38,123
15733	5/13/2011	100037-0001	C1014	3,500					C1044	50002	760,626
15753	5/29/2011	100030-0002	C1014	3,096					C1044	50003	51,813
15766	6/2/2011	100053-0001	C1014	5,250					C1044	50004	38,123
15776	6/7/2011	100057-0001	C1014	5,250					C1014	50005	406,206
15789	6/13/2011	100037-0002	C1014	3,500					C1014	50006	16,356
15832	7/2/2011	100053-0002	C1014	5,250					C1014	50007	30,454
15845	7/7/2011	100057-0002	C1014	5,250					C1014	50008	24,595
15868	7/13/2011	100037-0003	C1014	3,500
15916	8/2/2011	100053-0003	C1014	5,250
15921	8/6/2011	100103-0001	C1014	5,250
15922	8/7/2011	100057-0003	C1014	5,250
15944	8/13/2011	100037-0004	C1014	3,500					1,366,295		1,366,295
15958	8/18/2011	100120-0001	C1044	10,000
15973	8/21/2011	100057-0004	C1014	2,423
16035	9/2/2011	100053-0004	C1014	5,250
16068	9/6/2011	100103-0002	C1014	5,250
16093	9/13/2011	100037-0005	C1014	3,500
16116	9/18/2011	100120-0002	C1044	10,000
16191	10/2/2011	100053-0005	C1014	5,250
16205	10/6/2011	100103-0003	C1014	5,250
16221	10/10/2011	100195-0001	C1044	8,400
16243	10/13/2011	100037-0006	C1014	3,500
16259	10/18/2011	100120-0003	C1044	10,000
16277	10/22/2011	100207-0001	C1014	10,500
16367	11/2/2011	100053-0006	C1014	5,250
16380	11/6/2011	100103-0004	C1014	5,250
16396	11/10/2011	100195-0002	C1044	8,400
16413	11/13/2011	100037-0008	C1014	3,500
16435	11/18/2011	100120-0004	C1044	10,000
16455	11/22/2011	100207-0002	C1014	10,500
16543	12/1/2011	100281-0001	C1014	3,500
16557	12/2/2011	100053-0007	C1014	5,250
16567	12/4/2011	100195-0003	C1044	4,307
16565	12/4/2011	100120-0005	C1044	5,385
16573	12/5/2011	100281-0002	C1014	538
16577	12/6/2011	100103-0005	C1014	5,250
16599	12/10/2011	100195-0004	C1044	2,800
16616	12/13/2011	100037-0009	C1014	3,500
16806	1/3/2012	100053-0008	C1014	5,250
16823	1/8/2012	100103-0006	C1014	5,250
16847	1/10/2012	100195-0005	C1044	2,800
16881	1/15/2012	100037-0010	C1014	3,500
16940	1/17/2012	100366-0001	C1014	10,500
17055	2/2/2012	100053-0009	C1014	5,250
17075	2/6/2012	100103-0007	C1014	5,250
17116	2/12/2012	100366-0002	C1014	7,673
17118	2/12/2012	100195-0006	C1044	2,800
17138	2/14/2012	100037-0011	C1014	3,500
17316	3/4/2012	100053-0010	C1014	5,250
17333	3/6/2012	100103-0008	C1014	5,250
17359	3/10/2012	100195-0008	C1044	2,800
17384	3/13/2012	100037-0012	C1014	3,500
17464	3/24/2012	100195-0009	C1044	1,400
17488	3/26/2012	100103-0009	C1014	3,635
17487	3/26/2012	100037-0013	C1014	1,615
17554	3/31/2012	100053-0011	C1014	4,846
19093	8/16/2012	100765-0001	C1014	5,250
19346	9/4/2012	100828-0001	C1014	2,200
19834	10/20/2012	100864-0001	C1044	3,600
20197	11/20/2012	100864-0002	C1044	3,600
20206	11/21/2012	100928-0001	C1014	5,250
20568	12/20/2012	100864-0003	C1044	3,600
21022	1/20/2013	100864-0004	C1044	3,600
21408	2/20/2013	100864-0005	C1044	3,600
21787	3/20/2013	100864-0006	C1044	3,600
22212	4/20/2013	100864-0007	C1044	3,600
22676	5/20/2013	100864-0008	C1044	3,600
23147	6/20/2013	100864-0009	C1044	3,600
23652	7/20/2013	100864-0010	C1044	3,600
24115	8/7/2013	100864-0011	C1044	2,077
31170	8/24/2014	102062-0001	C1014	4,000
31854	9/24/2014	102062-0002	C1014	4,000
32454	10/15/2014	102158-0001	C1044	15,500
32616	10/26/2014	102174-0001	C1044	3,100
32615	10/26/2014	102158-0002	C1044	2,862
32647	10/28/2014	102181-0001	C1044	3,100
32646	10/28/2014	102174-0002	C1044	18,600
32649	10/29/2014	102174-0003	C1044	238
33109	11/5/2014	102217-0001	C1044	3,121
33108	11/5/2014	102174-0004	C1044	5,365
33107	11/5/2014	102158-0003	C1044	3,815
33178	11/13/2014	102202-0002	C1014	5,250
33231	11/18/2014	102202-0003	C1014	606
33257	11/20/2014	102217-0002	C1044	3,179
33633	11/30/2014	102174-0005	C1044	15,500
33630	11/30/2014	102158-0004	C1044	6,200
34138	12/31/2014	102174-0006	C1044	15,500
34137	12/31/2014	102158-0005	C1044	6,200
34560	1/7/2015	102174-0007	C1044	596
34761	1/26/2015	102174-0008	C1044	2,504
35160	1/31/2015	102174-0009	C1044	9,300
35159	1/31/2015	102158-0006	C1044	6,200
35337	2/16/2015	102158-0007	C1044	1,550
35764	2/28/2015	102174-0010	C1044	9,300
35763	2/28/2015	102158-0008	C1044	3,100
36245	3/31/2015	300721-0002	C1044	37,384
36244	3/31/2015	102174-0011	C1044	9,300
36243	3/31/2015	102158-0009	C1044	3,100
36696	4/7/2015	300721-0003	C1044	1,846
36697	4/8/2015	300721-0004	C1044	4,308
37296	4/30/2015	102174-0012	C1044	9,300
37295	4/30/2015	102158-0010	C1044	3,100
37694	5/31/2015	102174-0013	C1044	9,300
37693	5/31/2015	102158-0011	C1044	3,100
38938	6/30/2015	102174-0014	C1044	9,300
38937	6/30/2015	102158-0012	C1044	3,100
39674	7/31/2015	102174-0015	C1044	9,300
39673	7/31/2015	102158-0013	C1044	3,100
40218	8/31/2015	102174-0016	C1044	9,300
40217	8/31/2015	102158-0014	C1044	3,100
40969	9/30/2015	102174-0017	C1044	3,219
40968	9/30/2015	102158-0015	C1044	3,100
52052	12/23/2016	103310-0001	C1014	12,000
52051	12/23/2016	103309-0001	C1014	6,000
52815	1/23/2017	103310-0002	C1014	12,000
52814	1/23/2017	103309-0002	C1014	6,000
53467	2/20/2017	103310-0003	C1014	9,231
53502	2/23/2017	103309-0003	C1014	6,000
54204	3/23/2017	103309-0004	C1014	6,000
54737	4/3/2017	103309-0005	C1014	2,077
58804	10/31/2017	103837-0001	C1044	9,627
59391	11/30/2017	103908-0001	C1044	1,385
59390	11/30/2017	103890-0001	C1044	5,077
59389	11/30/2017	103837-0002	C1044	15,000
59986	12/17/2017	103908-0002	C1044	10,615
60390	12/31/2017	103890-0002	C1044	12,000
60389	12/31/2017	103837-0003	C1044	14,998
61541	1/31/2018	103940-0002	C1044	11,550
61540	1/31/2018	103890-0003	C1044	12,490
61539	1/31/2018	103837-0004	C1044	15,750
62285	2/28/2018	104066-0001	C1044	8,238
62284	2/28/2018	103940-0003	C1044	11,550
62283	2/28/2018	103890-0004	C1044	12,600
62282	2/28/2018	103837-0005	C1044	15,750
62759	3/15/2018	103837-0006	C1044	7,269
62777	3/18/2018	104089-0001	C1014	44,100
63432	3/31/2018	104123-0001	C1014	2,940
63431	3/31/2018	104119-0001	C1014	5,089
63430	3/31/2018	104115-0001	C1014	16,478
63391	3/31/2018	104091-0002	C1044	12,600
63390	3/31/2018	104066-0002	C1044	12,600
63389	3/31/2018	103940-0004	C1044	11,550
63388	3/31/2018	103890-0005	C1044	12,600
63491	4/6/2018	104102-0001	C1014	6,300
63526	4/12/2018	104123-0002	C1014	1,470
63525	4/12/2018	104115-0002	C1014	4,361
63547	4/16/2018	104119-0002	C1014	3,731
63545	4/16/2018	104102-0002	C1014	1,696
63603	4/23/2018	104091-0003	C1044	9,208
63602	4/23/2018	103890-0006	C1044	9,208
64069	4/30/2018	104066-0003	C1044	12,600
64068	4/30/2018	103940-0005	C1044	10,217
64618	5/31/2018	104066-0004	C1044	12,600
64617	5/31/2018	103940-0006	C1044	11,550
65471	6/30/2018	104066-0005	C1044	12,593
65470	6/30/2018	103940-0007	C1044	9,328
66581	7/31/2018	104296-0001	C1044	4,239
66580	7/31/2018	104066-0006	C1044	12,600
66579	7/31/2018	103940-0008	C1044	11,550
66887	8/31/2018	104296-0002	C1044	15,750
66886	8/31/2018	104066-0007	C1044	12,600
66885	8/31/2018	103940-0009	C1044	11,328
67610	9/30/2018	104296-0003	C1044	15,750
67609	9/30/2018	104066-0008	C1044	12,300
67608	9/30/2018	103940-0010	C1044	10,073
68372	10/31/2018	600131-0001	C1044	5,775
68371	10/31/2018	104425-0001	C1044	7,916
68370	10/31/2018	104296-0004	C1044	15,750
68369	10/31/2018	104066-0009	C1044	12,600
68368	10/31/2018	103940-0011	C1044	9,772
69307	11/30/2018	600131-0002	C1044	11,550
69306	11/30/2018	104425-0002	C1044	2,423
69305	11/30/2018	104296-0005	C1044	15,750
69304	11/30/2018	104066-0010	C1044	12,600
69303	11/30/2018	103940-0012	C1044	11,550
130440	1/2/2019	104517-0001	C1014	8,885
130489	1/9/2019	104517-0002	C1014	4,523
130509	1/15/2019	104517-0003	C1014	9,692
132135	3/31/2019	104625-0001	C1014	16,356	16356
205902	4/21/2019	104625-0002	C1014	12,975
205915	4/22/2019	104625-0003	C1014	5,888
206463	4/30/2019	104660-0001	C1014	24,595	24595
206462	4/30/2019	104659-0002	C1014	5,654
205989	4/30/2019	104659-0001	C1014	5,937	#N/A
				1,366,295

<colgroup><col><col><col><col><col><col span="4"><col><col><col></colgroup><tbody>
</tbody>

 

[manoj18071965]

Please advise!

 

[Sam_D_Ben]

Please check if this is the kind of formula help you are looking for.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K
1	Internal ID	Date	RM Inv	Customer #	Amount	Receipt#	Allocation Receipt		Customer#	Receipt#	Receipt Amt
2	15727	40669	100030-0001	C1014	3500	-	00001-15727		C1044	50001	38123
3	15733	40676	100037-0001	C1014	3500	-	70001-15733		C1044	50002	760626
4	15753	40692	100030-0002	C1014	3096	-	00002-15753		C1044	50003	51813
5	15766	40696	100053-0001	C1014	5250	-	30001-15766		C1044	50004	38123
6	15776	40701	100057-0001	C1014	5250	-	70001-15776		C1014	50005	406206
7	15789	40707	100037-0002	C1014	3500	-	70002-15789		C1014	50006	16356
8	15832	40726	100053-0002	C1014	5250	-	30002-15832		C1014	50007	30454
9	15845	40731	100057-0002	C1014	5250	-	70002-15845		C1014	50008	24595
Sheet25

Cell Formulas
Range	Formula
F2	=IFERROR(VLOOKUP(VALUE(SUBSTITUTE(RIGHT($C2,6),"-","")),$J$2:$J$9,1,0),"-")
G2	=IF(LEN(VALUE(SUBSTITUTE(RIGHT($C2,6),"-","")))=1,(SUBSTITUTE(RIGHT($C2,6),"-","")&"-"&$A2),(SUBSTITUTE(RIGHT($C2,6),"-","")&"-"&$A2))

 

[manoj18071965]

Dear Sam,

Thank you.

Kindly advise Receipt#50001 having Receipt Amt 38123 (For Multiple Invoiced amount), how can be traced that 38123 combined amount is for which invoices. - Manoj

 

[Sam_D_Ben]

Please check the accuracy of the master data from your end. Cause looked up value is more that the receipt amount. And for to find the multiple invoice you can either use "Filter" or "Pivot" or even can apply "Formula'.