split duplicate data from sheet to another based on three columns together

[Hasson]

hello
I need sorting data from the first sheet based on columns B,C,D together . so if any duplicate items in column D depends on also the duplicates dates into columns B,C . when split data should be repeated based on column B,C,D together with sum the values in lastrow for each item . I put the result in second sheet.
note: I will increase data in first sheet at needing ,so should change into second sheet.also when split data should be the same borders and formatting as put it.

RESULT (2) (2).xlsm
	A	B	C	D	E
1	ITEM	FROM DATE	TO DATE	ID	QTY
2	1	1/1/2022	2/11/2022	QQW-1	200.00
3	2	1/1/2022	2/11/2022	QQW-1	300.00
4	3	1/1/2022	2/11/2022	QQW-1	120.00
5	4	1/4/2022	2/14/2022	QQW-2	100.00
6	5	1/5/2022	2/15/2022	QQW-3	120.00
7	6	1/6/2022	2/16/2022	QQW-4	90.00
8	7	1/7/2022	2/17/2022	QQW-1	80.00
9	8	1/8/2022	2/18/2022	QQW-3	60.00
10	9	1/6/2022	2/16/2022	QQW-4	50.00
11	10	1/10/2022	2/20/2022	QQW-5	40.00
12	11	1/11/2022	2/21/2022	QQW-6	30.00
13	12	1/12/2022	2/22/2022	QQW-7	120.00
14	13	1/13/2022	2/23/2022	QQW-8	100.00
15	14	1/14/2022	2/24/2022	QQW-9	110.00
16	15	1/15/2022	2/25/2022	QQW-10	30.00
17	16	1/16/2022	2/26/2022	QQW-11	20.00
18	17	1/17/2022	2/27/2022	QQW-12	120.00
RP

result

RESULT (2) (2).xlsm
	A	B	C	D	E
1			ID
2			QQW-1
3	ITEM	FROM DATE	TO DATE	QTY
4	1	1/1/2022	2/11/2022	200.00
5	2	1/1/2022	2/11/2022	300.00
6	3	1/1/2022	2/11/2022	120.00
7			TOTAL	620.00
8
9			ID
10			QQW-1
11	ITEM	FROM DATE	TO DATE	QTY
12	1	1/7/2022	2/17/2022	80.00
13			TOTAL	80.00
14
15			ID
16			QQW-2
17	ITEM	FROM DATE	TO DATE	QTY
18	1	1/4/2022	2/14/2022	100.00
19			TOTAL	100.00
20
21			ID
22			QQW-3
23	ITEM	FROM DATE	TO DATE	QTY
24	1	1/5/2022	2/15/2022	120.00
25			TOTAL	120.00
26
27			ID
28			QQW-4
29	ITEM	FROM DATE	TO DATE	QTY
30	1	1/6/2022	2/16/2022	90.00
31	2	1/6/2022	2/16/2022	50.00
32			TOTAL	140.00
33
34			ID
35			QQW-5
36	ITEM	FROM DATE	TO DATE	QTY
37	1	1/10/2022	2/20/2022	40.00
38			TOTAL	40.00
39
40			ID
41			QQW-6
42	ITEM	FROM DATE	TO DATE	QTY
43	1	1/11/2022	2/21/2022	30.00
44			TOTAL	30.00
45
46			ID
47			QQW-7
48	ITEM	FROM DATE	TO DATE	QTY
49	1	1/12/2022	2/22/2022	120.00
50			TOTAL	120.00
51
52			ID
53			QQW-8
54	ITEM	FROM DATE	TO DATE	QTY
55	1	1/13/2022	2/23/2022	100.00
56			TOTAL	100.00
57
58			ID
59			QQW-9
60	ITEM	FROM DATE	TO DATE	QTY
61	1	1/14/2022	2/24/2022	110.00
62			TOTAL	110.00
63
64			ID
65			QQW-10
66	ITEM	FROM DATE	TO DATE	QTY
67	1	1/15/2022	2/25/2022	30.00
68			TOTAL	30.00
69
70
71			ID
72			QQW-11
73	ITEM	FROM DATE	TO DATE	QTY	QTY
74	1	1/16/2022	2/26/2022	20.00	20.00
75			TOTAL	20.00	20.00
76
77			ID
78			QQW-12
79	ITEM	FROM DATE	TO DATE	QTY
80	1	1/17/2022	2/27/2022	120.00
81			TOTAL	120.00
82
83
84
outcome

thanks

 

[DanteAmor]

Before running the macro, create a sheet called "Format" and put the following format:

Dante Amor
	A	B	C	D
1			ID
2
3	ITEM	FROM DATE	TO DATE	QTY
4
5			TOTAL
Format

Try this:

Sub SplitData()
  Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
  Dim lr As Long, i As Long, j As Long, n As Long, tot As Double
  Dim ant As String
  
  Application.ScreenUpdating = False
  Set sh1 = Sheets("RP")
  Set sh2 = Sheets("Outcome")
  Set sh3 = Sheets("Format")
  
  sh2.Cells.Clear
  lr = sh1.Range("B" & Rows.Count).End(3).Row
  sh1.Range("A1:E" & lr).Sort sh1.[B2], xlAscending, sh1.[C2], , xlAscending, sh1.[D2], xlAscending, xlYes

  ant = sh1.Range("B2").Value & "|" & sh1.Range("D2").Value
  sh3.Range("A1:D5").Copy sh2.Range("A1")
  sh2.Range("C2").Value = sh1.Range("D2").Value
  j = 4
  n = 1
  For i = 2 To lr + 1
    If ant <> sh1.Range("B" & i).Value & "|" & sh1.Range("D" & i).Value Then
      sh2.Range("D" & j).Value = tot
      If i = lr + 1 Then Exit For
      j = j + 2
      n = 1
      sh3.Range("A1:D5").Copy sh2.Range("A" & j)
      sh2.Range("C" & j + 1).Value = sh1.Range("D" & i).Value
      tot = 0
      j = j + 3
    ElseIf n > 1 Then
      sh2.Range("A" & j - 1).EntireRow.Copy
      sh2.Range("A" & j).Insert Shift:=xlDown
    End If
    sh2.Range("A" & j).Value = n
    sh2.Range("B" & j).Value = sh1.Range("B" & i).Value
    sh2.Range("C" & j).Value = sh1.Range("C" & i).Value
    sh2.Range("D" & j).Value = sh1.Range("E" & i).Value
    tot = tot + sh1.Range("E" & i).Value
    n = n + 1
    j = j + 1
    ant = sh1.Range("B" & i).Value & "|" & sh1.Range("D" & i).Value
  Next
  Application.ScreenUpdating = True
End Sub

 

[Hasson]

perfect! how can I auto fit the columns for sheet outcome ?

 

[DanteAmor]

Before this line:
Application.ScreenUpdating = True

Add this line:
sh2.Range("A:D").EntireColumn.AutoFit

 

[Hasson]

many thanks