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]

it is not an option to apply a filter or to delete rows afterwards

I don't understand.
1. you want rows with product# is text in final result and then Filter
2. You don't want them

 

[Lars1]

I don't understand.
1. you want rows with product# is text in final result and then Filter
2. You don't want them

Sorry for not beeing clear in my answer.

If the product# is a letter = I don't want it on my final list.
If the product# is a number = i would like it on my final list

 

[Peter_SSs]

Try this with a copy of your data.

Sub CombineData()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, uba2 As Long
  
  a = Range("F6", Range("N" & Rows.Count).End(xlUp)).Value
  uba2 = UBound(a, 2)
  ReDim b(1 To 3 * UBound(a), 1 To 4)
  For i = 1 To UBound(a)
    For j = 1 To uba2 Step 3
      If IsNumeric(a(i, j + 1)) Then
        k = k + 1
        b(k, 1) = a(i, j): b(k, 2) = (j + 2) / 3: b(k, 3) = a(i, j + 1): b(k, 4) = a(i, j + 2)
      End If
    Next j
  Next i
  With Range("A5:D5").Resize(k)
    .Value = b
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Key2:=.Columns(2), Order2:=xlAscending, Key3:=.Columns(4), Order3:=xlAscending, Header:=xlNo
    .Columns(1).NumberFormat = "ddmmyyyy"
  End With
End Sub

My sample data (F:G) and results (A:D)

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

 

[Lars1]

Try this with a copy of your data.

Sub CombineData()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, uba2 As Long
 
  a = Range("F6", Range("N" & Rows.Count).End(xlUp)).Value
  uba2 = UBound(a, 2)
  ReDim b(1 To 3 * UBound(a), 1 To 4)
  For i = 1 To UBound(a)
    For j = 1 To uba2 Step 3
      If IsNumeric(a(i, j + 1)) Then
        k = k + 1
        b(k, 1) = a(i, j): b(k, 2) = (j + 2) / 3: b(k, 3) = a(i, j + 1): b(k, 4) = a(i, j + 2)
      End If
    Next j
  Next i
  With Range("A5:D5").Resize(k)
    .Value = b
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Key2:=.Columns(2), Order2:=xlAscending, Key3:=.Columns(4), Order3:=xlAscending, Header:=xlNo
    .Columns(1).NumberFormat = "ddmmyyyy"
  End With
End Sub

My sample data (F:G) and results (A:D)

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

Hi Peter

Thank you so much

This works perfectly and all in one solution - thanks.
I am quit new to this VBA, but i am trying to "learning by doing".

I'll hope it's okay with an additional question...
If possible i would like to make my data a bit easier and only have one column with date.

MrExcelData.xlsm
	F	G	H	I	J	K	L
1		Line 1		Line 2		Line 3
2	Date	Product #	Quantity	Product #	Quantity	Product #	Quantity
3	06-09-2021	4512	1.000	4519	500	4512	1.000
4	07-09-2021	5896	2.000			5896	2.000
5	08-09-2021	2478	3.000	5555	700	2478	3.000
6	08-09-2021	3578	4.000	2589	900	b	10
7	08-09-2021	1287	5.000
8	09-09-2021	1287	6.000
9	10-09-2021					1287	5.000
10	11-09-2021			3045	1.100
11	12-09-2021			1687	1.300
12	13-09-2021			6222	1.500
13	14-09-2021
14	15-09-2021
15	16-09-2021					6999	6.000
Liste til Produktionsordre (2)

 

[Peter_SSs]

i would like to make my data a bit easier and only have one column with date.

Try the code below.

I was bit confused about the required date format of the results as the following did not agree with the image and mini-sheet of the expected results in post 1. However, you can adjust that in the 'NumberFormat' line near the end of the code.

If i would like to format the date DDMMYYYY

Sub CombineData_v2()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, uba2 As Long
  
  a = Range("F3", Range("F" & Rows.Count).End(xlUp)).Resize(, 7).Value
  uba2 = UBound(a, 2)
  ReDim b(1 To 3 * UBound(a), 1 To 4)
  For i = 1 To UBound(a)
    For j = 2 To uba2 Step 2
      If IsNumeric(a(i, j)) And Len(a(i, j)) > 0 Then
        k = k + 1
        b(k, 1) = a(i, 1): b(k, 2) = j / 2: b(k, 3) = a(i, j): b(k, 4) = a(i, j + 1)
      End If
    Next j
  Next i
  With Range("A2:D2").Resize(k)
    .Value = b
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Key2:=.Columns(2), Order2:=xlAscending, Key3:=.Columns(4), Order3:=xlAscending, Header:=xlNo
    .Columns(1).NumberFormat = "ddmmyyyy"
  End With
End Sub

Again, my data & results.

Lars1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Line	Product #	Quantity			Line 1		Line 2		Line 3
2	06092021	1	4512	1000		Date	Product #	Quantity	Product #	Quantity	Product #	Quantity
3	06092021	2	4519	500		06-09-2021	4512	1000	4519	500	4512	1000
4	06092021	3	4512	1000		07-09-2021	5896	2000			5896	2000
5	07092021	1	5896	2000		08-09-2021	2478	3000	5555	700	2478	3000
6	07092021	3	5896	2000		08-09-2021	3578	4000	2589	900	b	10
7	08092021	1	2478	3000		08-09-2021	1287	5000
8	08092021	1	3578	4000		09-09-2021	1287	6000
9	08092021	1	1287	5000		10-09-2021					1287	5000
10	08092021	2	5555	700		11-09-2021			3045	1100
11	08092021	2	2589	900		12-09-2021			1687	1300
12	08092021	3	2478	3000		13-09-2021			6222	1500
13	09092021	1	1287	6000		14-09-2021
14	10092021	3	1287	5000		15-09-2021
15	11092021	2	3045	1100		16-09-2021					6999	6000
16	12092021	2	1687	1300
17	13092021	2	6222	1500
18	16092021	3	6999	6000
Sheet4

 

[Lars1]

Try the code below.

I was bit confused about the required date format of the results as the following did not agree with the image and mini-sheet of the expected results in post 1. However, you can adjust that in the 'NumberFormat' line near the end of the code.

Sub CombineData_v2()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, uba2 As Long
 
  a = Range("F3", Range("F" & Rows.Count).End(xlUp)).Resize(, 7).Value
  uba2 = UBound(a, 2)
  ReDim b(1 To 3 * UBound(a), 1 To 4)
  For i = 1 To UBound(a)
    For j = 2 To uba2 Step 2
      If IsNumeric(a(i, j)) And Len(a(i, j)) > 0 Then
        k = k + 1
        b(k, 1) = a(i, 1): b(k, 2) = j / 2: b(k, 3) = a(i, j): b(k, 4) = a(i, j + 1)
      End If
    Next j
  Next i
  With Range("A2:D2").Resize(k)
    .Value = b
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Key2:=.Columns(2), Order2:=xlAscending, Key3:=.Columns(4), Order3:=xlAscending, Header:=xlNo
    .Columns(1).NumberFormat = "ddmmyyyy"
  End With
End Sub

Again, my data & results.

Lars1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Line	Product #	Quantity			Line 1		Line 2		Line 3
2	06092021	1	4512	1000		Date	Product #	Quantity	Product #	Quantity	Product #	Quantity
3	06092021	2	4519	500		06-09-2021	4512	1000	4519	500	4512	1000
4	06092021	3	4512	1000		07-09-2021	5896	2000			5896	2000
5	07092021	1	5896	2000		08-09-2021	2478	3000	5555	700	2478	3000
6	07092021	3	5896	2000		08-09-2021	3578	4000	2589	900	b	10
7	08092021	1	2478	3000		08-09-2021	1287	5000
8	08092021	1	3578	4000		09-09-2021	1287	6000
9	08092021	1	1287	5000		10-09-2021					1287	5000
10	08092021	2	5555	700		11-09-2021			3045	1100
11	08092021	2	2589	900		12-09-2021			1687	1300
12	08092021	3	2478	3000		13-09-2021			6222	1500
13	09092021	1	1287	6000		14-09-2021
14	10092021	3	1287	5000		15-09-2021
15	11092021	2	3045	1100		16-09-2021					6999	6000
16	12092021	2	1687	1300
17	13092021	2	6222	1500
18	16092021	3	6999	6000
Sheet4

PERFECT
Thank you very much. This is perfect.

 

[Peter_SSs]

You're welcome. Glad it worked for you. Thanks for the follow-up.

 

[Lars1]

You're welcome. Glad it worked for you. Thanks for the follow-up.

i am gratefull for all the help i can get


Just one last thing.
If i choose to put my data in another sheet like "Sheet1":

How should this be then:

a = Range("F3", Range("F"......

 

[Peter_SSs]

If i choose to put my data in another sheet like "Sheet1":

How should this be then:

a = Range("F3", Range("F"......

Like this. Make sure that you don't miss the "." in front of Range (twice)

With Sheets("Sheet1")
  a = .Range("F3", .Range("F" & Rows.Count).End(xlUp)).Resize(, 7).Value
End With

 

[Lars1]

Like this. Make sure that you don't miss the "." in front of Range (twice)

With Sheets("Sheet1")
  a = .Range("F3", .Range("F" & Rows.Count).End(xlUp)).Resize(, 7).Value
End With

Ok...
This generates an error: