Please help!!! Macro/formula or anything

Koekemoer

New Member
Joined
Feb 7, 2021
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
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. :mad: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
ABCD
1REC-3813INE01253-Pmt rec'd 717.60
2REC-3814INE01254-Pmt rec'd 402.90
3REC-3815INE01249-Pmt rec'd 2 203.65
4-MOVEBFT001-INA85202-move to bal inv 55.95
5-MOVEINA85202-Moved to BFT001 3 285.10
6INE01249Tax Invoice - CARLO-#371912 203.65
7INE01251Tax Invoice - HELENI#37196373.75
8INE01252Tax Invoice - DONALEE272.85
9INE01253Tax Invoice - SIEGFRIED-#PORK SPEK717.60
10INE01254Tax Invoice - MICHEAL-#37217402.90
11INA85184Tax Invoice - MR G.HOMESHOP1 495.00
12INA85185Tax Invoice - YORK2 813.05
13INA85202Tax Invoice - BFT BUTCHERY3 285.10
14INC05244Tax Invoice - FS-DN4--EFT'D480.00
15INC05245Tax Invoice - FS-DN FOR CASH2 700.00
16INC05247Tax Invoice - FS-DN 26-OFFALLS595.00
17R920/161INE01255-Pmt rec'd 1 995.80
18REC-3816INE01257-Pmt rec'd 1 230.50
19REC-3817INE01259-Pmt rec'd 87.40
20REC-3818INE01260-Pmt rec'd 2 363.80
21REC-3819INE01261-Pmt rec'd 116.35
22R920/166INA85225-Pmt rec'd 850.60
23R920/174INE01251-Pmt rec'd 373.75
24R920/178INE01264-Pmt rec'd 1 552.50
25+R920164INC05244-Pmt rec'd 480.00
26REC-3682INA85203-Pmt rec'd 1 767.80
27INA85203Tax Invoice - TRANS KALAHARI MEAT1 767.80
28INE01255Tax Invoice - GREENER PASTURES#371881 995.80
29INE01257Tax Invoice - WILLEM-#372331 230.50
30INE01258Tax Invoice - MULLER-#PORK SPEK1 272.35
31INE01259Tax Invoice - JONAS-#3723587.40
32INE01260Tax Invoice - MATHEUS-#372452 363.80
33INE01261Tax Invoice - FRANS-#14116.35
34INE01264Tax Invoice - VALENCIA-OSHIWAMBO1 552.50
35INE01265Tax Invoice - JOHN VAN DEVENTER-#372563 190.35
36INA85216Tax Invoice - WAKKER BILTONG6 779.25
37INA85218Tax Invoice - CASSIE1 350.85
38INA85223Tax Invoice - MARIUS VAN RENSBURG5 516.20
39INA85225Tax Invoice - CHARLOTTE /JAQUES850.60
40INA85213Tax Invoice - CINDY GROENEWALDT1 916.80
41R920/182INA85242-Pmt rec'd 931.60
42R920/184INE01265-Pmt rec'd 3 190.35
43R920/190INA85216-Pmt rec'd 6 779.25
44R920/194INC05243-Pmt rec'd 1 516.95
45R920/194INC05241-Pmt rec'd 225.40
46REC-3820INE01269-Pmt rec'd 373.75
47REC-3821INE01252-Pmt rec'd 272.85
48REC-3684INE01258-Pmt rec'd 803.85
49REC-3685INE05242-Pmt rec'd 7 818.00
50REC-3687INA85213-Pmt rec'd 1 916.80
51REC-3688INA85241-Pmt rec'd 1 629.20
52REC-3689INA85245-Pmt rec'd 138.55
53REC-3690INA85218-Pmt rec'd 1 350.85
54+R920191INA85244-Pmt rec'd-BFT Butch 2 114.30
55+R920191INA85410-Pmt rec'd-BFT Butch 2 470.80
56+R920192Pmt rec'd-BFT Butch-balance-unk inv 4 401.55
57-R920180INA85252-Pmt rec'd ########
58-MOVEINA85244-Moved to BFT001 2 114.30
59-MOVEINA85410-Moved to BFT001 2 470.80
60+MOVEKierieshop (-R920180)-from 2950/000########
61-MOVEINA85244-to BFT0012 114.30
62-MOVER920192-balance amount4 401.55
63--MOVEINA85410-Moved to BFT0012 470.80
64-REC3684INE01258-Rev wrong inv nr803.85
65+REC3684INE01268-Pmt rec'd-was INE01258 803.85
66+R920205INE01258-4479-Pmt rec'd 1 272.35
67INC05241Tax Invoice - E BIERMANN/#37311225.40
68INC05242Tax Invoice - F/SHOP SALES-DN20-247 818.00
69INA85240Tax Invoice - BEATRICE546.25
70INA85241Tax Invoice - SWARTZ1 629.20
71INA85242Tax Invoice - EMMARENCHIA931.60
72INA85244Tax Invoice - BFT BUTCHERY2 114.30
73INA85245Tax Invoice - GRILL GURU138.55
74INA85252Tax Invoice - KIERIE SHOP########
75INE01267Tax Invoice - #37276-WYNAND3 523.15
76INE01268Tax Invoice - BABS-#37290803.85
77INE01269Tax Invoice - MEV FOURIE-#37307373.75
78INC05243Tax Invoice - FS SALES-DN'S SWIPES1 516.95
79ICC00788Credit Note - INE01267 1 224.00
80R920/195INA85273-Pmt rec'd 8 382.00
81R920/195Discount 0.90
82REC3694Payment INA85282 1 110.00
83REC3825Payment Willem 1 230.50
84REC3825Payment Sharon 5 844.30
85REC3825Payment Rothmann 2 541.30
86REC3825Payment Paco 373.75
87+R920196INA85223-Pmt rec'd 5 516.20
88+REC3691INC05245-Pmt rec'd 2 700.00
89INE01272Tax Invoice - WILLEM-#373331 230.50
90INE01273Tax Invoice - MNR ROTHMAN-373202 541.30
91INE01275Tax Invoice - PACO/AMILIA373.75
92INA85273Tax Invoice - MARTIN EHAFO8 382.90
93INA85274Tax Invoice - JCT BUTCHERY########
94INA85279Tax Invoice - WHK TOTAL NOORD8 609.10
95INA85282Tax Invoice - DRIE ANKER FARMING1 110.00
96INA85283Tax Invoice - WYNAND5 773.00
97INA85284Tax Invoice - SHARON5 844.30
98INA85285Tax Invoice - MONICA WERNER1 907.85
99INA85288Tax Invoice - R.T.HEISER3 351.75
100ICC00765Credit Note - INA85283 3 724.25
101R920/208INA85317-Pmt rec'd 8 218.55
102R920/213INE01283-Pmt rec'd 1 586.40
103R920/214INA85316-Pmt rec'd 1 121.25
104R920/216INE01285-Pmt rec'd 1 156.40
105R920/218INA -Pmt rec'd 4 003.85
106R920/225INA85285-Pmt rec'd 1 907.85
107R920/225INE01290-Pmt rec'd 357.00
108REC3700COD Willem 1 230.50
109REC3700Dn26 offal 2 070.00
110REC3700COD Josua 2 039.15
111REC3700COD Tusnelda 1 495.00
112REC3700COD Michael 1 660.30
113REC3700COD Yvonne 373.75
114+R920212INA85274-Pmt rec'd ########
115-R920218INE01233-Rev must be to C.O.D24 003.85
116INA85312Tax Invoice - T.W.KOORTS9 575.85
117INA85315Tax Invoice - G'S DISCOUNT LIQUER4 685.40
118INA85316Tax Invoice - MR G HOME SHOP1 121.25
119INA85317Tax Invoice - EILEEN RAKOW8 218.55
120INE01278Tax Invoice - IVONNE-#373.75
121INE01279Tax Invoice - WILLEM#373841 230.50
122INE01280Tax Invoice - TUSNELDA-#373781 495.00
123INE01281Tax Invoice - CLAUDIA-373.75
124INE01283Tax Invoice - #37387-ERONGO MEAT1 586.40
125INE01285Tax Invoice - BOSS ENTERPRISES1 156.40
126INE01286Tax Invoice - MICHEAL#373871 660.30
127INE01287Tax Invoice - JOSUA-#373922 039.15
128INE01288Tax Invoice - LOUIS KARSTEN-#373861 275.35
129INE01289Tax Invoice - RENE591.70
130INE01290Tax Invoice - F/SHOP D/N20357.00
131INE01291Tax Invoice - F/SHOP D/N 23200.00
132ICC00766Credit Note - INE01288 667.75
133R920/227INA85288-Pmt rec'd 3 351.75
134R920/227INM00495-Pmt rec'd 2 230.00
135R920/227INE01281-Pmt rec'd 373.75
136R920/227INE01289-Pmt rec'd 591.70
137INM00494Tax Invoice - F/SHOP AMILIA1 345.00
138INM00495Tax Invoice - SWIPES/F/SHOP AMILIA2 230.00
139INA85321Tax Invoice - EILENN RAKOW216.55
140R920/233INA85327-Pmt rec'd 3 815.95
141REC3696COD Beatrice 546.25
142REC3697COD GS Discount Liquor 4 685.40
143REC3698Cash factory shop 1 545.00
144REC3700COD Din 27-29 Gina ########
145REC3700COD L Karstens 667.75
146REC3700COD ouma Nora 373.75
147REC3707Payment Josua 1 824.85
148REC3707Payment Claudia 1 495.00
149REC3707Payment Willem 1 262.70
150+R920236INC05257-Pmt rec'd 260.00
151+VARINE01288-diff between ct note & rec60.15
152INA85325Tax Invoice - CHIILED DISTRIBUTION983.90
153INA85326Tax Invoice - SUSAN OUMA NORA373.75
154INA85327Tax Invoice - YORK3 815.95
155INC05248Tax Invoice - HEIKO/#374132 795.60
156INE01296Tax Invoice - ERONGO MEAT-374071 046.50
157INE01297Tax Invoice - JOSUA-#374091 824.85
158INE01299Tax Invoice - WYNAND-374143 624.35
159INE01300Tax Invoice - D/N 27-29 AMILIA########
160INE01301Tax Invoice - HENNIS#374371 050.85
161INE01302Tax Invoice - CLAUDIA-374461 495.00
162INE01303Tax Invoice - BAKEREI DEKKER-#37424431.25
163INE01304Tax Invoice - WILLEM1 262.70
164INC05257Tax Invoice - FS-DN 34 38 39 411 330.00
165ICC00775Credit Note - INE01299 3 624.35
166REC3706Payment Bakery Dekker 431.25
167R920/237INE01308-Pmt rec'd ########
168R920/245INE01307-Pmt rec'd 3 015.85
169REC4062INE01525 - JCT ########
170REC3967INE01626 - Katazu 833.95
171REC3968INE01627 - Willem 1 354.70
172REC3969INE01630 - Iomas 1 354.70
173REC3970INE01631 - Pundaleni 677.35
174REC3971INa85157 - Chanell 3.00
175REC3972INE01635 - Meletzky 890.55
176INC05480Tax Invoice - GRILL GURU3 293.60
177INC05483Tax Invoice - SJ VD MERWE/#38993464.30
178INA86167Tax Invoice - CHANTEL BARLOW2 108.50
179INA86170Tax Invoice - CHILLED DISTRIBUTION3 930.45
180INA86171Tax Invoice - LOUIS BOTHA3 865.75
181INA86178Tax Invoice - MARINE LIFE BOAT3 048.45
182INA86184Tax Invoice - OLAF HASE2 163.15
183INA86172Tax Invoice - SUSAN385.25
184INE01625Tax Invoice - JCT SUPERMARKET-#39########
185INE01626Tax Invoice - KATAZU ENTERPRISES-#39014833.95
186INE01627Tax Invoice - WILLEM-#390171 354.70
187INE01629Tax Invoice - MUTU-#39026817.40
188INE01630Tax Invoice - TOMAS-#390431 354.70
189INE01631Tax Invoice - PANDULENI-#39044677.35
190INE01632Tax Invoice - RUBEN'S CUST/SWIPES550.00
191ICC00968Credit Note - INA86184 2 163.15
192REC4064INC05490 - du Plessis 1 886.00
193REC4066INC05493 - Genesis 6 422.55
194REC4067INE01636 - Frans 501.20
195REC4069INE01529 - Mutu 817.40
196REC4072INC05501 - DN72-78 ########
197R920/698INA86195-Pmt rec'd 1 541.00
198R920/699INA86196-Pmt rec'd 1 722.10
199R920/700INA86134-Pmt rec'd 1 129.85
200R920/700INA86133-Pmt rec'd 629.60
201R920/704INA86167-Pmt rec'd 2 105.50
202R920/720INE01632-5315-Pmt rec'd 550.00
203R920/721INE01634-5325-Pmt rec'd 1 571.70
204R920/721INE05487-5331-Pmt rec'd 2 991.60
205R920/721INA86189-5333-Pmt rec'd 5 696.30
206R920/721INE01638-5349-Pmt rec'd 1 197.55
207R920/721INE01637-5351-Pmt rec'd 1 354.70
208R920/721INE01640-5363-Pmt rec'd 690.00
209R920/721INE01651-5371-Pmt rec'd 1 200.00
210INA86189Tax Invoice - JOSHUA5 696.30
211INA86192Tax Invoice - YORK2 384.00
212INA86195Tax Invoice - MR G/ HOMESHOP1 541.00
213INA86196Tax Invoice - A/ CLOETE1 722.10
214INE01634Tax Invoice - MEV VAN WYK-#390841 571.70
215INC05487Tax Invoice - MATTHEWS/#390702 991.60
216INC05490Tax Invoice - DU PLESSIS/#39077/MUTTON1 886.00
217INC05491Tax Invoice - BENDA/#39083/B-CHOPS1 354.70
218INC05493Tax Invoice - GENISIS BUTCH/#390896 422.55
219INV04835Tax Invoice2 163.15
220INE01635Tax Invoice - ANNA MALETZKY-#39085890.55
221INE01636Tax Invoice - FRANSE-#39088501.20
222INE01637Tax Invoice - ZENDA-#390901 354.70
223INE01638Tax Invoice - ZENDA-#390901 197.55
224INE01640Tax Invoice690.00
225INC05501Tax Invoice - DN72-78-GINA'S F/S OFFCUT########
226+R920712INA86091-Pmt rec'd 2 512.45
Sheet1
 
Last edited by a moderator:

Saba Sabaratnam

Board Regular
Joined
May 26, 2018
Messages
196
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

1612911716685.png

Kind regards

Saba
 
Solution

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Watch MrExcel Video

Forum statistics

Threads
1,126,939
Messages
5,621,717
Members
415,853
Latest member
Newlife72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top