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

 

[Saba Sabaratnam]

For highlighting paid rows, use Conditional Formatting as shown below.


Select A2 to I2
Go to Conditional Formatting
Select 'Use a formula to determine which cells to format'
Enter the formula as shown in the screen shot

After closing the Conditional Formatting dialog box, use Format painter to pain the formatting cells from A3 to I3

Kind regards

Saba