split data based on month and move the final balance from month to another

A

Ali M

Active Member
Joined
Oct 10, 2021
Messages
348
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Hi experts
I have many data for customer . each the customer contain data from JAN to DEC so what I want split data into multiple sheets which contain data for 12 month as in picture 2 should split based on column A is month when move the next month as in PIC3 . it should copy the last value in lastrow in column G as highlighted from month JAN into month FEB in first row and add the date and client and word " opening" as is added in first row in month FEB so when finish the month and move the next should add the last balance from precede month to next month into first row . and every time change the data in sheet MAIN then should update in others sheets(JAN,FEB,......) with dosen't show the formula when split data .
orginal data
multi.xlsm
ABCDEFG
1DATEINVOICE NUMBERCLIENTCONDITIONDEBITCREDITBALANCE
21/1/2021AL0OPENING12000500.0011,500.00
31/1/2021SLFR-100AL1PR1001000021,500.00
41/2/2021SLFR-101AL1PR101500005,000.0066,500.00
51/3/2021SLFR-102AL1PR10262000128,500.00
61/4/2021SLFR-103AL1PR10381000209,500.00
71/5/2021SLFR-104AL1PR104100000309,500.00
81/6/2021SLFR-105AL1PR1051190003,000.00425,500.00
91/7/2021SLFR-106AL1PR106138000563,500.00
101/8/2021SLFR-107AL1PR1071570006,000.00714,500.00
111/9/2021SLFR-108AL1PR108176000890,500.00
121/10/2021SLFR-109AL1PR1091950001,085,500.00
131/11/2021SLFR-110AL1PR1102140007,000.001,292,500.00
141/12/2021SLFR-111AL1PR1112330001,525,500.00
151/13/2021SLFR-112AL1PR1122520001,777,500.00
161/14/2021SLFR-113AL1PR1132710004,000.002,044,500.00
171/15/2021SLFR-114AL1PR1142900002,334,500.00
181/16/2021SLFR-115AL1PR11530900050,000.002,593,500.00
191/17/2021SLFR-116AL1PR1163280002,921,500.00
201/18/2021SLFR-117AL1PR11734700070,000.003,198,500.00
211/19/2021SLFR-118AL1PR1183660003,564,500.00
221/20/2021SLFR-119AL1PR1193850003,000.003,946,500.00
231/21/2021SLFR-115AL1PR1164040004,350,500.00
241/22/2021SLFR-116AL1PR1174230004,773,500.00
251/23/2021SLFR-117AL1PR1184420005,215,500.00
261/24/2021SLFR-118AL1PR1194610005,676,500.00
271/25/2021SLFR-119AL1PR1204800006,156,500.00
281/26/2021SLFR-120AL1PR1214990006,655,500.00
291/27/2021SLFR-121AL1PR1225180007,173,500.00
301/28/2021SLFR-122AL1PR1235370007,710,500.00
311/29/2021SLFR-123AL1PR12455600020,000.008,246,500.00
321/30/2021SLFR-124AL1PR1255750008,000.008,813,500.00
331/31/2021SLFR-125AL1PR1265940009,407,500.00
342/1/2021SLFR-126AL1PR12761300010,020,500.00
352/2/2021SLFR-127AL1PR1286320007,000.0010,645,500.00
362/3/2021SLFR-128AL1PR12965100011,296,500.00
372/4/2021SLFR-129AL1PR13067000011,966,500.00
382/5/2021SLFR-130AL1PR13168900012,655,500.00
392/6/2021SLFR-131AL1PR1327080004,000.0013,359,500.00
402/7/2021SLFR-132AL1PR13372700014,086,500.00
412/8/2021SLFR-133AL1PR13474600014,832,500.00
422/9/2021SLFR-134AL1PR13576500015,597,500.00
432/10/2021SLFR-135AL1PR13678400016,381,500.00
442/11/2021SLFR-136AL1PR1378030004,600.0017,179,900.00
452/12/2021SLFR-137AL1PR13882200018,001,900.00
462/13/2021SLFR-138AL1PR13984100018,842,900.00
472/14/2021SLFR-139AL1PR14086000019,702,900.00
482/15/2021SLFR-140AL1PR14187900020,581,900.00
492/16/2021SLFR-141AL1PR14289800021,479,900.00
502/17/2021SLFR-142AL1PR14391700022,396,900.00
512/18/2021SLFR-143AL1PR14493600023,332,900.00
522/19/2021SLFR-144AL1PR14595500024,287,900.00
532/20/2021SLFR-145AL1PR14697400025,261,900.00
542/21/2021SLFR-146AL1PR14799300026,254,900.00
552/22/2021SLFR-147AL1PR148101200027,266,900.00
562/23/2021SLFR-148AL1PR149103100028,297,900.00
572/24/2021SLFR-149AL1PR150105000029,347,900.00
582/25/2021SLFR-150AL1PR151106900030,416,900.00
592/26/2021SLFR-151AL1PR152108800031,504,900.00
602/27/2021SLFR-152AL1PR153110700032,611,900.00
612/28/2021SLFR-153AL1PR154112600033,737,900.00
main
Cell Formulas
RangeFormula
G2G2=E2-F2
G3:G61G3=G2+E3-F3



result in two months based on orginal data
multi.xlsm
ABCDEFG
1DATEINVOICE NUMBERCLIENTCONDITIONDEBITCREDITBALANCE
21/1/2021AL1OPENING1200012,000.00
31/1/2021SLFR-100AL1PR1001000022,000.00
41/2/2021SLFR-101AL1PR101500005,000.0067,000.00
51/3/2021SLFR-102AL1PR10262000129,000.00
61/4/2021SLFR-103AL1PR10381000210,000.00
71/5/2021SLFR-104AL1PR104100000310,000.00
81/6/2021SLFR-105AL1PR1051190003,000.00426,000.00
91/7/2021SLFR-106AL1PR106138000564,000.00
101/8/2021SLFR-107AL1PR1071570006,000.00715,000.00
111/9/2021SLFR-108AL1PR108176000891,000.00
121/10/2021SLFR-109AL1PR1091950001,086,000.00
131/11/2021SLFR-110AL1PR1102140007,000.001,293,000.00
141/12/2021SLFR-111AL1PR1112330001,526,000.00
151/13/2021SLFR-112AL1PR1122520001,778,000.00
161/14/2021SLFR-113AL1PR1132710004,000.002,045,000.00
171/15/2021SLFR-114AL1PR1142900002,335,000.00
181/16/2021SLFR-115AL1PR11530900050,000.002,594,000.00
191/17/2021SLFR-116AL1PR1163280002,922,000.00
201/18/2021SLFR-117AL1PR11734700070,000.003,199,000.00
211/19/2021SLFR-118AL1PR1183660003,565,000.00
221/20/2021SLFR-119AL1PR1193850003,000.003,947,000.00
231/21/2021SLFR-115AL1PR1164040004,351,000.00
241/22/2021SLFR-116AL1PR1174230004,774,000.00
251/23/2021SLFR-117AL1PR1184420005,216,000.00
261/24/2021SLFR-118AL1PR1194610005,677,000.00
271/25/2021SLFR-119AL1PR1204800006,157,000.00
281/26/2021SLFR-120AL1PR1214990006,656,000.00
291/27/2021SLFR-121AL1PR1225180007,174,000.00
301/28/2021SLFR-122AL1PR1235370007,711,000.00
311/29/2021SLFR-123AL1PR12455600020,000.008,247,000.00
321/30/2021SLFR-124AL1PR1255750008,000.008,814,000.00
331/31/2021SLFR-125AL1PR1265940009,408,000.00
JAN
Cell Formulas
RangeFormula
G2G2=E2-F2
G3:G33G3=G2+E3-F3




multi.xlsm
ABCDEFG
1DATEINVOICE NUMBERCLIENTCONDITIONDEBITCREDITBALANCE
22/1/2021AL1OPENING9,408,000.009,408,000.00
32/1/2021SLFR-126AL1PR12761300010,021,000.00
42/2/2021SLFR-127AL1PR1286320007,000.0010,646,000.00
52/3/2021SLFR-128AL1PR12965100011,297,000.00
62/4/2021SLFR-129AL1PR13067000011,967,000.00
72/5/2021SLFR-130AL1PR13168900012,656,000.00
82/6/2021SLFR-131AL1PR1327080004,000.0013,360,000.00
92/7/2021SLFR-132AL1PR13372700014,087,000.00
102/8/2021SLFR-133AL1PR13474600014,833,000.00
112/9/2021SLFR-134AL1PR13576500015,598,000.00
122/10/2021SLFR-135AL1PR13678400016,382,000.00
132/11/2021SLFR-136AL1PR1378030004,600.0017,180,400.00
142/12/2021SLFR-137AL1PR13882200018,002,400.00
152/13/2021SLFR-138AL1PR13984100018,843,400.00
162/14/2021SLFR-139AL1PR14086000019,703,400.00
172/15/2021SLFR-140AL1PR14187900020,582,400.00
182/16/2021SLFR-141AL1PR14289800021,480,400.00
192/17/2021SLFR-142AL1PR14391700022,397,400.00
202/18/2021SLFR-143AL1PR14493600023,333,400.00
212/19/2021SLFR-144AL1PR14595500024,288,400.00
222/20/2021SLFR-145AL1PR14697400025,262,400.00
232/21/2021SLFR-146AL1PR14799300026,255,400.00
242/22/2021SLFR-147AL1PR148101200027,267,400.00
252/23/2021SLFR-148AL1PR149103100028,298,400.00
262/24/2021SLFR-149AL1PR150105000029,348,400.00
272/25/2021SLFR-150AL1PR151106900030,417,400.00
282/26/2021SLFR-151AL1PR152108800031,505,400.00
292/27/2021SLFR-152AL1PR153110700032,612,400.00
302/28/2021SLFR-153AL1PR154112600033,738,400.00
FEB
Cell Formulas
RangeFormula
G2G2=E2-F2
G3:G30G3=G2+E3-F3

note: if the last balance is minus then should show in column CREDIT when add in first row as OPENING
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

SanjayGMusafir
  • Solved
Text function not returning correct month
Replies
7
Views
256
Alex Blakenburg
A
A
  • Solved
Calculate monthly booked percentage of hotel rooms
2 3
Replies
21
Views
342
arendberg
A
B
  • Solved
timestamp listing to show more than >19hrs away per day?
Replies
6
Views
112
Kaper
Kaper
A
  • Question Question
How restrict copy data from form to sheet based on five days within current month
Replies
3
Views
112
Ali M
A
K
  • Question Question
Trying to make a formula to figure out what total hours in the month will be, based on what I already know
Replies
1
Views
50
Sufiyan97
Sufiyan97

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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