VBA combine and sort several lists in to one list

[Lars1]

Hi

I have a problem that i hope some one in here could help me with.

I have several lists/coloumns i would like to combine and sort.
For specific reasons, this should be solved by VBA and not Pivot.

If a product number is NOT a number, it should not be on the final list.

MrExcelData.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2			AFTER							BEFORE
3
4	Date	Line	Product #	Quantity			Line 1			Line 2			Line 3
5	06-09-2021	1	4512	1.000		Date	Product #	Quantity	Date	Product #	Quantity	Date	Product #	Quantity
6	06-09-2021	2	4519	500		06-09-2021	4512	1.000	06-09-2021	4519	500	06-09-2021	4512	1.000
7	06-09-2021	3	4512	1.000		07-09-2021	5896	2.000	07-09-2021	5555	700	07-09-2021	5896	2.000
8	07-09-2021	1	5896	2.000		08-09-2021	2478	3.000	08-09-2021	2589	900	08-09-2021	2478	3.000
9	07-09-2021	2	5555	700		08-09-2021	3578	4.000	08-09-2021	3045	1.100	08-09-2021	b	10
10	07-09-2021	3	5896	2.000		08-09-2021	1287	5.000	08-09-2021	1687	1.300	08-09-2021	1287	5.000
11	08-09-2021	1	2478	3.000		09-09-2021	1287	6.000	09-09-2021	6222	1.500	09-09-2021	6999	6.000
12	08-09-2021	1	3578	4.000
13	08-09-2021	1	1287	5.000
14	08-09-2021	2	2589	900
15	08-09-2021	2	3045	1.100
16	08-09-2021	2	1687	1.300
17	08-09-2021	3	2478	3.000
18	08-09-2021	3	1287	5.000
19	09-09-2021	1	6999	6.000
20	09-09-2021	2	6222	1.500
21	09-09-2021	3	6999	6.000
22
23
Liste til Produktionsordre

 

[maabadi]

I supposed your first Date is in A3 (this File) at Sheet1:

Book1(AutoRecovered).xlsm
	A	B	C	D	E	F	G	H	I	J
1		Line 1			Line 2			Line 3
2	Date	Product #	Quantity	Date	Product #	Quantity	Date	Product #	Quantity
3	9/6/2021	4512	1000	9/6/2021	4519	500	9/6/2021	4512	1000
4	9/7/2021	5896	2000	9/7/2021	5555	700	9/7/2021	5896	2000
5	9/8/2021	2478	3000	9/8/2021	2589	900	9/8/2021	2478	3000
6	9/8/2021	3578	4000	9/8/2021	3045	1100	9/8/2021	b	10
7	9/8/2021	1287	5000	9/8/2021	1687	1300	9/8/2021	1287	5000
8	9/9/2021	1287	6000	9/9/2021	6222	1500	9/9/2021	6999	6000
9
10
Sheet1

Then with this macro you see result at sheet2:

Sub SortData()
Dim i As Long, j As Long, Lr As Long, Sh1 As Worksheet, Sh2 As Worksheet
Set Sh1 = Sheets("Sheet1")
Set Sh2 = Sheets("Sheet2")
Lr = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Sh2.Range("A2:D2").Value = Array("Date", "Line", "Product#", "Quantity")
For i = 3 To 3 * Lr - 4
Sh2.Range("A" & i).Value = Sh1.Cells(Int((i - 3) / 3) + 3, ((i - 3) Mod 3) * 3 + 1).Value
Sh2.Range("B" & i).Value = (i - 3) Mod 3 + 1
Sh2.Range("C" & i).Value = Sh1.Cells(Int((i - 3) / 3) + 3, ((i - 3) Mod 3) * 3 + 2).Value
Sh2.Range("D" & i).Value = Sh1.Cells(Int((i - 3) / 3) + 3, ((i - 3) Mod 3) * 3 + 3).Value
Next i
End Sub

 

[Lars1]

I supposed your first Date is in A3 (this File) at Sheet1:

Book1(AutoRecovered).xlsm
	A	B	C	D	E	F	G	H	I	J
1		Line 1			Line 2			Line 3
2	Date	Product #	Quantity	Date	Product #	Quantity	Date	Product #	Quantity
3	9/6/2021	4512	1000	9/6/2021	4519	500	9/6/2021	4512	1000
4	9/7/2021	5896	2000	9/7/2021	5555	700	9/7/2021	5896	2000
5	9/8/2021	2478	3000	9/8/2021	2589	900	9/8/2021	2478	3000
6	9/8/2021	3578	4000	9/8/2021	3045	1100	9/8/2021	b	10
7	9/8/2021	1287	5000	9/8/2021	1687	1300	9/8/2021	1287	5000
8	9/9/2021	1287	6000	9/9/2021	6222	1500	9/9/2021	6999	6000
9
10
Sheet1

Then with this macro you see result at sheet2:

Sub SortData()
Dim i As Long, j As Long, Lr As Long, Sh1 As Worksheet, Sh2 As Worksheet
Set Sh1 = Sheets("Sheet1")
Set Sh2 = Sheets("Sheet2")
Lr = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Sh2.Range("A2:D2").Value = Array("Date", "Line", "Product#", "Quantity")
For i = 3 To 3 * Lr - 4
Sh2.Range("A" & i).Value = Sh1.Cells(Int((i - 3) / 3) + 3, ((i - 3) Mod 3) * 3 + 1).Value
Sh2.Range("B" & i).Value = (i - 3) Mod 3 + 1
Sh2.Range("C" & i).Value = Sh1.Cells(Int((i - 3) / 3) + 3, ((i - 3) Mod 3) * 3 + 2).Value
Sh2.Range("D" & i).Value = Sh1.Cells(Int((i - 3) / 3) + 3, ((i - 3) Mod 3) * 3 + 3).Value
Next i
End Sub

Hi

Looks very much like the solution i am looking for.

If i would like to format the date DDMMYYYY...
How to do this ?

And i also would like to filter out all lines where product # is not a number.

 

[alansidman]

Accomplished with Power Query. Here is the Mcode for the steps taken

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Line 1", type any}, {"Column2", type any}, {"Column3", type any}, {"Line 2", type any}, {"Column4", type any}, {"Column5", type any}, {"Line 3", type any}, {"Column6", type any}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1:Date", type date}, {"Column3:Date", type date}, {"Column5:Date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Column1:Date", "Column3:Date", "Column5:Date"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Column3:Date", "Column5:Date"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Attribute", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Product#", each if Text.StartsWith([Attribute.2],"Product") then [Value] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Quantity", each if Text.StartsWith([Attribute.2],"Quantity") then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"Quantity"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Up","Line","",Replacer.ReplaceText,{"Attribute.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Attribute.1] = " 1" or [Attribute.1] = " 2" or [Attribute.1] = " 3")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Attribute.2", "Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Attribute.1", "Line"}, {"Column1:Date", "Date"}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Product#", Int64.Type}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type3", {"Product#"})
in
    #"Removed Errors"

 

[maabadi]

And if you want Formula Method:

Book1(AutoRecovered).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Line 1			Line 2			Line 3			Date	Line	Product#	Quantity
2	Date	Product #	Quantity	Date	Product #	Quantity	Date	Product #	Quantity		9/6/2021	1	4512	1000
3	9/6/2021	4512	1000	9/6/2021	4519	500	9/6/2021	4512	1000		9/6/2021	2	4519	500
4	9/7/2021	5896	2000	9/7/2021	5555	700	9/7/2021	5896	2000		9/6/2021	3	4512	1000
5	9/8/2021	2478	3000	9/8/2021	2589	900	9/8/2021	2478	3000		9/7/2021	1	5896	2000
6	9/8/2021	3578	4000	9/8/2021	3045	1100	9/8/2021	b	10		9/7/2021	2	5555	700
7	9/8/2021	1287	5000	9/8/2021	1687	1300	9/8/2021	1287	5000		9/7/2021	3	5896	2000
8	9/9/2021	1287	6000	9/9/2021	6222	1500	9/9/2021	6999	6000		9/8/2021	1	2478	3000
9											9/8/2021	2	2589	900
10											9/8/2021	3	2478	3000
11											9/8/2021	1	3578	4000
12											9/8/2021	2	3045	1100
13											9/8/2021	3	b	10
14											9/8/2021	1	1287	5000
15											9/8/2021	2	1687	1300
16											9/8/2021	3	1287	5000
17											9/9/2021	1	1287	6000
18											9/9/2021	2	6222	1500
19											9/9/2021	3	6999	6000
20
21
Sheet1

Cell Formulas
Range	Formula
K2:K19	K2	=INDEX($A$3:$I$8,INT((ROWS($K$1:K1)-1)/3)+1,MOD(ROWS($K$1:K1)-1,3)*3+1)
L2:L19	L2	=MOD(ROWS($K$1:K2)-2,3) + 1
M2:M19	M2	=INDEX($A$3:$I$8,INT((ROWS($K$1:K1)-1)/3)+1,MOD(ROWS($K$1:K1)-1,3)*3+COLUMNS($K$1:L1))
N2:N19	N2	=INDEX($A$3:$I$8,INT((ROWS($K$1:K1)-1)/3)+1,MOD(ROWS($K$1:K1)-1,3)*3+COLUMNS($K$1:M1))

 

[Lars1]

And if you want Formula Method:

Book1(AutoRecovered).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Line 1			Line 2			Line 3			Date	Line	Product#	Quantity
2	Date	Product #	Quantity	Date	Product #	Quantity	Date	Product #	Quantity		9/6/2021	1	4512	1000
3	9/6/2021	4512	1000	9/6/2021	4519	500	9/6/2021	4512	1000		9/6/2021	2	4519	500
4	9/7/2021	5896	2000	9/7/2021	5555	700	9/7/2021	5896	2000		9/6/2021	3	4512	1000
5	9/8/2021	2478	3000	9/8/2021	2589	900	9/8/2021	2478	3000		9/7/2021	1	5896	2000
6	9/8/2021	3578	4000	9/8/2021	3045	1100	9/8/2021	b	10		9/7/2021	2	5555	700
7	9/8/2021	1287	5000	9/8/2021	1687	1300	9/8/2021	1287	5000		9/7/2021	3	5896	2000
8	9/9/2021	1287	6000	9/9/2021	6222	1500	9/9/2021	6999	6000		9/8/2021	1	2478	3000
9											9/8/2021	2	2589	900
10											9/8/2021	3	2478	3000
11											9/8/2021	1	3578	4000
12											9/8/2021	2	3045	1100
13											9/8/2021	3	b	10
14											9/8/2021	1	1287	5000
15											9/8/2021	2	1687	1300
16											9/8/2021	3	1287	5000
17											9/9/2021	1	1287	6000
18											9/9/2021	2	6222	1500
19											9/9/2021	3	6999	6000
20
21
Sheet1

Cell Formulas
Range	Formula
K2:K19	K2	=INDEX($A$3:$I$8,INT((ROWS($K$1:K1)-1)/3)+1,MOD(ROWS($K$1:K1)-1,3)*3+1)
L2:L19	L2	=MOD(ROWS($K$1:K2)-2,3) + 1
M2:M19	M2	=INDEX($A$3:$I$8,INT((ROWS($K$1:K1)-1)/3)+1,MOD(ROWS($K$1:K1)-1,3)*3+COLUMNS($K$1:L1))
N2:N19	N2	=INDEX($A$3:$I$8,INT((ROWS($K$1:K1)-1)/3)+1,MOD(ROWS($K$1:K1)-1,3)*3+COLUMNS($K$1:M1))

Unfortunately VBA is the only solution in this case...

 

[maabadi]

And i also would like to filter out all lines where product # is not a number.

You want exclude that product from results ( Delete that lines at result) or include then filter

 

[Lars1]

You want exclude that product from results ( Delete that lines at result) or include then filter

Thank you so much for your effort in this.

As i stated... Unfortunately the only solution to this is to use VBA.
it is not an option to apply a filter or to delete rows afterwards.

 

[alansidman]

the only solution to this is to use VBA

Why?

 

[Lars1]

Why?