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

 

[Marcelo Branco]

Do you want a solution using formulas just for testing purposes?

M.

 

[saraapple]

Its is part of a bigger process I have going on.
I have to list orders by priority, then split the summarised data I have into singles (this step) so I can break them down into loads and split loads. My plan was to generate the data, split to singles, run a macro I have to split into groups of 40, then I was going to resummarise the results by pivoting the data.
This is my basic idea, am I going in the wrong direction to achieve my desired result?
Thank you for taking the time

 

[Marcelo Branco]

I'm not sure the solution below, using formulas with a helper column to make things easier, would help with the process you're developing.
Anyway, look at the solution I found to list in the F:I columns what you showed in post #1.

Pasta1
	F	G	H	I	J	K
1	Priority	Store Number	Store Name	Sum of Pallet Total		Helper
2	1	114	Jersey	1		0
3	1	114	Jersey	1		2
4	3	865	Broadstairs	1		6
5	3	865	Broadstairs	1		17
6	3	865	Broadstairs	1		23
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
Plan4

Cell Formulas
Range	Formula
F2:F25	F2	=IF(ROWS(F$2:F2)>D$6,"",INDEX(A$2:A$5,MATCH(ROWS(F$2:F2)-1,K$2:K$6)))
G2:H25	G2	=IF(F2="","",VLOOKUP(F2,A$2:B$5,2,0))
K2:K6	K2	=SUM(D$1:D1)
I25	I25	=SUM(I2:I24)

Hope this helps

M.

 

[saraapple]

Hi, thank you again but I don't think this will work as your helper column is on the finished data that I cant currently produce!
I have just been looking at reverse pivot tables - could this be a way?

My data is for example:

Sara 3
Marcelo 5

I want this to become:

Sara 1
Sara 1
Sara1
Marcelo 1
Marcelo 1
Marcelo 1
Marcelo 1
Marcelo 1

Thanks
Sara

 

[Special-K99]

Try (untested)
Input on Sheet1
Output on Sheet2

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("Sheet2").Cells(k,1)=Worksheets("Sheet1".Cells(i,1)
Worksheets("Sheet2").Cells(k,2)=Worksheets("Sheet1".Cells(i,2)
Worksheets("Sheet2").Cells(k,3)=Worksheets("Sheet1".Cells(i,3)
Next j
Next i
End Sub

 

[saraapple]

Thank you - is this trying to put the data into sheet 2?
All my data is in sheet 1 - I have tried amending the above to reflect this but I get a compile error:

 

[Marcelo Branco]

Hi, thank you again but I don't think this will work as your helper column is on the finished data that I cant currently produce!

I assumed the data in columns A:D were already there (given). Am i wrong?

M.

 

[Marcelo Branco]

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.

 

[Special-K99]

Sorry my fault, try changing this bit to

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))

No real need for column I, it will always be 1.