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

 

[Peter_SSs]

I am i the process of learning this VBA.
Is it too much to ask if you can put in some comments on what you are doing with your code ?

Hope this helps

Sub CombineData_v3()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, uba2 As Long
  
  With Sheets("Sheet2")
    'Put all the data values into an array that is 7 columns wide and however many rows down.
    a = .Range("F3", .Range("F" & Rows.Count).End(xlUp)).Resize(, 7).Value
  End With
  'This is how 'wide' the array 'a' is. It will be 7. This saves re-calculating it over and over as we loop later in the code.
  uba2 = UBound(a, 2)
  'make an array plent big enough to hold all the data (number of data rows x 3, and 4 'columns' wide)
  ReDim b(1 To 3 * UBound(a), 1 To 4)
  'Work through each row of the array ..
  For i = 1 To UBound(a)
    '.. and across each row, but in jumps of 2 because each set of data has two columns (+ the date)
    ' This is alo where we use the uba2 (7) calculated earlier
    For j = 2 To uba2 Step 2
      'If column 2 (or 4 or 6 as we jump across) contains a number then
      If IsNumeric(a(i, j)) And Len(a(i, j)) > 0 Then
        'Get ready for a new results row
        k = k + 1
        'Put date in 1st col, Line no. in 2nd col, Prod# in 3rd col and Qty in 4th col
        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
    'Jump across 2 cols
    Next j
  'Move down to next row
  Next i
  'Get ready to write the results in Sheet1 (k has counted how many rows of results we found)
  With Sheets("Sheet1").Range("A2:D2").Resize(k)
    'Enter the values from the results array 'b'
    .Value = b
    'Sort results 1st by Date, then by Line No. then by Quantity
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Key2:=.Columns(2), Order2:=xlAscending, Key3:=.Columns(4), Order3:=xlAscending, Header:=xlNo
    'Apply appropriate Date format to the first column
    .Columns(1).NumberFormat = "ddmmyyyy"
  End With
End Sub

 

[Lars1]

Hope this helps

Sub CombineData_v3()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, uba2 As Long
 
  With Sheets("Sheet2")
    'Put all the data values into an array that is 7 columns wide and however many rows down.
    a = .Range("F3", .Range("F" & Rows.Count).End(xlUp)).Resize(, 7).Value
  End With
  'This is how 'wide' the array 'a' is. It will be 7. This saves re-calculating it over and over as we loop later in the code.
  uba2 = UBound(a, 2)
  'make an array plent big enough to hold all the data (number of data rows x 3, and 4 'columns' wide)
  ReDim b(1 To 3 * UBound(a), 1 To 4)
  'Work through each row of the array ..
  For i = 1 To UBound(a)
    '.. and across each row, but in jumps of 2 because each set of data has two columns (+ the date)
    ' This is alo where we use the uba2 (7) calculated earlier
    For j = 2 To uba2 Step 2
      'If column 2 (or 4 or 6 as we jump across) contains a number then
      If IsNumeric(a(i, j)) And Len(a(i, j)) > 0 Then
        'Get ready for a new results row
        k = k + 1
        'Put date in 1st col, Line no. in 2nd col, Prod# in 3rd col and Qty in 4th col
        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
    'Jump across 2 cols
    Next j
  'Move down to next row
  Next i
  'Get ready to write the results in Sheet1 (k has counted how many rows of results we found)
  With Sheets("Sheet1").Range("A2:D2").Resize(k)
    'Enter the values from the results array 'b'
    .Value = b
    'Sort results 1st by Date, then by Line No. then by Quantity
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Key2:=.Columns(2), Order2:=xlAscending, Key3:=.Columns(4), Order3:=xlAscending, Header:=xlNo
    'Apply appropriate Date format to the first column
    .Columns(1).NumberFormat = "ddmmyyyy"
  End With
End Sub

Thank you once again Peter.
This is very helpful for me in my learning process.

THANKS

 

[Peter_SSs]

Cheers

 

[Lars1]

Thank you once again Peter.
This is very helpful for me in my learning process.

THANKS

Hi Peter

Maybe i already haavve used alle my questions here, but i'll try anyway

Ot looks like there could be a small change in my data because an extra column named TEXT is added..
This column should NOT be part of the final list, so how can i "jump over" these extra columns ?

I am using your above code without any changes.

MrExcelData.xlsm
	F	G	H	I	J	K	L	M	N	O	P	Q
1		Line 1				Line 2				Line 3
2	Date	Product #	TEXT	Quantity	Date	Product #	TEXT	Quantity	Date	Product #	TEXT	Quantity
3	06-09-2021	4512	Text 1	1.000	06-09-2021	4519	Text 2	500	06-09-2021	4512	Text 3	1.000
4	07-09-2021	5896	Text 1	2.000	07-09-2021	5555	Text 2	700	07-09-2021	5896	Text 3	2.000
5	08-09-2021	2478	Text 1	3.000	08-09-2021	2589	Text 2	900	08-09-2021	2478	Text 3	3.000
6	08-09-2021	3578	Text 1	4.000	08-09-2021	3045	Text 2	1.100	08-09-2021	b	Text 3	10
7	08-09-2021	1287	Text 1	5.000	08-09-2021	1687	Text 2	1.300	08-09-2021	1287	Text 3	5.000
8	09-09-2021	1287	Text 1	6.000	09-09-2021	6222	Text 2	1.500	09-09-2021	6999	Text 3	6.000
Sheet1

MrExcelData.xlsm
	A	B	C	D
1	Date	Line	Product #	Quantity
2	06092021	1	4512	1.000
3	06092021	2	4519	500
4	06092021	3	4512	1.000
5	07092021	1	5896	2.000
6	07092021	3	5896	2.000
7	08092021	1	2478	3.000
8	08092021	1	3578	4.000
9	08092021	1	1287	5.000
10	08092021	2	5555	700
11	08092021	2	2589	900
12	08092021	3	2478	3.000
13	09092021	1	1287	6.000
14	10092021	3	1287	5.000
15	11092021	2	3045	1.100
16	16092021	3	6999	6.000
Sheet1

 

[Peter_SSs]

I'm confused.

Originally your raw data had a date column for every Line.
In post 14 you changed to having only one date column. My code was re-written to cope with that layout.
Now your latest mini-sheet shows a date column for every Line again.

What is actually going on in relation to date columns?

 

[Lars1]

I'm confused.

Originally your raw data had a date column for every Line.
In post 14 you changed to having only one date column. My code was re-written to cope with that layout.
Now your latest mini-sheet shows a date column for every Line again.

What is actually going on in relation to date columns?

So sorry. My mistake...

Only one column with the date, like your code is working with

	Line 1			Line 2			Line 3
Date	Product #	TEXT	Quantity	Product #	TEXT	Quantity	Product #	TEXT	Quantity
06-09-2021	4512	Text 1	1.000	4519	Text 2	500	4512	Text 3	1.000
07-09-2021	5896	Text 1	2.000	5555	Text 2	700	5896	Text 3	2.000
08-09-2021	2478	Text 1	3.000	2589	Text 2	900	2478	Text 3	3.000
08-09-2021	3578	Text 1	4.000	3045	Text 2	1.100	b	Text 3	10
08-09-2021	1287	Text 1	5.000	1687	Text 2	1.300	1287	Text 3	5.000
09-09-2021	1287	Text 1	6.000	6222	Text 2	1.500	6999	Text 3	6.000

 

[Peter_SSs]

So sorry. My mistake...

Only one column with the date, like your code is working with

In that case, try ..

Sub CombineData_v4()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, uba2 As Long
 
  With Sheets("Sheet2")
    'Put all the data values into an array that is 10 columns wide and however many rows down.
    a = .Range("F3", .Range("F" & Rows.Count).End(xlUp)).Resize(, 10).Value
  End With
  'This is how 'wide' the array 'a' is. It will be 10. This saves re-calculating it over and over as we loop later in the code.
  uba2 = UBound(a, 2)
  'make an array plenty big enough to hold all the results (number of data rows x 3, and 4 'columns' wide)
  ReDim b(1 To 3 * UBound(a), 1 To 4)
  'Work through each row of the array ..
  For i = 1 To UBound(a)
    '.. and across each row, but in jumps of 3 because each set of data has 3 columns (+ the date)
    ' This is alo where we use the uba2 calculated earlier
    For j = 2 To uba2 Step 3
      'If a product column contains a number then
      If IsNumeric(a(i, j)) And Len(a(i, j)) > 0 Then
        'Get ready for a new results row
        k = k + 1
        'Put date in 1st col, Line no. in 2nd col, Prod# in 3rd col and Qty in 4th col
        b(k, 1) = a(i, 1): b(k, 2) = (j + 1) / 3: b(k, 3) = a(i, j): b(k, 4) = a(i, j + 2)
      End If
    'Jump across 2 cols
    Next j
  'Move down to next row
  Next i
  'Get ready to write the results in Sheet1 (k has counted how many rows of results we found)
  With Sheets("Sheet1").Range("A2:D2").Resize(k)
    'Enter the values from the results array 'b'
    .Value = b
    'Sort results 1st by Date, then by Line No. then by Quantity
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Key2:=.Columns(2), Order2:=xlAscending, Key3:=.Columns(4), Order3:=xlAscending, Header:=xlNo
    'Apply appropriate Date format to the first column
    .Columns(1).NumberFormat = "ddmmyyyy"
  End With
End Sub

 

[Lars1]

In that case, try ..

Sub CombineData_v4()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, uba2 As Long
 
  With Sheets("Sheet2")
    'Put all the data values into an array that is 10 columns wide and however many rows down.
    a = .Range("F3", .Range("F" & Rows.Count).End(xlUp)).Resize(, 10).Value
  End With
  'This is how 'wide' the array 'a' is. It will be 10. This saves re-calculating it over and over as we loop later in the code.
  uba2 = UBound(a, 2)
  'make an array plenty big enough to hold all the data (number of data rows x 3, and 4 'columns' wide)
  ReDim b(1 To 3 * UBound(a), 1 To 4)
  'Work through each row of the array ..
  For i = 1 To UBound(a)
    '.. and across each row, but in jumps of 3 because each set of data has 3 columns (+ the date)
    ' This is alo where we use the uba2 calculated earlier
    For j = 2 To uba2 Step 3
      'If a product column contains a number then
      If IsNumeric(a(i, j)) And Len(a(i, j)) > 0 Then
        'Get ready for a new results row
        k = k + 1
        'Put date in 1st col, Line no. in 2nd col, Prod# in 3rd col and Qty in 4th col
        b(k, 1) = a(i, 1): b(k, 2) = (j + 1) / 3: b(k, 3) = a(i, j): b(k, 4) = a(i, j + 2)
      End If
    'Jump across 2 cols
    Next j
  'Move down to next row
  Next i
  'Get ready to write the results in Sheet1 (k has counted how many rows of results we found)
  With Sheets("Sheet1").Range("A2:D2").Resize(k)
    'Enter the values from the results array 'b'
    .Value = b
    'Sort results 1st by Date, then by Line No. then by Quantity
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Key2:=.Columns(2), Order2:=xlAscending, Key3:=.Columns(4), Order3:=xlAscending, Header:=xlNo
    'Apply appropriate Date format to the first column
    .Columns(1).NumberFormat = "ddmmyyyy"
  End With
End Sub

Once again you have done it. THANKS Peter.

I am trying to see if i can understand what is actually going on, but i think i need some basic VBA training

My guess is that "b(k, 1)" points out the Column.
But the rest, i am not sure i can figure out

Put date in 1st col, b(k, 1) = a(i, 1):

Line no. in 2nd col, b(k, 2) = (j + 1) / 3:

Prod# in 3rd col and b(k, 3) = a(i, j):

Qty in 4th col b(k, 4) = a(i, j + 2)

 

[Peter_SSs]

As noted in the comments, b is an array to hold the results (?? rows deep and 4 columns wide)

b(k, 1) refers to row k of the array (this increments by one every time we find a valid Product # in the data) and column 1
b(k, 2) refers to row k, column 2 of the array
etc