Please help!!! Macro/formula or anything

[Koekemoer]

Good day. Can anyone please assist with a formula or macro or whatever, to sort, match/unmatched data? Internet videos just don't do it and I just don't grasp =IF, or vlookup, or what the hell I'm suppose to use. My data looks like this (very shortened version): Those that do match in A (invoice number), B (invoice number), C (invoice value) & D (payment value) to be, for example, in 1 color, or anything. Some clients also made separate payments. I will be forever grateful for any help.

COD to Excel masters-b.xlsx
	A	B	C	D
1	REC-3813	INE01253-Pmt rec'd		717.60
2	REC-3814	INE01254-Pmt rec'd		402.90
3	REC-3815	INE01249-Pmt rec'd		2 203.65
4	-MOVE	BFT001-INA85202-move to bal inv		55.95
5	-MOVE	INA85202-Moved to BFT001		3 285.10
6	INE01249	Tax Invoice - CARLO-#37191	2 203.65
7	INE01251	Tax Invoice - HELENI#37196	373.75
8	INE01252	Tax Invoice - DONALEE	272.85
9	INE01253	Tax Invoice - SIEGFRIED-#PORK SPEK	717.60
10	INE01254	Tax Invoice - MICHEAL-#37217	402.90
11	INA85184	Tax Invoice - MR G.HOMESHOP	1 495.00
12	INA85185	Tax Invoice - YORK	2 813.05
13	INA85202	Tax Invoice - BFT BUTCHERY	3 285.10
14	INC05244	Tax Invoice - FS-DN4--EFT'D	480.00
15	INC05245	Tax Invoice - FS-DN FOR CASH	2 700.00
16	INC05247	Tax Invoice - FS-DN 26-OFFALLS	595.00
17	R920/161	INE01255-Pmt rec'd		1 995.80
18	REC-3816	INE01257-Pmt rec'd		1 230.50
19	REC-3817	INE01259-Pmt rec'd		87.40
20	REC-3818	INE01260-Pmt rec'd		2 363.80
21	REC-3819	INE01261-Pmt rec'd		116.35
22	R920/166	INA85225-Pmt rec'd		850.60
23	R920/174	INE01251-Pmt rec'd		373.75
24	R920/178	INE01264-Pmt rec'd		1 552.50
25	+R920164	INC05244-Pmt rec'd		480.00
26	REC-3682	INA85203-Pmt rec'd		1 767.80
27	INA85203	Tax Invoice - TRANS KALAHARI MEAT	1 767.80
28	INE01255	Tax Invoice - GREENER PASTURES#37188	1 995.80
29	INE01257	Tax Invoice - WILLEM-#37233	1 230.50
30	INE01258	Tax Invoice - MULLER-#PORK SPEK	1 272.35
31	INE01259	Tax Invoice - JONAS-#37235	87.40
32	INE01260	Tax Invoice - MATHEUS-#37245	2 363.80
33	INE01261	Tax Invoice - FRANS-#14	116.35
34	INE01264	Tax Invoice - VALENCIA-OSHIWAMBO	1 552.50
35	INE01265	Tax Invoice - JOHN VAN DEVENTER-#37256	3 190.35
36	INA85216	Tax Invoice - WAKKER BILTONG	6 779.25
37	INA85218	Tax Invoice - CASSIE	1 350.85
38	INA85223	Tax Invoice - MARIUS VAN RENSBURG	5 516.20
39	INA85225	Tax Invoice - CHARLOTTE /JAQUES	850.60
40	INA85213	Tax Invoice - CINDY GROENEWALDT	1 916.80
41	R920/182	INA85242-Pmt rec'd		931.60
42	R920/184	INE01265-Pmt rec'd		3 190.35
43	R920/190	INA85216-Pmt rec'd		6 779.25
44	R920/194	INC05243-Pmt rec'd		1 516.95
45	R920/194	INC05241-Pmt rec'd		225.40
46	REC-3820	INE01269-Pmt rec'd		373.75
47	REC-3821	INE01252-Pmt rec'd		272.85
48	REC-3684	INE01258-Pmt rec'd		803.85
49	REC-3685	INE05242-Pmt rec'd		7 818.00
50	REC-3687	INA85213-Pmt rec'd		1 916.80
51	REC-3688	INA85241-Pmt rec'd		1 629.20
52	REC-3689	INA85245-Pmt rec'd		138.55
53	REC-3690	INA85218-Pmt rec'd		1 350.85
54	+R920191	INA85244-Pmt rec'd-BFT Butch		2 114.30
55	+R920191	INA85410-Pmt rec'd-BFT Butch		2 470.80
56	+R920192	Pmt rec'd-BFT Butch-balance-unk inv		4 401.55
57	-R920180	INA85252-Pmt rec'd		########
58	-MOVE	INA85244-Moved to BFT001		2 114.30
59	-MOVE	INA85410-Moved to BFT001		2 470.80
60	+MOVE	Kierieshop (-R920180)-from 2950/000	########
61	-MOVE	INA85244-to BFT001	2 114.30
62	-MOVE	R920192-balance amount	4 401.55
63	--MOVE	INA85410-Moved to BFT001	2 470.80
64	-REC3684	INE01258-Rev wrong inv nr	803.85
65	+REC3684	INE01268-Pmt rec'd-was INE01258		803.85
66	+R920205	INE01258-4479-Pmt rec'd		1 272.35
67	INC05241	Tax Invoice - E BIERMANN/#37311	225.40
68	INC05242	Tax Invoice - F/SHOP SALES-DN20-24	7 818.00
69	INA85240	Tax Invoice - BEATRICE	546.25
70	INA85241	Tax Invoice - SWARTZ	1 629.20
71	INA85242	Tax Invoice - EMMARENCHIA	931.60
72	INA85244	Tax Invoice - BFT BUTCHERY	2 114.30
73	INA85245	Tax Invoice - GRILL GURU	138.55
74	INA85252	Tax Invoice - KIERIE SHOP	########
75	INE01267	Tax Invoice - #37276-WYNAND	3 523.15
76	INE01268	Tax Invoice - BABS-#37290	803.85
77	INE01269	Tax Invoice - MEV FOURIE-#37307	373.75
78	INC05243	Tax Invoice - FS SALES-DN'S SWIPES	1 516.95
79	ICC00788	Credit Note - INE01267		1 224.00
80	R920/195	INA85273-Pmt rec'd		8 382.00
81	R920/195	Discount		0.90
82	REC3694	Payment INA85282		1 110.00
83	REC3825	Payment Willem		1 230.50
84	REC3825	Payment Sharon		5 844.30
85	REC3825	Payment Rothmann		2 541.30
86	REC3825	Payment Paco		373.75
87	+R920196	INA85223-Pmt rec'd		5 516.20
88	+REC3691	INC05245-Pmt rec'd		2 700.00
89	INE01272	Tax Invoice - WILLEM-#37333	1 230.50
90	INE01273	Tax Invoice - MNR ROTHMAN-37320	2 541.30
91	INE01275	Tax Invoice - PACO/AMILIA	373.75
92	INA85273	Tax Invoice - MARTIN EHAFO	8 382.90
93	INA85274	Tax Invoice - JCT BUTCHERY	########
94	INA85279	Tax Invoice - WHK TOTAL NOORD	8 609.10
95	INA85282	Tax Invoice - DRIE ANKER FARMING	1 110.00
96	INA85283	Tax Invoice - WYNAND	5 773.00
97	INA85284	Tax Invoice - SHARON	5 844.30
98	INA85285	Tax Invoice - MONICA WERNER	1 907.85
99	INA85288	Tax Invoice - R.T.HEISER	3 351.75
100	ICC00765	Credit Note - INA85283		3 724.25
101	R920/208	INA85317-Pmt rec'd		8 218.55
102	R920/213	INE01283-Pmt rec'd		1 586.40
103	R920/214	INA85316-Pmt rec'd		1 121.25
104	R920/216	INE01285-Pmt rec'd		1 156.40
105	R920/218	INA -Pmt rec'd		4 003.85
106	R920/225	INA85285-Pmt rec'd		1 907.85
107	R920/225	INE01290-Pmt rec'd		357.00
108	REC3700	COD Willem		1 230.50
109	REC3700	Dn26 offal		2 070.00
110	REC3700	COD Josua		2 039.15
111	REC3700	COD Tusnelda		1 495.00
112	REC3700	COD Michael		1 660.30
113	REC3700	COD Yvonne		373.75
114	+R920212	INA85274-Pmt rec'd		########
115	-R920218	INE01233-Rev must be to C.O.D2	4 003.85
116	INA85312	Tax Invoice - T.W.KOORTS	9 575.85
117	INA85315	Tax Invoice - G'S DISCOUNT LIQUER	4 685.40
118	INA85316	Tax Invoice - MR G HOME SHOP	1 121.25
119	INA85317	Tax Invoice - EILEEN RAKOW	8 218.55
120	INE01278	Tax Invoice - IVONNE-#	373.75
121	INE01279	Tax Invoice - WILLEM#37384	1 230.50
122	INE01280	Tax Invoice - TUSNELDA-#37378	1 495.00
123	INE01281	Tax Invoice - CLAUDIA-	373.75
124	INE01283	Tax Invoice - #37387-ERONGO MEAT	1 586.40
125	INE01285	Tax Invoice - BOSS ENTERPRISES	1 156.40
126	INE01286	Tax Invoice - MICHEAL#37387	1 660.30
127	INE01287	Tax Invoice - JOSUA-#37392	2 039.15
128	INE01288	Tax Invoice - LOUIS KARSTEN-#37386	1 275.35
129	INE01289	Tax Invoice - RENE	591.70
130	INE01290	Tax Invoice - F/SHOP D/N20	357.00
131	INE01291	Tax Invoice - F/SHOP D/N 23	200.00
132	ICC00766	Credit Note - INE01288		667.75
133	R920/227	INA85288-Pmt rec'd		3 351.75
134	R920/227	INM00495-Pmt rec'd		2 230.00
135	R920/227	INE01281-Pmt rec'd		373.75
136	R920/227	INE01289-Pmt rec'd		591.70
137	INM00494	Tax Invoice - F/SHOP AMILIA	1 345.00
138	INM00495	Tax Invoice - SWIPES/F/SHOP AMILIA	2 230.00
139	INA85321	Tax Invoice - EILENN RAKOW	216.55
140	R920/233	INA85327-Pmt rec'd		3 815.95
141	REC3696	COD Beatrice		546.25
142	REC3697	COD GS Discount Liquor		4 685.40
143	REC3698	Cash factory shop		1 545.00
144	REC3700	COD Din 27-29 Gina		########
145	REC3700	COD L Karstens		667.75
146	REC3700	COD ouma Nora		373.75
147	REC3707	Payment Josua		1 824.85
148	REC3707	Payment Claudia		1 495.00
149	REC3707	Payment Willem		1 262.70
150	+R920236	INC05257-Pmt rec'd		260.00
151	+VAR	INE01288-diff between ct note & rec	60.15
152	INA85325	Tax Invoice - CHIILED DISTRIBUTION	983.90
153	INA85326	Tax Invoice - SUSAN OUMA NORA	373.75
154	INA85327	Tax Invoice - YORK	3 815.95
155	INC05248	Tax Invoice - HEIKO/#37413	2 795.60
156	INE01296	Tax Invoice - ERONGO MEAT-37407	1 046.50
157	INE01297	Tax Invoice - JOSUA-#37409	1 824.85
158	INE01299	Tax Invoice - WYNAND-37414	3 624.35
159	INE01300	Tax Invoice - D/N 27-29 AMILIA	########
160	INE01301	Tax Invoice - HENNIS#37437	1 050.85
161	INE01302	Tax Invoice - CLAUDIA-37446	1 495.00
162	INE01303	Tax Invoice - BAKEREI DEKKER-#37424	431.25
163	INE01304	Tax Invoice - WILLEM	1 262.70
164	INC05257	Tax Invoice - FS-DN 34 38 39 41	1 330.00
165	ICC00775	Credit Note - INE01299		3 624.35
166	REC3706	Payment Bakery Dekker		431.25
167	R920/237	INE01308-Pmt rec'd		########
168	R920/245	INE01307-Pmt rec'd		3 015.85
169	REC4062	INE01525 - JCT		########
170	REC3967	INE01626 - Katazu		833.95
171	REC3968	INE01627 - Willem		1 354.70
172	REC3969	INE01630 - Iomas		1 354.70
173	REC3970	INE01631 - Pundaleni		677.35
174	REC3971	INa85157 - Chanell		3.00
175	REC3972	INE01635 - Meletzky		890.55
176	INC05480	Tax Invoice - GRILL GURU	3 293.60
177	INC05483	Tax Invoice - SJ VD MERWE/#38993	464.30
178	INA86167	Tax Invoice - CHANTEL BARLOW	2 108.50
179	INA86170	Tax Invoice - CHILLED DISTRIBUTION	3 930.45
180	INA86171	Tax Invoice - LOUIS BOTHA	3 865.75
181	INA86178	Tax Invoice - MARINE LIFE BOAT	3 048.45
182	INA86184	Tax Invoice - OLAF HASE	2 163.15
183	INA86172	Tax Invoice - SUSAN	385.25
184	INE01625	Tax Invoice - JCT SUPERMARKET-#39	########
185	INE01626	Tax Invoice - KATAZU ENTERPRISES-#39014	833.95
186	INE01627	Tax Invoice - WILLEM-#39017	1 354.70
187	INE01629	Tax Invoice - MUTU-#39026	817.40
188	INE01630	Tax Invoice - TOMAS-#39043	1 354.70
189	INE01631	Tax Invoice - PANDULENI-#39044	677.35
190	INE01632	Tax Invoice - RUBEN'S CUST/SWIPES	550.00
191	ICC00968	Credit Note - INA86184		2 163.15
192	REC4064	INC05490 - du Plessis		1 886.00
193	REC4066	INC05493 - Genesis		6 422.55
194	REC4067	INE01636 - Frans		501.20
195	REC4069	INE01529 - Mutu		817.40
196	REC4072	INC05501 - DN72-78		########
197	R920/698	INA86195-Pmt rec'd		1 541.00
198	R920/699	INA86196-Pmt rec'd		1 722.10
199	R920/700	INA86134-Pmt rec'd		1 129.85
200	R920/700	INA86133-Pmt rec'd		629.60
201	R920/704	INA86167-Pmt rec'd		2 105.50
202	R920/720	INE01632-5315-Pmt rec'd		550.00
203	R920/721	INE01634-5325-Pmt rec'd		1 571.70
204	R920/721	INE05487-5331-Pmt rec'd		2 991.60
205	R920/721	INA86189-5333-Pmt rec'd		5 696.30
206	R920/721	INE01638-5349-Pmt rec'd		1 197.55
207	R920/721	INE01637-5351-Pmt rec'd		1 354.70
208	R920/721	INE01640-5363-Pmt rec'd		690.00
209	R920/721	INE01651-5371-Pmt rec'd		1 200.00
210	INA86189	Tax Invoice - JOSHUA	5 696.30
211	INA86192	Tax Invoice - YORK	2 384.00
212	INA86195	Tax Invoice - MR G/ HOMESHOP	1 541.00
213	INA86196	Tax Invoice - A/ CLOETE	1 722.10
214	INE01634	Tax Invoice - MEV VAN WYK-#39084	1 571.70
215	INC05487	Tax Invoice - MATTHEWS/#39070	2 991.60
216	INC05490	Tax Invoice - DU PLESSIS/#39077/MUTTON	1 886.00
217	INC05491	Tax Invoice - BENDA/#39083/B-CHOPS	1 354.70
218	INC05493	Tax Invoice - GENISIS BUTCH/#39089	6 422.55
219	INV04835	Tax Invoice	2 163.15
220	INE01635	Tax Invoice - ANNA MALETZKY-#39085	890.55
221	INE01636	Tax Invoice - FRANSE-#39088	501.20
222	INE01637	Tax Invoice - ZENDA-#39090	1 354.70
223	INE01638	Tax Invoice - ZENDA-#39090	1 197.55
224	INE01640	Tax Invoice	690.00
225	INC05501	Tax Invoice - DN72-78-GINA'S F/S OFFCUT	########
226	+R920712	INA86091-Pmt rec'd		2 512.45
Sheet1

 

[Saurabhj]

Hi, what I understand is:

If the invoice number in column A is available in column B, AND the invoice amount in column C matches with payment in Column D then both row should be highlighted.

Is my understanding correct ?

 

[Koekemoer]

Hi, what I understand is:

If the invoice number in column A is available in column B, AND the invoice amount in column C matches with payment in Column D then both row should be highlighted.

Is my understanding correct ?

Hi. Thank you very much for replying. Yes, that is correct. So, if above all agrees, than transaction has been completed in full, that is customer has paid his inv. I need to know which customers have not paid their inv. Thank you once again.

 

[Saba Sabaratnam]

Hi you may want to try this solution.

Enter the following formula in F1 and copy it down

=IF(LEFT(B1,3)="Rec",MID(C1,1,FIND("-",C1,1)-1),"")

And then enter the following formula in G1 and copy it down

IF(IF(LEFT(B1,3)="rec",COUNTIFS(B:B,F1,D:D,E1),COUNTIFS(F:F,B1,E:E,D1))=1,"Paid","")


The data and solution result is shown below.

Kind regards

Saba

 

[Saurabhj]

Hi,

Use below code. the code will highlight the cells which are paid. Also mention in Column E whether amount paid or not.

Sub findRecord()
    Dim lastRow As Integer, rowno As Integer, rowpay As Integer
    Dim amtPaid As String
    
    lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Application.ScreenUpdating = False
    For rowno = 1 To lastRow
    amtPaid = "No"
        For rowpay = 2 To lastRow
            If InStr(1, ActiveSheet.Cells(rowpay, 2), ActiveSheet.Cells(rowno, 1)) > 0 And _
            ActiveSheet.Cells(rowpay, 4) = ActiveSheet.Cells(rowno, 3) Then
                ActiveSheet.Cells(rowno, "A").Interior.Color = vbGreen
                ActiveSheet.Cells(rowno, "C").Interior.Color = vbGreen
                ActiveSheet.Cells(rowpay, "D").Interior.Color = vbGreen
                amtPaid = "Yes"
            End If
        Next
        ActiveSheet.Cells(rowno, "E") = amtPaid
    Next
    Application.ScreenUpdating = True
End Sub

 

[Koekemoer]

Good evening from my side of the world..!!
Thank you very much Saba, your time and effort is appreciated. I'm attaching the file after entering the 2 codes. Is it suppose to look like this? Giving values and so on? May I please abuse your expertise further and ask if you could edit the formula so that it look for anything/everything in B? Because not all rows have REC in it. Sometimes I had to make use of "+", "-", IN*" and "R" to complete the transaction.

Thank you very much Saurabhj........remember, I'm a complete & utter useless human being when it come to this. Where do I add this code, that is how do I get there?

Much appreciated.

Copy of cod 08 02 2021 after entering the 2 formulas.xlsx
	A	B	C	D	E	F	G	H
1	1		INA54060-J Enslin-moved to 3150/000		4 129.97
2	2	INC02658	Tax Invoice - DN51-56	20 230.00
3	3	ICC00416	Credit Note - -INC02658		20 230.00
4	4	+RB92095	INA85202-Pmt rec'd-BFT Butchery		3 229.15			Should also show paid
5	5	-MOVE	INA85202-to BFT001	3 229.15
6	6	-MOVE	INA85202-to BFT001	55.95
7	7	INC05246	Tax Invoice - FS-DN 25 - OFFALLS	1 475.00
8	8	R920/153	INA85185-Pmt rec'd		2 813.05
9	9	REC-3813	INE01253-Pmt rec'd		717.60	INE01253	PAID
10	10	REC-3814	INE01254-Pmt rec'd		402.90	INE01254	PAID
11	11	REC-3815	INE01249-Pmt rec'd		2 203.65	INE01249	PAID
12	12	-MOVE	BFT001-INA85202-move to bal inv		55.95
13	13	-MOVE	INA85202-Moved to BFT001		3 285.10
14	14	INE01249	Tax Invoice - CARLO-#37191	2 203.65			PAID
15	15	INE01251	Tax Invoice - HELENI#37196	373.75
16	16	INE01252	Tax Invoice - DONALEE	272.85			PAID
17	17	INE01253	Tax Invoice - SIEGFRIED-#PORK SPEK	717.60			PAID
18	18	INE01254	Tax Invoice - MICHEAL-#37217	402.90			PAID
19	19	INA85184	Tax Invoice - MR G.HOMESHOP	1 495.00
20	20	INA85185	Tax Invoice - YORK	2 813.05
21	21	INA85202	Tax Invoice - BFT BUTCHERY	3 285.10
22	22	INC05244	Tax Invoice - FS-DN4--EFT'D	480.00
23	23	INC05245	Tax Invoice - FS-DN FOR CASH	2 700.00
24	24	INC05247	Tax Invoice - FS-DN 26-OFFALLS	595.00
25	25	R920/161	INE01255-Pmt rec'd		1 995.80
26	26	REC-3816	INE01257-Pmt rec'd		1 230.50	INE01257	PAID
27	27	REC-3817	INE01259-Pmt rec'd		87.40	INE01259	PAID
28	28	REC-3818	INE01260-Pmt rec'd		2 363.80	INE01260	PAID
29	29	REC-3819	INE01261-Pmt rec'd		116.35	INE01261	PAID
30	30	R920/166	INA85225-Pmt rec'd		850.60			Match
31	31	R920/174	INE01251-Pmt rec'd		373.75
32	32	R920/178	INE01264-Pmt rec'd		1 552.50
33	33	+R920164	INC05244-Pmt rec'd		480.00
34	34	REC-3682	INA85203-Pmt rec'd		1 767.80	INA85203	PAID
35	35	INA85203	Tax Invoice - TRANS KALAHARI MEAT	1 767.80			PAID
36	36	INE01255	Tax Invoice - GREENER PASTURES#37188	1 995.80
37	37	INE01257	Tax Invoice - WILLEM-#37233	1 230.50			PAID
38	38	INE01258	Tax Invoice - MULLER-#PORK SPEK	1 272.35
39	39	INE01259	Tax Invoice - JONAS-#37235	87.40			PAID
40	40	INE01260	Tax Invoice - MATHEUS-#37245	2 363.80			PAID
41	41	INE01261	Tax Invoice - FRANS-#14	116.35			PAID
42	42	INE01264	Tax Invoice - VALENCIA-OSHIWAMBO	1 552.50
43	43	INE01265	Tax Invoice - JOHN VAN DEVENTER-#37256	3 190.35
44	44	INA85216	Tax Invoice - WAKKER BILTONG	6 779.25
45	45	INA85218	Tax Invoice - CASSIE	1 350.85			PAID
46	46	INA85223	Tax Invoice - MARIUS VAN RENSBURG	5 516.20
47	47	INA85225	Tax Invoice - CHARLOTTE /JAQUES	850.60				match
48	48	INA85213	Tax Invoice - CINDY GROENEWALDT	1 916.80			PAID
49	49	R920/182	INA85242-Pmt rec'd		931.60
50	50	R920/184	INE01265-Pmt rec'd		3 190.35
51	51	R920/190	INA85216-Pmt rec'd		6 779.25
52	52	R920/194	INC05243-Pmt rec'd		1 516.95
53	53	R920/194	INC05241-Pmt rec'd		225.40
54	54	REC-3820	INE01269-Pmt rec'd		373.75	INE01269	PAID
55	55	REC-3821	INE01252-Pmt rec'd		272.85	INE01252	PAID
56	56	REC-3684	INE01258-Pmt rec'd		803.85	INE01258
57	57	REC-3685	INE05242-Pmt rec'd		7 818.00	INE05242
58	58	REC-3687	INA85213-Pmt rec'd		1 916.80	INA85213	PAID
59	59	REC-3688	INA85241-Pmt rec'd		1 629.20	INA85241	PAID
60	60	REC-3689	INA85245-Pmt rec'd		138.55	INA85245	PAID
61	61	REC-3690	INA85218-Pmt rec'd		1 350.85	INA85218	PAID
62	62	+R920191	INA85244-Pmt rec'd-BFT Butch		2 114.30
63	63	+R920191	INA85410-Pmt rec'd-BFT Butch		2 470.80
64	64	+R920192	Pmt rec'd-BFT Butch-balance-unk inv		4 401.55
65	65	-R920180	INA85252-Pmt rec'd		29 412.25
66	66	-MOVE	INA85244-Moved to BFT001		2 114.30
67	67	-MOVE	INA85410-Moved to BFT001		2 470.80
68	68	+MOVE	Kierieshop (-R920180)-from 2950/000	29 412.25
69	69	-MOVE	INA85244-to BFT001	2 114.30
70	70	-MOVE	R920192-balance amount	4 401.55
71	71	--MOVE	INA85410-Moved to BFT001	2 470.80
72	72	-REC3684	INE01258-Rev wrong inv nr	803.85
73	73	+REC3684	INE01268-Pmt rec'd-was INE01258		803.85
74	74	+R920205	INE01258-4479-Pmt rec'd		1 272.35
75	75	INC05241	Tax Invoice - E BIERMANN/#37311	225.40
76	76	INC05242	Tax Invoice - F/SHOP SALES-DN20-24	7 818.00
77	77	INA85240	Tax Invoice - BEATRICE	546.25
78	78	INA85241	Tax Invoice - SWARTZ	1 629.20			PAID
79	79	INA85242	Tax Invoice - EMMARENCHIA	931.60
80	80	INA85244	Tax Invoice - BFT BUTCHERY	2 114.30
81	81	INA85245	Tax Invoice - GRILL GURU	138.55			PAID
82	82	INA85252	Tax Invoice - KIERIE SHOP	29 412.25
83	83	INE01267	Tax Invoice - #37276-WYNAND	3 523.15
84	84	INE01268	Tax Invoice - BABS-#37290	803.85
85	85	INE01269	Tax Invoice - MEV FOURIE-#37307	373.75			PAID
86	86	INC05243	Tax Invoice - FS SALES-DN'S SWIPES	1 516.95
87	87	ICC00788	Credit Note - INE01267		1 224.00
88	88	R920/195	INA85273-Pmt rec'd		8 382.00
89	89	R920/195	Discount		0.90
90	90	REC3694	Payment INA85282		1 110.00	#VALUE!
91	91	REC3825	Payment Willem		1 230.50	#VALUE!
92	92	REC3825	Payment Sharon		5 844.30	#VALUE!
93	93	REC3825	Payment Rothmann		2 541.30	#VALUE!
94	94	REC3825	Payment Paco		373.75	#VALUE!
95	95	+R920196	INA85223-Pmt rec'd		5 516.20
96	96	+REC3691	INC05245-Pmt rec'd		2 700.00
97	97	INE01272	Tax Invoice - WILLEM-#37333	1 230.50
98	98	INE01273	Tax Invoice - MNR ROTHMAN-37320	2 541.30
99	99	INE01275	Tax Invoice - PACO/AMILIA	373.75
100	100	INA85273	Tax Invoice - MARTIN EHAFO	8 382.90
101	101	INA85274	Tax Invoice - JCT BUTCHERY	18 638.65
102	102	INA85279	Tax Invoice - WHK TOTAL NOORD	8 609.10
103	103	INA85282	Tax Invoice - DRIE ANKER FARMING	1 110.00				Why does it show VALUE?
104	104	INA85283	Tax Invoice - WYNAND	5 773.00
105	105	INA85284	Tax Invoice - SHARON	5 844.30
106	106	INA85285	Tax Invoice - MONICA WERNER	1 907.85
107	107	INA85288	Tax Invoice - R.T.HEISER	3 351.75
108	108	ICC00765	Credit Note - INA85283		3 724.25
109	109	R920/208	INA85317-Pmt rec'd		8 218.55
110	110	R920/213	INE01283-Pmt rec'd		1 586.40
111	111	R920/214	INA85316-Pmt rec'd		1 121.25
112	112	R920/216	INE01285-Pmt rec'd		1 156.40
113	113	R920/218	INA -Pmt rec'd		4 003.85
114	114	R920/225	INA85285-Pmt rec'd		1 907.85
115	115	R920/225	INE01290-Pmt rec'd		357.00
116	116	REC3700	COD Willem		1 230.50	#VALUE!
117	117	REC3700	Dn26 offal		2 070.00	#VALUE!
118	118	REC3700	COD Josua		2 039.15	#VALUE!
119	119	REC3700	COD Tusnelda		1 495.00	#VALUE!
120	120	REC3700	COD Michael		1 660.30	#VALUE!
121	121	REC3700	COD Yvonne		373.75	#VALUE!
122	122	+R920212	INA85274-Pmt rec'd		18 638.65
123	123	-R920218	INE01233-Rev must be to C.O.D2	4 003.85
124	124	INA85312	Tax Invoice - T.W.KOORTS	9 575.85
125	125	INA85315	Tax Invoice - G'S DISCOUNT LIQUER	4 685.40
126	126	INA85316	Tax Invoice - MR G HOME SHOP	1 121.25
127	127	INA85317	Tax Invoice - EILEEN RAKOW	8 218.55
128	128	INE01278	Tax Invoice - IVONNE-#	373.75
129	129	INE01279	Tax Invoice - WILLEM#37384	1 230.50
130	130	INE01280	Tax Invoice - TUSNELDA-#37378	1 495.00
131	131	INE01281	Tax Invoice - CLAUDIA-	373.75
132	132	INE01283	Tax Invoice - #37387-ERONGO MEAT	1 586.40
133	133	INE01285	Tax Invoice - BOSS ENTERPRISES	1 156.40
134	134	INE01286	Tax Invoice - MICHEAL#37387	1 660.30
135	135	INE01287	Tax Invoice - JOSUA-#37392	2 039.15
136	136	INE01288	Tax Invoice - LOUIS KARSTEN-#37386	1 275.35
137	137	INE01289	Tax Invoice - RENE	591.70
138	138	INE01290	Tax Invoice - F/SHOP D/N20	357.00
139	139	INE01291	Tax Invoice - F/SHOP D/N 23	200.00
140	140	ICC00766	Credit Note - INE01288		667.75
141	141	R920/227	INA85288-Pmt rec'd		3 351.75
142	142	R920/227	INM00495-Pmt rec'd		2 230.00
143	143	R920/227	INE01281-Pmt rec'd		373.75
144	144	R920/227	INE01289-Pmt rec'd		591.70
145	145	INM00494	Tax Invoice - F/SHOP AMILIA	1 345.00
146	146	INM00495	Tax Invoice - SWIPES/F/SHOP AMILIA	2 230.00
147	147	INA85321	Tax Invoice - EILENN RAKOW	216.55
148	148	R920/233	INA85327-Pmt rec'd		3 815.95
149	149	REC3696	COD Beatrice		546.25	#VALUE!
150	150	REC3697	COD GS Discount Liquor		4 685.40	#VALUE!
Sheet1

Cell Formulas
Range	Formula
F1:F12	F1	=IF(LEFT(B1,3)="rec",MID(C1,1,FIND("-",C1,1)-1),"")
G1:G150	G1	=IF(IF(LEFT(B1,3)="REC",COUNTIFS(B:B,F1,D:D,E1),COUNTIFS(F:F,B1,E:E,D1))=1,"PAID","")
F13:F150	F13	=IF(LEFT(B13,3)="REC",MID(C13,1,FIND("-",C13,1)-1),"")

 

[Saba Sabaratnam]

Hi

I had to change my solution as your data is more complex than I thought.

Please see my spreadsheet layout for your data.

Enter three letter prefixes of your invoices / receipts in M1 to M6. in the future, if you have more invoice prefixes you can include in the list and expand the formula to include it.

Enter the following array formula by pressing Shift + Control +Enter in H2 and copy it down. It is very important to enter this formula as array formula. If not, this solution will not work.

=IFERROR(IFERROR(MID(C2,SUM(IF(ISNUMBER(SEARCH($M$1:$M$6,C2,1)),SEARCH($M$1:$M$6,C2,1))),8),MID(D2,SUM(IF(ISNUMBER(SEARCH($M$1:$M$6,D2,1)),SEARCH($M$1:$M$6,D2,1))),8)),"No invoice Number")

Enter this formula in I2 and copy it down

=IF(E2<>0,E2,F2)


And finally enter the following formula in J2 and copy it down.

=IF(COUNTIFS(H:H,H2,I:I,I2)>=2,"Paid","")


I noticed that some of your entries / records / rows do not have any invoice number and the formula will put "No Invoice Number' against the row.

Kind regards

Saba

 

[Saurabhj]

Hi,

Please download the sheet. (Download)

 

[Koekemoer]

Good evening all (once again)... If you tell me to bugger off, I will understand..! Thank you very much for your patience & efforts with my issue. I'm so much further than a couple of days ago.
If I may abuse your kindness, please check for me if I have all the formulas & copying correct? As per my sheet, some rows show PAID, although there aren't payments for those invoices. Also if possible, can you add a highlight to the rows that do match/or those that don't? Don't worry about the "no inv numbers", I will match them. I wish I could stick you for a drink just to say thank you...I really hope I will be out of your hair very soon..

COD with final formulas added-09 02 2021.xlsx
	A	B	C	D	E	F	G	H	I	J	K
2540	2539	2539	INE02249	Tax Invoice - MUTU-#42042	5123.05			No invoice Number		Paid
2541	2540	2540	INE02250	Tax Invoice - MINETTE-#42043	3012.50			INETTE-#			Why no "paid"
2542	2541	2541	INE02252	Tax Invoice - WHK GYNASUIM-#42044	3107.85			No invoice Number		Paid
2543	2542	2542	INE02255	Tax Invoice - # 42079	1377.70			No invoice Number		Paid
2544	2543	2543	INM00591	Tax Invoice - D/N 006	14440.00			No invoice Number		Paid
2545	2565	2565	+DEP279	INV04910-Pmt rec'd		28022.70		No invoice Number	28022.70
2546	2566	2566	+DEP279	INA87077-Pmt rec'd		71390.60		No invoice Number	71390.60
2547	2567	2567	REC 4373	INA87694 - G'S disc		3778.10		INA87694	3778.10
2548	2573	2573	INM00600	Tax Invoice - #42137 LLOYS	1953.55			No invoice Number		Paid
2549	2578	2578	INE02276	Tax Invoice - # 42162 RITUNGEE/AMILIA	505.45			RITUNGEE		Paid
2550	2579	2579	INE02277	Tax Invoice - STEVEN-#42142	1758.10			No invoice Number		Paid
2551	2580	2580	INE02280	Tax Invoice - BOETA-#42153	130.75			No invoice Number		Paid
2552	2581	2581	REC4376	INE02268 - Mutu		5634.50		INE02268	5634.50
2553	2582	2582	W2362	INE02276 - Ritungee		505.45		itungee	505.45
2554	2583	2583	W2363	INA87714 - Spesbona		5274.30		INA87714	5274.30
2555	2584	2584	W2364	INE02277 - Steven		1758.10		INE02277	1758.10
2556	2585	2585	W2365	INE02280 - Boeta		130.75		INE02280	130.75
2557	2586	2586	W2366	INE02286 - Michael		917.70		INE02286	917.70
2558	2587	2587	INM00611	Deleted Tax Invoice - #42241/AMILIA	0.00			No invoice Number		Paid
2559	2588	2588	DEP327	INC06092-Pmt rec'd		2567.40		No invoice Number	2567.40
2560	2589	2589	DEP329	INE02287-Pmt rec'd		2273.55		No invoice Number	2273.55
2561	2590	2590	DEP330	INE02291-Pmt rec'd		1974.55		No invoice Number	1974.55	Paid
2562	2591	2591	DEP331	INC06094-Pmt rec'd		2491.20		No invoice Number	2491.20
2563	2592	2592	DEP332	INC06095-Pmt rec'd		2259.68		No invoice Number	2259.68
2564	2593	2593	DEP334	INC06096-Pmt rec'd		1289.20		No invoice Number	1289.20
2565	2594	2594	DEP337	INM00612-Pmt rec'd		300.00		No invoice Number	300.00	Paid
2566	2595	2595	DEP346	INE02269-7893-Pmt rec'd		6615.95		No invoice Number	6615.95
2567	2596	2596	DEP346	INM00600-7897-Pmt rec'd		1953.55		No invoice Number	1953.55
2568	2597	2597	DEP346	INE02275-7911-Pmt rec'd		5522.55		No invoice Number	5522.55
2569	2598	2598	+DEP319	INE02281-Pmt rec'd		1633.75		No invoice Number	1633.75
2570	2599	2599	W2367	INA87745 - Spesbona		7560.95		INA87745	7560.95
2571	2600	2600	W2369	INE02292 - Mathew		2173.75		INE02292	2173.75
2572	2601	2601	W2373	INM00615 - Bertha		47.25		tha	47.25
2573	2602	2602	W2374	INE02297 - Tusnelda		1605.40		INE02297	1605.40
2574	2603	2603	+DEP339	INA87456-Pmt rec'd		5158.00		No invoice Number	5158.00
2575	2604	2604	INM00608	Tax Invoice - #42211-SALAMON/AMILIA	1469.70			No invoice Number		Paid
2576	2605	2605	INA87745	Tax Invoice - SPESBONA MARK	7560.95			RK		Paid
2577	2606	2606	INE02281	Tax Invoice - CAPITAL GROW-42154	1633.75			ROW-4215
2578	2607	2607	INE02282	Tax Invoice - MEV SWARTS-#42187	1362.75			RTS-#421
2579	2608	2608	INE02283	Tax Invoice - MOUNT KARAS-#42188	3168.80			RAS-#421
2580	2609	2609	INE02286	Tax Invoice - MICHEAL-#42214	917.70			No invoice Number		Paid
2581	2610	2610	INE02287	Tax Invoice - SFM -#42210	2273.55			No invoice Number		Paid
2582	2611	2611	INE02288	Tax Invoice - THE ROCK LODGE-#42238	2137.25			ROCK LOD		Paid
2583	2612	2612	INE02290	Tax Invoice - MASHONGO-#42248	1357.75			No invoice Number		Paid
2584	2613	2613	INE02291	Tax Invoice - MNR VERMAAK#42241	1974.55			R VERMAA
2585	2614	2614	INE02292	Tax Invoice - #42250 MATHEW	2173.75			No invoice Number		Paid
2586	2615	2615	INC06094	Tax Invoice - CMDS MEAT/#42229	2491.20			No invoice Number		Paid
2587	2616	2616	INC06095	Tax Invoice - JENSEN-ORDER 1 + 2	2598.65			RDER 1 +
2588	2617	2617	INC06096	Tax Invoice - KRAEHEMANN/#42249	1289.20			RAEHEMAN
2589	2618	2618	INM00612	Tax Invoice - #42211	300.00			No invoice Number		Paid
2590	2619	2619	INM00609	Tax Invoice - #42212	401.35			No invoice Number		Paid
2591	2624	2624	INM00613	Tax Invoice - WEST COAST	4284.10			No invoice Number		Paid
2592	2625	2625	DEP349	INC06095-Pmt rec'd		338.97		No invoice Number	338.97
2593	2626	2626	W2370	INE02295 - Ritungee		521.40		itungee	521.40
2594	2629	2629	REC4379	INE02283 - Mount Karas		3168.80		as	3168.80
2595	2633	2633	DEP357	INM00613-Pmt rec'd-West Coast		5041.95		No invoice Number	5041.95
2596	2634	2634	DEP362	INM00608-7949-Pmt rec'd		1469.70		No invoice Number	1469.70	Paid
2597	2635	2635	DEP362	INE02288-7953-Pmt rec'd		2137.25		No invoice Number	2137.25
2598	2636	2636	DEP362	INE02290-8067-Pmt rec'd		1357.75		No invoice Number	1357.75
2599	2641	2641	INA87758	Tax Invoice - HENK KEMPEN	1457.55			No invoice Number		Paid
2600	2642	2642	INA87759	Tax Invoice - OMATEKO LODGE	2843.20			No invoice Number		Paid
2601	2643	2643	INA87770	Tax Invoice - MR G HOMESHOP	2006.75			R G HOME		Paid
2602	2644	2644	INA87783	Tax Invoice - PERCY	387.05			RCY
2603	2645	2645	INE02293	Tax Invoice - CHILLED DIST-#42192	5867.00			No invoice Number		Paid
2604	2646	2646	INE02294	Tax Invoice - MEV FOURIE-#42270	401.35			RIE-#422
2605	2647	2647	INE02295	Tax Invoice - RITUNGEE-#42271	521.40			RITUNGEE		Paid
2606	2648	2648	INA87793	Tax Invoice - MARTIN ROSH PINAH	4313.05					Paid
2607	2649	2649	DEP366	INC06103-Pmt rec'd		14667.45		No invoice Number	14667.45
2608	2650	2650	DEP367	INA87793-Pmt rec'd		4313.05		No invoice Number	4313.05
2609	2651	2651	DEP376	INE02296-8190-Pmt rec'd		474.05		No invoice Number	474.05
2610	2652	2652	REC4390	INE02301 - Chilled		4340.10		INE02301	4340.10
2611	2653	2653	REC4394	D/N 008		4670.00		No invoice Number	4670.00
2612	2654	2654	INB01778	Tax Invoice - BERTUS MYBURGH	985.30			RTUS MYB
2613	2667	2667	DEP384	INM00609-Pmt rec'd		851.35		No invoice Number	851.35
2614	2673	2673	INE02309	Tax Invoice - TOTAL WHK-#PORK SPEK	2242.50			RK SPEK		Paid	There isn't a pmt for this inv
2615	2674	2674	INE02310	Tax Invoice - MATHEUS	3345.05			No invoice Number		Paid
2616	2675	2675	INE02311	Tax Invoice - HEINRICH VAN ZYL	261.85			RICH VAN
2617	2676	2676	INE02312	Tax Invoice - DESERE-#42383	2904.65			RE-#4238
2618	2677	2677	INE02315	Tax Invoice - FRIEDA-#42385	287.45			RIEDA-#4
2619	2678	2678	INE02317	Tax Invoice - JOSUA-#42396	8954.70			No invoice Number		Paid	There isn't a pmt for this inv
2620	2679	2679	INM00629	Tax Invoice - D/N 008	4670.00			No invoice Number		Paid	There isn't a pmt for this inv
Sheet1

Cell Formulas
Range	Formula
H2540:H2620	H2540	=IFERROR(IFERROR(MID(D2540,SUM(IF(ISNUMBER(SEARCH($M$1:$M$8,D2540,1)),SEARCH($M$1:$M$6,D2540,1))),8),MID(E2540,SUM(IF(ISNUMBER(SEARCH($M$1:$M$6,E2540,1)),SEARCH($M$1:$M$6,E2540,1))),8)),"No invoice Number")
I2540:I2620	I2540	=IF(F2540<>0,F2540,G2540)
J2540:J2620	J2540	=IF(COUNTIFS(H:H,H2540,I:I,I2540)>=2,"Paid","")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Saba Sabaratnam]

Hi,


I am happy to help.

I think that your formulas are referring to wrong cells.

I adjusted formulas using your worksheet layout. Enter this formula as array by pressing Shirt + Control + Enter in H2


=IFERROR(IFERROR(MID(C2,SUM(IF(ISNUMBER(SEARCH($M$1:$M$6,C2,1)),SEARCH($M$1:$M$6,C2,1))),8),MID(D2,SUM(IF(ISNUMBER(SEARCH($M$1:$M$6,D2,1)),SEARCH($M$1:$M$6,D2,1))),8)),"No invoice Number")

Enter this formula in I2 and copy it down

=IF(E2<>0,E2,F2)


Enter this formula in J2 and copy it down

=IF(COUNTIFS(H:H,H2,I:I,I2)>=2,"Paid","")

You can check which cells your formulas are referring to by putting your curser in front of = sign in the Formula bar as shown below.

Happy to help

Kind regards

Saba