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:

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

New Member
Joined
Feb 7, 2021
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
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

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

1612749709401.png


Kind regards

Saba
 

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

Hi,

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

VBA Code:
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

New Member
Joined
Feb 7, 2021
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
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.....;):oops::unsure:...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
ABCDEFGH
11INA54060-J Enslin-moved to 3150/000 4 129.97  
22INC02658Tax Invoice - DN51-5620 230.00   
33ICC00416Credit Note - -INC02658 20 230.00  
44+RB92095INA85202-Pmt rec'd-BFT Butchery 3 229.15  Should also show paid
55-MOVEINA85202-to BFT0013 229.15   
66-MOVEINA85202-to BFT00155.95   
77INC05246Tax Invoice - FS-DN 25 - OFFALLS1 475.00   
88R920/153INA85185-Pmt rec'd 2 813.05  
99REC-3813INE01253-Pmt rec'd 717.60INE01253PAID
1010REC-3814INE01254-Pmt rec'd 402.90INE01254PAID
1111REC-3815INE01249-Pmt rec'd 2 203.65INE01249PAID
1212-MOVEBFT001-INA85202-move to bal inv 55.95  
1313-MOVEINA85202-Moved to BFT001 3 285.10  
1414INE01249Tax Invoice - CARLO-#371912 203.65  PAID
1515INE01251Tax Invoice - HELENI#37196373.75   
1616INE01252Tax Invoice - DONALEE272.85  PAID
1717INE01253Tax Invoice - SIEGFRIED-#PORK SPEK717.60  PAID
1818INE01254Tax Invoice - MICHEAL-#37217402.90  PAID
1919INA85184Tax Invoice - MR G.HOMESHOP1 495.00   
2020INA85185Tax Invoice - YORK2 813.05   
2121INA85202Tax Invoice - BFT BUTCHERY3 285.10   
2222INC05244Tax Invoice - FS-DN4--EFT'D480.00   
2323INC05245Tax Invoice - FS-DN FOR CASH2 700.00   
2424INC05247Tax Invoice - FS-DN 26-OFFALLS595.00   
2525R920/161INE01255-Pmt rec'd 1 995.80  
2626REC-3816INE01257-Pmt rec'd 1 230.50INE01257PAID
2727REC-3817INE01259-Pmt rec'd 87.40INE01259PAID
2828REC-3818INE01260-Pmt rec'd 2 363.80INE01260PAID
2929REC-3819INE01261-Pmt rec'd 116.35INE01261PAID
3030R920/166INA85225-Pmt rec'd 850.60  Match
3131R920/174INE01251-Pmt rec'd 373.75  
3232R920/178INE01264-Pmt rec'd 1 552.50  
3333+R920164INC05244-Pmt rec'd 480.00  
3434REC-3682INA85203-Pmt rec'd 1 767.80INA85203PAID
3535INA85203Tax Invoice - TRANS KALAHARI MEAT1 767.80  PAID
3636INE01255Tax Invoice - GREENER PASTURES#371881 995.80   
3737INE01257Tax Invoice - WILLEM-#372331 230.50  PAID
3838INE01258Tax Invoice - MULLER-#PORK SPEK1 272.35   
3939INE01259Tax Invoice - JONAS-#3723587.40  PAID
4040INE01260Tax Invoice - MATHEUS-#372452 363.80  PAID
4141INE01261Tax Invoice - FRANS-#14116.35  PAID
4242INE01264Tax Invoice - VALENCIA-OSHIWAMBO1 552.50   
4343INE01265Tax Invoice - JOHN VAN DEVENTER-#372563 190.35   
4444INA85216Tax Invoice - WAKKER BILTONG6 779.25   
4545INA85218Tax Invoice - CASSIE1 350.85  PAID
4646INA85223Tax Invoice - MARIUS VAN RENSBURG5 516.20   
4747INA85225Tax Invoice - CHARLOTTE /JAQUES850.60   match
4848INA85213Tax Invoice - CINDY GROENEWALDT1 916.80  PAID
4949R920/182INA85242-Pmt rec'd 931.60  
5050R920/184INE01265-Pmt rec'd 3 190.35  
5151R920/190INA85216-Pmt rec'd 6 779.25  
5252R920/194INC05243-Pmt rec'd 1 516.95  
5353R920/194INC05241-Pmt rec'd 225.40  
5454REC-3820INE01269-Pmt rec'd 373.75INE01269PAID
5555REC-3821INE01252-Pmt rec'd 272.85INE01252PAID
5656REC-3684INE01258-Pmt rec'd 803.85INE01258 
5757REC-3685INE05242-Pmt rec'd 7 818.00INE05242 
5858REC-3687INA85213-Pmt rec'd 1 916.80INA85213PAID
5959REC-3688INA85241-Pmt rec'd 1 629.20INA85241PAID
6060REC-3689INA85245-Pmt rec'd 138.55INA85245PAID
6161REC-3690INA85218-Pmt rec'd 1 350.85INA85218PAID
6262+R920191INA85244-Pmt rec'd-BFT Butch 2 114.30  
6363+R920191INA85410-Pmt rec'd-BFT Butch 2 470.80  
6464+R920192Pmt rec'd-BFT Butch-balance-unk inv 4 401.55  
6565-R920180INA85252-Pmt rec'd 29 412.25  
6666-MOVEINA85244-Moved to BFT001 2 114.30  
6767-MOVEINA85410-Moved to BFT001 2 470.80  
6868+MOVEKierieshop (-R920180)-from 2950/00029 412.25   
6969-MOVEINA85244-to BFT0012 114.30   
7070-MOVER920192-balance amount4 401.55   
7171--MOVEINA85410-Moved to BFT0012 470.80   
7272-REC3684INE01258-Rev wrong inv nr803.85   
7373+REC3684INE01268-Pmt rec'd-was INE01258 803.85  
7474+R920205INE01258-4479-Pmt rec'd 1 272.35  
7575INC05241Tax Invoice - E BIERMANN/#37311225.40   
7676INC05242Tax Invoice - F/SHOP SALES-DN20-247 818.00   
7777INA85240Tax Invoice - BEATRICE546.25   
7878INA85241Tax Invoice - SWARTZ1 629.20  PAID
7979INA85242Tax Invoice - EMMARENCHIA931.60   
8080INA85244Tax Invoice - BFT BUTCHERY2 114.30   
8181INA85245Tax Invoice - GRILL GURU138.55  PAID
8282INA85252Tax Invoice - KIERIE SHOP29 412.25   
8383INE01267Tax Invoice - #37276-WYNAND3 523.15   
8484INE01268Tax Invoice - BABS-#37290803.85   
8585INE01269Tax Invoice - MEV FOURIE-#37307373.75  PAID
8686INC05243Tax Invoice - FS SALES-DN'S SWIPES1 516.95   
8787ICC00788Credit Note - INE01267 1 224.00  
8888R920/195INA85273-Pmt rec'd 8 382.00  
8989R920/195Discount 0.90  
9090REC3694Payment INA85282 1 110.00#VALUE! 
9191REC3825Payment Willem 1 230.50#VALUE! 
9292REC3825Payment Sharon 5 844.30#VALUE! 
9393REC3825Payment Rothmann 2 541.30#VALUE! 
9494REC3825Payment Paco 373.75#VALUE! 
9595+R920196INA85223-Pmt rec'd 5 516.20  
9696+REC3691INC05245-Pmt rec'd 2 700.00  
9797INE01272Tax Invoice - WILLEM-#373331 230.50   
9898INE01273Tax Invoice - MNR ROTHMAN-373202 541.30   
9999INE01275Tax Invoice - PACO/AMILIA373.75   
100100INA85273Tax Invoice - MARTIN EHAFO8 382.90   
101101INA85274Tax Invoice - JCT BUTCHERY18 638.65   
102102INA85279Tax Invoice - WHK TOTAL NOORD8 609.10   
103103INA85282Tax Invoice - DRIE ANKER FARMING1 110.00   Why does it show VALUE?
104104INA85283Tax Invoice - WYNAND5 773.00   
105105INA85284Tax Invoice - SHARON5 844.30   
106106INA85285Tax Invoice - MONICA WERNER1 907.85   
107107INA85288Tax Invoice - R.T.HEISER3 351.75   
108108ICC00765Credit Note - INA85283 3 724.25  
109109R920/208INA85317-Pmt rec'd 8 218.55  
110110R920/213INE01283-Pmt rec'd 1 586.40  
111111R920/214INA85316-Pmt rec'd 1 121.25  
112112R920/216INE01285-Pmt rec'd 1 156.40  
113113R920/218INA -Pmt rec'd 4 003.85  
114114R920/225INA85285-Pmt rec'd 1 907.85  
115115R920/225INE01290-Pmt rec'd 357.00  
116116REC3700COD Willem 1 230.50#VALUE! 
117117REC3700Dn26 offal 2 070.00#VALUE! 
118118REC3700COD Josua 2 039.15#VALUE! 
119119REC3700COD Tusnelda 1 495.00#VALUE! 
120120REC3700COD Michael 1 660.30#VALUE! 
121121REC3700COD Yvonne 373.75#VALUE! 
122122+R920212INA85274-Pmt rec'd 18 638.65  
123123-R920218INE01233-Rev must be to C.O.D24 003.85   
124124INA85312Tax Invoice - T.W.KOORTS9 575.85   
125125INA85315Tax Invoice - G'S DISCOUNT LIQUER4 685.40   
126126INA85316Tax Invoice - MR G HOME SHOP1 121.25   
127127INA85317Tax Invoice - EILEEN RAKOW8 218.55   
128128INE01278Tax Invoice - IVONNE-#373.75   
129129INE01279Tax Invoice - WILLEM#373841 230.50   
130130INE01280Tax Invoice - TUSNELDA-#373781 495.00   
131131INE01281Tax Invoice - CLAUDIA-373.75   
132132INE01283Tax Invoice - #37387-ERONGO MEAT1 586.40   
133133INE01285Tax Invoice - BOSS ENTERPRISES1 156.40   
134134INE01286Tax Invoice - MICHEAL#373871 660.30   
135135INE01287Tax Invoice - JOSUA-#373922 039.15   
136136INE01288Tax Invoice - LOUIS KARSTEN-#373861 275.35   
137137INE01289Tax Invoice - RENE591.70   
138138INE01290Tax Invoice - F/SHOP D/N20357.00   
139139INE01291Tax Invoice - F/SHOP D/N 23200.00   
140140ICC00766Credit Note - INE01288 667.75  
141141R920/227INA85288-Pmt rec'd 3 351.75  
142142R920/227INM00495-Pmt rec'd 2 230.00  
143143R920/227INE01281-Pmt rec'd 373.75  
144144R920/227INE01289-Pmt rec'd 591.70  
145145INM00494Tax Invoice - F/SHOP AMILIA1 345.00   
146146INM00495Tax Invoice - SWIPES/F/SHOP AMILIA2 230.00   
147147INA85321Tax Invoice - EILENN RAKOW216.55   
148148R920/233INA85327-Pmt rec'd 3 815.95  
149149REC3696COD Beatrice 546.25#VALUE! 
150150REC3697COD GS Discount Liquor4 685.40#VALUE! 
Sheet1
Cell Formulas
RangeFormula
F1:F12F1=IF(LEFT(B1,3)="rec",MID(C1,1,FIND("-",C1,1)-1),"")
G1:G150G1=IF(IF(LEFT(B1,3)="REC",COUNTIFS(B:B,F1,D:D,E1),COUNTIFS(F:F,B1,E:E,D1))=1,"PAID","")
F13:F150F13=IF(LEFT(B13,3)="REC",MID(C13,1,FIND("-",C13,1)-1),"")
 

Saba Sabaratnam

Board Regular
Joined
May 26, 2018
Messages
196

ADVERTISEMENT

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.

1612823517480.png

Kind regards

Saba
 

Koekemoer

New Member
Joined
Feb 7, 2021
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFGHIJK
254025392539INE02249Tax Invoice - MUTU-#420425123.05No invoice Number Paid
254125402540INE02250Tax Invoice - MINETTE-#420433012.50INETTE-#  Why no "paid"
254225412541INE02252Tax Invoice - WHK GYNASUIM-#420443107.85No invoice Number Paid
254325422542INE02255Tax Invoice - # 420791377.70No invoice Number Paid
254425432543INM00591Tax Invoice - D/N 00614440.00No invoice Number Paid
254525652565+DEP279INV04910-Pmt rec'd28022.70No invoice Number28022.70 
254625662566+DEP279INA87077-Pmt rec'd71390.60No invoice Number71390.60 
254725672567REC 4373INA87694 - G'S disc3778.10INA876943778.10 
254825732573INM00600Tax Invoice - #42137 LLOYS1953.55No invoice Number Paid
254925782578INE02276Tax Invoice - # 42162 RITUNGEE/AMILIA505.45RITUNGEE Paid
255025792579INE02277Tax Invoice - STEVEN-#421421758.10No invoice Number Paid
255125802580INE02280Tax Invoice - BOETA-#42153130.75No invoice Number Paid
255225812581REC4376INE02268 - Mutu5634.50INE022685634.50 
255325822582W2362INE02276 - Ritungee505.45itungee505.45 
255425832583W2363INA87714 - Spesbona5274.30INA877145274.30 
255525842584W2364INE02277 - Steven1758.10INE022771758.10 
255625852585W2365INE02280 - Boeta130.75INE02280130.75 
255725862586W2366INE02286 - Michael917.70INE02286917.70 
255825872587INM00611Deleted Tax Invoice - #42241/AMILIA0.00No invoice Number Paid
255925882588DEP327INC06092-Pmt rec'd2567.40No invoice Number2567.40 
256025892589DEP329INE02287-Pmt rec'd2273.55No invoice Number2273.55 
256125902590DEP330INE02291-Pmt rec'd1974.55No invoice Number1974.55Paid
256225912591DEP331INC06094-Pmt rec'd2491.20No invoice Number2491.20 
256325922592DEP332INC06095-Pmt rec'd2259.68No invoice Number2259.68 
256425932593DEP334INC06096-Pmt rec'd1289.20No invoice Number1289.20 
256525942594DEP337INM00612-Pmt rec'd300.00No invoice Number300.00Paid
256625952595DEP346INE02269-7893-Pmt rec'd6615.95No invoice Number6615.95 
256725962596DEP346INM00600-7897-Pmt rec'd1953.55No invoice Number1953.55 
256825972597DEP346INE02275-7911-Pmt rec'd5522.55No invoice Number5522.55 
256925982598+DEP319INE02281-Pmt rec'd1633.75No invoice Number1633.75 
257025992599W2367INA87745 - Spesbona7560.95INA877457560.95 
257126002600W2369INE02292 - Mathew2173.75INE022922173.75 
257226012601W2373INM00615 - Bertha47.25tha47.25 
257326022602W2374INE02297 - Tusnelda1605.40INE022971605.40 
257426032603+DEP339INA87456-Pmt rec'd5158.00No invoice Number5158.00 
257526042604INM00608Tax Invoice - #42211-SALAMON/AMILIA1469.70No invoice Number Paid
257626052605INA87745Tax Invoice - SPESBONA MARK7560.95RK Paid
257726062606INE02281Tax Invoice - CAPITAL GROW-421541633.75ROW-4215  
257826072607INE02282Tax Invoice - MEV SWARTS-#421871362.75RTS-#421  
257926082608INE02283Tax Invoice - MOUNT KARAS-#421883168.80RAS-#421  
258026092609INE02286Tax Invoice - MICHEAL-#42214917.70No invoice Number Paid
258126102610INE02287Tax Invoice - SFM -#422102273.55No invoice Number Paid
258226112611INE02288Tax Invoice - THE ROCK LODGE-#422382137.25ROCK LOD Paid
258326122612INE02290Tax Invoice - MASHONGO-#422481357.75No invoice Number Paid
258426132613INE02291Tax Invoice - MNR VERMAAK#422411974.55R VERMAA  
258526142614INE02292Tax Invoice - #42250 MATHEW2173.75No invoice Number Paid
258626152615INC06094Tax Invoice - CMDS MEAT/#422292491.20No invoice Number Paid
258726162616INC06095Tax Invoice - JENSEN-ORDER 1 + 22598.65RDER 1 +  
258826172617INC06096Tax Invoice - KRAEHEMANN/#422491289.20RAEHEMAN  
258926182618INM00612Tax Invoice - #42211300.00No invoice Number Paid
259026192619INM00609Tax Invoice - #42212401.35No invoice Number Paid
259126242624INM00613Tax Invoice - WEST COAST4284.10No invoice Number Paid
259226252625DEP349INC06095-Pmt rec'd338.97No invoice Number338.97 
259326262626W2370INE02295 - Ritungee521.40itungee521.40 
259426292629REC4379INE02283 - Mount Karas3168.80as3168.80 
259526332633DEP357INM00613-Pmt rec'd-West Coast5041.95No invoice Number5041.95 
259626342634DEP362INM00608-7949-Pmt rec'd1469.70No invoice Number1469.70Paid
259726352635DEP362INE02288-7953-Pmt rec'd2137.25No invoice Number2137.25 
259826362636DEP362INE02290-8067-Pmt rec'd1357.75No invoice Number1357.75 
259926412641INA87758Tax Invoice - HENK KEMPEN1457.55No invoice Number Paid
260026422642INA87759Tax Invoice - OMATEKO LODGE2843.20No invoice Number Paid
260126432643INA87770Tax Invoice - MR G HOMESHOP2006.75R G HOME Paid
260226442644INA87783Tax Invoice - PERCY387.05RCY  
260326452645INE02293Tax Invoice - CHILLED DIST-#421925867.00No invoice Number Paid
260426462646INE02294Tax Invoice - MEV FOURIE-#42270401.35RIE-#422  
260526472647INE02295Tax Invoice - RITUNGEE-#42271521.40RITUNGEE Paid
260626482648INA87793Tax Invoice - MARTIN ROSH PINAH4313.05  Paid
260726492649DEP366INC06103-Pmt rec'd14667.45No invoice Number14667.45 
260826502650DEP367INA87793-Pmt rec'd4313.05No invoice Number4313.05 
260926512651DEP376INE02296-8190-Pmt rec'd474.05No invoice Number474.05 
261026522652REC4390INE02301 - Chilled4340.10INE023014340.10 
261126532653REC4394D/N 0084670.00No invoice Number4670.00 
261226542654INB01778Tax Invoice - BERTUS MYBURGH985.30RTUS MYB  
261326672667DEP384INM00609-Pmt rec'd851.35No invoice Number851.35 
261426732673INE02309Tax Invoice - TOTAL WHK-#PORK SPEK2242.50RK SPEK PaidThere isn't a pmt for this inv
261526742674INE02310Tax Invoice - MATHEUS3345.05No invoice Number Paid
261626752675INE02311Tax Invoice - HEINRICH VAN ZYL261.85RICH VAN  
261726762676INE02312Tax Invoice - DESERE-#423832904.65RE-#4238  
261826772677INE02315Tax Invoice - FRIEDA-#42385287.45RIEDA-#4  
261926782678INE02317Tax Invoice - JOSUA-#423968954.70No invoice Number PaidThere isn't a pmt for this inv
262026792679INM00629Tax Invoice - D/N 0084670.00No invoice Number PaidThere isn't a pmt for this inv
Sheet1
Cell Formulas
RangeFormula
H2540:H2620H2540=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:I2620I2540=IF(F2540<>0,F2540,G2540)
J2540:J2620J2540=IF(COUNTIFS(H:H,H2540,I:I,I2540)>=2,"Paid","")
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Saba Sabaratnam

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

1612910910331.png


Happy to help

Kind regards

Saba
 

Watch MrExcel Video

Forum statistics

Threads
1,127,334
Messages
5,624,082
Members
416,010
Latest member
NJT

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