Formula or macro to itemise summarised results

[saraapple]

I am hoping someone could advise me on the below - or just any ideas that could help.
I want to do the opposite of a pivot table of sorts, my data is summarised and I need to break it down into singles.
The reason I need to break it into singles, is I need to plan loads of say 40 with the potential for each load to have a remainder - hence the need for singles to accurately calculate.

I already have the macro to split the data from singles (I think with a few amendments) but I need to get to singles to test it works!

Here is what I have and what I want:

New Wave File Template Aug 2021.xlsm
	A	B	C	D	E	F	G	H	I
1	Priority	Store Number	Store Name	Sum of Pallet Total		Priority	Store Number	Store Name	Sum of Pallet Total
2	1	114	Jersey	2		1	114	Jersey	1
3	3	865	Broadstairs	4		1	114	Jersey	1
4	4	800	Canterbury	11		3	865	Broadstairs	1
5	5	265	Ashford	6		3	865	Broadstairs	1
6				23		3	865	Broadstairs	1
7						3	865	Broadstairs	1
8						4	800	Canterbury	1
9						4	800	Canterbury	1
10						4	800	Canterbury	1
11						4	800	Canterbury	1
12						4	800	Canterbury	1
13						4	800	Canterbury	1
14						4	800	Canterbury	1
15						4	800	Canterbury	1
16						4	800	Canterbury	1
17						4	800	Canterbury	1
18						4	800	Canterbury	1
19						5	265	Ashford	1
20						5	265	Ashford	1
21						5	265	Ashford	1
22						5	265	Ashford	1
23						5	265	Ashford	1
24						5	265	Ashford	1
25									23
Sheet6

Cell Formulas
Range	Formula
A1:D5	A1	='Stoke 1 Pallets'!F6
D6	D6	=SUM(D2:D5)
I25	I25	=SUM(I2:I24)

Is there a function in excel to reverse a pivot table?

Thank you for your time
Sara

 

[saraapple]

Sub Macro22()
'
' Macro22 Macro
'Sub k1()
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
k = 0
For i = 2 To lastrow
For j = 1 To Worksheets("Sheet2").Cells(i, 4)
k = k + 1
Worksheets("Sheet1").Cells(k,6)=Worksheets("Sheet1".Cells(i,1))
Worksheets("Sheet1").Cells(k,7)=Worksheets("Sheet1".Cells(i,2))
Worksheets("Sheet1").Cells(k,8)=Worksheets("Sheet1".Cells(i,3))
Next j
Next i
End Sub '

This is what I now have but still:

These error messages are my nightmare, I never understand what they mean?!?

 

[saraapple]

Using the example you showed in post #5

Assuming data in A:B

Pasta1
	A	B	C	D	E	F
1	Names	Total		List	Pallets	Helper
2	Sara	3		Sara	1	0
3	Marcelo	5		Sara	1	3
4		8		Sara	1	8
5				Marcelo	1
6				Marcelo	1
7				Marcelo	1
8				Marcelo	1
9				Marcelo	1
10
Plan5

Cell Formulas
Range	Formula
D2:D10	D2	=IF(ROWS(D$2:D2)>B$4,"",INDEX(A$2:A$3,MATCH(ROWS(D$2:D2)-1,F$2:F$4)))
E2:E10	E2	=IF(D2="","",--(D2<>""))
F2:F4	F2	=SUM(B$1:B1)
B4	B4	=SUM(B2:B3)

M.

You are right! I am just trying to tweak this to cover my actual data but this will do the job.
My only concern is handing this over to none excel people, which is why I am also looking at the possibility of a macro (I need as little operator involvement as possible) Thank you so much, as soon as I have this working at I can look at protection options and take things from there

 

[Marcelo Branco]

I am also looking at the possibility of a macro (I need as little operator involvement as possible)

Try

Sub aTest()
    'Assumes data in columns A:D; headers in row 1
    'Results in columns F:I
    Dim vData As Variant, vResult As Variant
    Dim lNumRows As Long, i As Long, j As Long, lLin As Long
   
    vData = Range("A2:D" & Cells(Rows.Count, "A").End(xlUp).Row)
    lNumRows = Application.Sum(Application.Index(vData, 0, 4))
    vResult = Range("F2").Resize(lNumRows, 4)
       
    For i = 1 To UBound(vData, 1)
        For j = 1 To vData(i, 4)
            lLin = lLin + 1
            vResult(lLin, 1) = vData(i, 1)
            vResult(lLin, 2) = vData(i, 2)
            vResult(lLin, 3) = vData(i, 3)
            vResult(lLin, 4) = 1
        Next j
    Next i
    Range("F1:I1").Value = Range("A1:D1").Value
    Range("F2").Resize(lNumRows, 4) = vResult
    Columns("F:I").AutoFit
End Sub

M.

 

[saraapple]

Try

Sub aTest()
    'Assumes data in columns A:D; headers in row 1
    'Results in columns F:I
    Dim vData As Variant, vResult As Variant
    Dim lNumRows As Long, i As Long, j As Long, lLin As Long
  
    vData = Range("A2:D" & Cells(Rows.Count, "A").End(xlUp).Row)
    lNumRows = Application.Sum(Application.Index(vData, 0, 4))
    vResult = Range("F2").Resize(lNumRows, 4)
      
    For i = 1 To UBound(vData, 1)
        For j = 1 To vData(i, 4)
            lLin = lLin + 1
            vResult(lLin, 1) = vData(i, 1)
            vResult(lLin, 2) = vData(i, 2)
            vResult(lLin, 3) = vData(i, 3)
            vResult(lLin, 4) = 1
        Next j
    Next i
    Range("F1:I1").Value = Range("A1:D1").Value
    Range("F2").Resize(lNumRows, 4) = vResult
    Columns("F:I").AutoFit
End Sub

M.

This is fantastic THANK YOU!
The formula way works great to and I have a few other uses for it.
I am going to analyze your code now, figure out exactly how it works thank you again this is a HUGE help to me

 

[Marcelo Branco]

You're welcome. Glad to help

M.

 

[Special-K99]

Sub Macro22()
'
' Macro22 Macro
'Sub k1()
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
k = 0
For i = 2 To lastrow
For j = 1 To Worksheets("Sheet2").Cells(i, 4)
k = k + 1
Worksheets("Sheet1").Cells(k,6)=Worksheets("Sheet1".Cells(i,1))
Worksheets("Sheet1").Cells(k,7)=Worksheets("Sheet1".Cells(i,2))
Worksheets("Sheet1").Cells(k,8)=Worksheets("Sheet1".Cells(i,3))
Next j
Next i
End Sub '

This is what I now have but still:

View attachment 45043
These error messages are my nightmare, I never understand what they mean?!?

Looks like you've sorted it now.
Just for solution purposes I put a bracket in the wrong place

Worksheets("Sheet1").Cells(k,6)=Worksheets("Sheet1").Cells(i,1)
Worksheets("Sheet1").Cells(k,7)=Worksheets("Sheet1").Cells(i,2)
Worksheets("Sheet1").Cells(k,8)=Worksheets("Sheet1").Cells(i,3)

That'll teach me to post untested code