collection details invoices as accounts in debit, credit

abdo meghari

Well-known Member
Joined
Aug 3, 2021
Messages
711
Office Version
  1. 2019
Hello,
before LIST sheet contains many sheets and increasable as I need it.
in list sheet will put PURCHASING ,SELLING RETURNS in credit column(D), SELLING ,PURCHASING RETURNS put in DEBIT column(C) as to RECEIVED should reverse to CREDIT column ,PAID to DEBIT column .
as to words in column B will brings after colon for each MOVEMENT word. when brings any word will be consecutive when move to next sheet until reach new movement.
the amounts will brings from TOTAL column , in first row will write date (end of date for previous year based on current year) BALANCE and if the amount is minus in A1 cell then will put in CREDIT column and also BALANCE column , if positive value in A1 cell then will put in DEBIT column and also BALANCE column after that will calculate for each next row in column DEBIT,CREDIT as formula shows .


LIST.xlsm
ABCDEFG
1BALANCE:-2,50000.00NAME:BASHIR
2
3MOVEMENT : PURCHASING
4DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
52024.01.021141284GC 1200R20 AZ0026 CHI80895242560
61285GC 1200R20 AZ0183 CHI40925
71385GC 315/80R22.5 AT161 CHI20735
81287GC 315/80R22.5 AZ126 CHI20735
91294GC 315/80R22.5 AZ188 CHI20745
101241BS 1200R20 G580 JAP402035
111227BS 215/70R15C R623 THI8425
121221BS 205/70R15C R623 THI12405
13DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
142024.01.091211287GC 315/80R22.5 AZ126 CHI1473510290
15DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
162024.01.101251227BS 215/70R15C R623 THI2425850
17DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
182024.01.201481287GC 315/80R22.5 AZ126 CHI2673519110
19DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
202024.01.201491221BS 205/70R15C R623 THI50405391650
211227BS 215/70R15C R623 THI12425
221241BS 1200R20 G580 JAP1802035
23DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
242024.03.302171285GC 1200R20 AZ0183 CHI10122512250
25DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
262024.03.302261227BS 215/70R15C R623 THI45442176
27DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
282024.04.142301285GC 1200R20 AZ0183 CHI40120581950
291284GC 1200R20 AZ0026 CHI301125
30DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
312024.04.172361284GC 1200R20 AZ0026 CHI20112546600
321285GC 1200R20 AZ0183 CHI201205
PAGE 0




LIST.xlsm
ABCDEFG
1DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
22024.01.0215661284GC 1200R20 AZ0026 CHI1089525550
31285GC 1200R20 AZ0183 CHI10925
41385GC 315/80R22.5 AT161 CHI10735
5DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
62024.04.175671287GC 315/80R22.5 AZ126 CHI1735735
7DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
82024.04.185681227BS 215/70R15C R623 THI104254250
9DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
102024.01.0215691287GC 315/80R22.5 AZ126 CHI1735735
11MOVEMENT : SELLING
12DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
132024.01.244401306BS 750R16 R230 JAP107757750
14DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
152024.01.244411306BS 750R16 R230 JAP27801560
16DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
172024.01.244421306BS 750R16 R230 JAP17802285
181305BS 700R16 R230 JAP1770
191287GC 315/80R22.5 AZ126 CHI1735
20DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
212024.01.254431227BS 215/70R15C R623 THI2425850
22DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
232024.01.254441287GC 315/80R22.5 AZ126 CHI1735735
PAGE 1




LIST.xlsm
ABCDEFG
1DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
22024.01.264451306BS 750R16 R230 JAP27751550
3DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
42024.01.264461306BS 750R16 R230 JAP1780780
5DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
62024.01.264471306BS 750R16 R230 JAP17801550
71305BS 700R16 R230 JAP1770
8MOVEMENT : SELLING RETURNS
9DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
102024.01.243301306BS 750R16 R230 JAP27751550
11DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
122024.01.243311306BS 750R16 R230 JAP1780780
13DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
142024.01.243321305BS 700R16 R230 JAP17801560
151306BS 750R16 R230 JAP1780
PAGE 2




LIST.xlsm
ABCDEFG
1DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
22024.01.253331227BS 215/70R15C R623 THI14251160
31287GC 315/80R22.5 AZ126 CHI1735
4DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
52024.01.253341287GC 315/80R22.5 AZ126 CHI1735735
6MOVEMENT : PURCHASING RETURNS
7DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
82024.01.222201284GC 1200R20 AZ0026 CHI189518925
91285GC 1200R20 AZ0183 CHI2925
101385GC 315/80R22.5 AT161 CHI2735
111287GC 315/80R22.5 AZ126 CHI2735
12DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
132024.01.222211241BS 1200R20 G580 JAP220354920
141227BS 215/70R15C R623 THI2425
PAGE 3



LIST.xlsm
ABCDEFG
1DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
22024.01.222221284GC 1200R20 AZ0026 CHI18951820
31285GC 1200R20 AZ0183 CHI1925
4DATEINVOICE NOCODEBRANDQTYUNIT PRICETOTAL
52024.01.232231241BS 1200R20 G580 JAP220354920
PAGE 4


LIST.xlsm
ABCD
1DATEDESCIBEDEBITCREDIT
22024.01.10RECEIVED VBGHH2002,000.00
32024.01.10PAID HJU88882,000.00
42024.01.11RECEIVED VBGHH2011,500.00
52024.01.12PAID HJU8889100.00
62024.01.13PAID HJU8890200
72024.01.14PAID HJU8891225
82024.01.15RECEIVED VBGHH20230000
92024.01.16RECEIVED VBGHH20340000
10
11
12
13
14
15
ABS



RESULT SHOULD BE IN LIST sheet
LIST.xlsm
ABCDE
1DATEDESCRIBEDEBITCREDITBALANCE
231/12/2023BALANCE--250,000.00-250,000.00
32024.01.02PURCHASING-242,560.00-492,560.00
42024.01.09PURCHASING-10,290.00-502,850.00
52024.01.10PURCHASING-850.00-503,700.00
62024.01.10RECEIVED-2,000.00-505,700.00
72024.01.10PAID2,000.00--503,700.00
82024.01.11RECEIVED-1,500.00-505,200.00
92024.01.12PAID100.00--505,100.00
102024.01.13PAID200.00--504,900.00
112024.01.14PAID225.00--504,675.00
122024.04.14PURCHASING-81,950.00-586,625.00
132024.01.15RECEIVED-30,000.00-616,625.00
142024.01.16RECEIVED-40,000.00-656,625.00
152024.04.17PURCHASING-46,600.00-703,225.00
162024.04.17PURCHASING-735.00-703,960.00
172024.04.18PURCHASING-4,250.00-708,210.00
182024.01.20PURCHASING-19,110.00-727,320.00
192024.01.20PURCHASING-391,650.00-1,118,970.00
202024.01.22PURCHASING RETURNS-18,925.00-1,137,895.00
212024.01.22PURCHASING RETURNS-4,920.00-1,142,815.00
222024.01.22PURCHASING RETURNS-1,820.00-1,144,635.00
232024.01.23PURCHASING RETURNS-4,920.00-1,149,555.00
242024.01.24SELLING RETURNS-1,550.00-1,151,105.00
252024.01.24SELLING RETURNS-780.00-1,151,885.00
262024.01.24SELLING RETURNS-1,560.00-1,153,445.00
272024.01.24SELLING7,750.00--1,145,695.00
282024.01.24SELLING1,560.00--1,144,135.00
292024.01.24SELLING2,285.00--1,141,850.00
302024.01.25SELLING RETURNS-1,160.00-1,143,010.00
312024.01.25SELLING RETURNS-735.00-1,143,745.00
322024.01.25SELLING850.00--1,142,895.00
332024.01.25SELLING735.00--1,142,160.00
342024.01.26SELLING1,550.00--1,140,610.00
352024.01.26SELLING1,550.00--1,139,060.00
362024.03.30PURCHASING-12,250.00-1,151,310.00
372024.03.30PURCHASING-2,176.00-1,153,486.00
LIST
Cell Formulas
RangeFormula
E3:E37E3=E2+C3-D3
 

Forum statistics

Threads
1,226,771
Messages
6,192,918
Members
453,766
Latest member
Gskier

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
Back
Top