split range into multiple range based on blank row for each range

[Ali M]

Hi
I have many data contains blank row , should split data for each range based on end blank row
each range start data from first row and finish at blank row .
when split data should merge duplicates items and sum amounts for debit and credit and calculation by column BALANCE and I don't need any formula in column L
the result should be H:L.
every time I will add new data in columns A:D , should delete data H:L when run the macro every time .
I Have about 3500 rows .

AGGREGATE1.xlsm
	A	B	C	D
1	DATE	ACCOUNT NAME	DEBIT	CREDIT
2	01/03/2023	CASH BALANCE	200,000.00
3	03/03/2023	CASH PR		2,000.00
4	03/03/2023	CASH PR		15,000.00
5	09/03/2023	CASH PR		500.00
6	13/03/2023	CASH PR		10,000.00
7	13/03/2023	CASH PR	15,000.00
8	14/03/2023	CASH PR		500.00
9	19/03/2023	CASH PR		500.00
10	19/03/2023	CASH PR		2,000.00
11	05/03/2023	CASH DM	2,000.00
12	10/03/2023	CASH DM	500.00
13	15/03/2023	CASH DM	5,000.00
14	15/03/2023	CASH DM	2,000.00
15	15/03/2023	CASH DM		10,000.00
16	18/03/2023	CASH DM		25,000.00
17	19/03/2023	CASH DM		2,000.00
18	07/04/2023	EXPENSE ADMIN		55000
19	08/04/2023	EXPENSE ADMIN		10000
20
21	25/03/2023	PURCHASE	10000
22	29/03/2023	PURCHASE	5000
23	09/04/2023	STOCK1	100000
24	30/03/2023	PURCHASE RETURNS		2000
25	31/03/2023	PURCHASE RETURNS		2000
26	03/04/2023	PURCHASE LOW	4000
27	04/04/2023	PURCHASE LOW	2200
28	21/03/2023	EXPENSE PR	15,000.00
29	21/03/2023	EXPENSE PR	20,000.00
30	23/03/2023	EXPENSE PR	2,000.00
31
32	24/03/2023	SALES		200000
33	26/03/2023	SALES		1000
34	27/03/2023	SALES RETURNS	2500
35	28/03/2023	SALES RETURNS	1000
36	01/04/2023	SELLING LOW		2000
37	02/04/2023	SELLING LOW		1200
38
39	05/04/2023	STOCK	70000
40	06/04/2023	STOCK	130000
41
DAILY

result

AGGREGATE1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	DATE	ACCOUNT NAME	DEBIT	CREDIT				ITEM	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
2	01/03/2023	CASH BALANCE	200,000.00					1	CASH BALANCE	200,000.00		200,000.00
3	03/03/2023	CASH PR		2,000.00				2	CASH PR	15,000.00	30,500.00	184,500.00
4	03/03/2023	CASH PR		15,000.00				3	CASH DM	9,500.00	37,000.00	157,000.00
5	09/03/2023	CASH PR		500.00				4	EXPENSE ADMIN		65,000.00	92,000.00
6	13/03/2023	CASH PR		10,000.00					TOTAL	224,500.00	132,500.00	92,000.00
7	13/03/2023	CASH PR	15,000.00
8	14/03/2023	CASH PR		500.00				ITEM	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
9	19/03/2023	CASH PR		500.00				1	PURCHASE	15,000.00		15,000.00
10	19/03/2023	CASH PR		2,000.00				2	STOCK1	100,000.00		115,000.00
11	05/03/2023	CASH DM	2,000.00					3	PURCHASE RETURNS		4,000.00	111,000.00
12	10/03/2023	CASH DM	500.00					4	PURCHASE LOW	6,200.00		117,200.00
13	15/03/2023	CASH DM	5,000.00					5	EXPENSE PR	37,000.00		154,200.00
14	15/03/2023	CASH DM	2,000.00						TOTAL	158,200.00	4,000.00	154,200.00
15	15/03/2023	CASH DM		10,000.00
16	18/03/2023	CASH DM		25,000.00				ITEM	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
17	19/03/2023	CASH DM		2,000.00				1	SALES		201,000.00	-201,000.00
18	07/04/2023	EXPENSE ADMIN		55000				2	SALES RETURNS	3,500.00		-197,500.00
19	08/04/2023	EXPENSE ADMIN		10000				3	SELLING LOW		3,200.00	-200,700.00
20									TOTAL	3,500.00	204,200.00	-200,700.00
21	25/03/2023	PURCHASE	10000
22	29/03/2023	PURCHASE	5000					ITEM	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
23	09/04/2023	STOCK1	100000					1	STOCK	200,000.00		200,000.00
24	30/03/2023	PURCHASE RETURNS		2000					TOTAL	200,000.00	0.00	200,000.00
25	31/03/2023	PURCHASE RETURNS		2000
26	03/04/2023	PURCHASE LOW	4000
27	04/04/2023	PURCHASE LOW	2200
28	21/03/2023	EXPENSE PR	15,000.00
29	21/03/2023	EXPENSE PR	20,000.00
30	23/03/2023	EXPENSE PR	2,000.00
31
32	24/03/2023	SALES		200000
33	26/03/2023	SALES		1000
34	27/03/2023	SALES RETURNS	2500
35	28/03/2023	SALES RETURNS	1000
36	01/04/2023	SELLING LOW		2000
37	02/04/2023	SELLING LOW		1200
38
39	05/04/2023	STOCK	70000
40	06/04/2023	STOCK	130000
41
DAILY

Cell Formulas
Range	Formula
L2,L23:L24,L20,L17,L14,L9,L6	L2	=J2-K2
L18:L19,L10:L13,L3:L5	L3	=L2+J3-K3
J6:K6	J6	=SUM(J2:J5)
J14:K14	J14	=SUM(J9:J13)
J20:K20	J20	=SUM(J17:J19)
J24:K24	J24	=SUM(J23:J23)

AGGREGATE1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	DATE	ACCOUNT NAME	DEBIT	CREDIT				ITEM	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
2	01/03/2023	CASH BALANCE	200,000.00					1	CASH BALANCE	200,000.00		200,000.00
3	03/03/2023	CASH PR		2,000.00				2	CASH PR	15,000.00	30,500.00	184,500.00
4	03/03/2023	CASH PR		15,000.00				3	CASH DM	9,500.00	37,000.00	157,000.00
5	09/03/2023	CASH PR		500.00				4	EXPENSE ADMIN		65,000.00	92,000.00
6	13/03/2023	CASH PR		10,000.00					TOTAL	224,500.00	132,500.00	92,000.00
7	13/03/2023	CASH PR	15,000.00
8	14/03/2023	CASH PR		500.00				ITEM	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
9	19/03/2023	CASH PR		500.00				1	PURCHASE	15,000.00		15,000.00
10	19/03/2023	CASH PR		2,000.00				2	STOCK1	100,000.00		115,000.00
11	05/03/2023	CASH DM	2,000.00					3	PURCHASE RETURNS		4,000.00	111,000.00
12	10/03/2023	CASH DM	500.00					4	PURCHASE LOW	6,200.00		117,200.00
13	15/03/2023	CASH DM	5,000.00					5	EXPENSE PR	37,000.00		154,200.00
14	15/03/2023	CASH DM	2,000.00						TOTAL	158,200.00	4,000.00	154,200.00
15	15/03/2023	CASH DM		10,000.00
16	18/03/2023	CASH DM		25,000.00				ITEM	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
17	19/03/2023	CASH DM		2,000.00				1	SALES		201,000.00	-201,000.00
18	07/04/2023	EXPENSE ADMIN		55000				2	SALES RETURNS	3,500.00		-197,500.00
19	08/04/2023	EXPENSE ADMIN		10000				3	SELLING LOW		3,200.00	-200,700.00
20									TOTAL	3,500.00	204,200.00	-200,700.00
21	25/03/2023	PURCHASE	10000
22	29/03/2023	PURCHASE	5000					ITEM	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
23	09/04/2023	STOCK1	100000					1	STOCK	200,000.00		200,000.00
24	30/03/2023	PURCHASE RETURNS		2000					TOTAL	200,000.00	0.00	200,000.00
25	31/03/2023	PURCHASE RETURNS		2000
26	03/04/2023	PURCHASE LOW	4000
27	04/04/2023	PURCHASE LOW	2200
28	21/03/2023	EXPENSE PR	15,000.00
29	21/03/2023	EXPENSE PR	20,000.00
30	23/03/2023	EXPENSE PR	2,000.00
31
32	24/03/2023	SALES		200000
33	26/03/2023	SALES		1000
34	27/03/2023	SALES RETURNS	2500
35	28/03/2023	SALES RETURNS	1000
36	01/04/2023	SELLING LOW		2000
37	02/04/2023	SELLING LOW		1200
38
39	05/04/2023	STOCK	70000
40	06/04/2023	STOCK	130000
41
DAILY

Cell Formulas
Range	Formula
L2,L23:L24,L20,L17,L14,L9,L6	L2	=J2-K2
L18:L19,L10:L13,L3:L5	L3	=L2+J3-K3
J6:K6	J6	=SUM(J2:J5)
J14:K14	J14	=SUM(J9:J13)
J20:K20	J20	=SUM(J17:J19)
J24:K24	J24	=SUM(J23:J23)

M(J17:J19)[/XD][/XR][XR][XD]J24:K24[/XD][XD=fw:b]J24[/XD][XD]=SUM(J23:J23)[/XD][/XR][/RANGE]

 

[DanteAmor]

Try the following macro:

Sub SplitRange()
  Dim sh As Worksheet
  Dim dic As Object
  Dim a As Variant, b As Variant
  Dim i As Long, nRow As Long, y As Long
  Dim dbt As Double, cdt As Double
  Dim ar As Range
 
  Application.ScreenUpdating = False
 
  Set sh = Sheets("DAILY")
  Set dic = CreateObject("Scripting.Dictionary")
 
  a = sh.Range("B2:D" & sh.Range("B" & Rows.Count).End(3).Row + 1).Value
  ReDim b(1 To UBound(a, 1) * 3, 1 To 5)
  sh.Range("H:L").Clear
 
'merge duplicates items and sum amounts for debit and credit
  y = 1
  For i = 1 To UBound(a)
    If a(i, 1) = "" Then
      dic.RemoveAll
      y = y + 3
    Else
      If Not dic.exists(a(i, 1)) Then
        y = y + 1
        nRow = y
        dic(a(i, 1)) = nRow
      Else
        nRow = dic(a(i, 1))
      End If
      b(nRow, 1) = dic.Count
      b(nRow, 2) = a(i, 1)
      b(nRow, 3) = b(nRow, 3) + a(i, 2)
      b(nRow, 4) = b(nRow, 4) + a(i, 3)
    End If
  Next
 
'Balance and Total
  For i = 2 To y
    If b(i, 1) = "" And b(i - 1, 1) <> "" Then
      b(i, 2) = "TOTAL"
      b(i, 3) = dbt
      b(i, 4) = cdt
      b(i, 5) = dbt - cdt
      dbt = 0
      cdt = 0
    Else
      If b(i, 1) <> "" Then
        b(i, 5) = Val(b(i - 1, 5)) + b(i, 3) - b(i, 4)
      End If
      dbt = dbt + b(i, 3)
      cdt = cdt + b(i, 4)
    End If
  Next
  Range("H1").Resize(UBound(b, 1), UBound(b, 2)).Value = b
 
'Format Cells
  For Each ar In sh.Range("H2:H" & sh.Range("H" & Rows.Count).End(3).Row). _
    SpecialCells(xlCellTypeConstants).Areas
    With ar.Offset(-1).Resize(ar.Rows.Count + 1, 5)
      .Borders.LineStyle = 1
      .Borders.ColorIndex = 16
    End With
    With ar.Offset(-1).Resize(1, 5)
      .Value = Array("ITEM", "ACCOUNT NAME", "DEBIT", "CREDIT", "BALANCE")
      .Font.Color = sh.Range("D1").Font.Color
      .Font.Bold = True
      .Interior.Color = sh.Range("D1").Interior.Color
    End With
    With ar.Cells(1).Offset(ar.Rows.Count, 1)
      .Font.Color = vbRed
      .Font.Bold = True
      .Interior.Color = vbYellow
      .Resize(1, 4).Borders.LineStyle = 1
      .Resize(1, 4).Borders.ColorIndex = 16
    End With
  Next
  With sh.Range("H:L")
    .EntireColumn.AutoFit
    .HorizontalAlignment = xlCenter
  End With
  sh.Range("J:L").NumberFormat = "#,##0.00;;;"
 
  Application.ScreenUpdating = True
End Sub

Regards
Dante Amor

The good listener few words.
Al buen entendedor, pocas palabras.
-Dicho popular-​

 

[Ali M]

thanks
seem some headers and balance column for specific range are missed !

AGGREGATE1.xlsm
	H	I	J	K	L
1	ITEM	ACCOUNT NAME
2	1	CASH BALANCE	200,000.00		200,000.00
3	2	CASH PR	15,000.00	30,500.00	184,500.00
4	3	CASH DM	9,500.00	37,000.00	157,000.00
5	4	EXPENSE ADMIN		65,000.00	92,000.00
6		TOTAL	224,500.00	132,500.00	92,000.00
7
8	ITEM	ACCOUNT NAME
9	1	PURCHASE	15,000.00		15,000.00
10	2	STOCK1	100,000.00		115,000.00
11	3	PURCHASE RETURNS		4,000.00	111,000.00
12	4	PURCHASE LOW	6,200.00		117,200.00
13	5	EXPENSE PR	37,000.00		154,200.00
14		TOTAL	158,200.00	4,000.00	154,200.00
15
16	ITEM	ACCOUNT NAME
17	1	SALES		201,000.00
18	2	SALES RETURNS	3,500.00
19	3	SELLING LOW		3,200.00
20		TOTAL	3,500.00	204,200.00
21
22	ITEM	ACCOUNT NAME
23	1	STOCK	200,000.00		200,000.00
24		TOTAL	200,000.00		200,000.00
DAILY

 

[DanteAmor]

After many tests and checking that everything was ok, and after more than 80 lines of code, just the last line, I no longer checked that the data was displayed correctly on the screen.
(There are always details in macros and even more so when it comes to cell formats)

Just change this line:

sh.Range("J:L").NumberFormat = "#,##0.00;;;"

For this line:

sh.Range("J:L").NumberFormat = "#,##0.00;-#,##0.00;;@"

The best hunter loses the hare
Al mejor cazador se le va la liebre
-Dicho popular-​

 

[Ali M]

now it works greatly
I grateful for you
thank you so much