Hi,
I have several €˜sections with up to 4 products (columns), but some sections
have less than 4 products (columns) but the same products. I have hundreds of
sections. How can I align the columns so that the relevant products are all
above each other? Is there any other way to make this data user friendly for
plotting charts/pivot tables? There are hundreds of sections, any suggestions
appreciated. Sample below:

Section 1
Product A Product B Product C
03-Jan-09 150 245
12-Jan-09 280 159
16-Jan-09 366
18-Jan-09 146 86
20-Jan-09 280 99
29-Jan-09 300
30-Jan-09 280 99
Sum: 3922 1916 86
Section 2
Product A Product B Product D
07-Jan-09 189 199
12-Jan-09 169 149 59
21-Jan-09 179 150 59
28-Jan-09 169 221
Sum: 905 906 118
Section 3
Product B Product C
02-Jan-09 170 210
08-Jan-09 230 151
19-Jan-09 155 226
23-Jan-09 200 186
29-Jan-09 180 211
Sum: 1110 1195

Probably not many other ways to make it pivot table/charting friendly, so
let's just get it done?

Here is code that will read through data that's set up as you've shown here,
with the limits you provided and copy it into a separate sheet into 4 columns
as:
date ... Section ID ... Product ID ... Quantity
You'll have to format those quantities after the move to get the data to
appear as you want it to (especially column A with the dates in it).

You'll probably need to make some changes to the Const values I've provided
to get it to work with your workbook, since at least the worksheet names will
probably need to be changed. It's assumed that [Sheet2] is an empty sheet
that is available for this use. [Sheet1] is the sheet with the data in it
that needs to be reorganized.

To get the code into your workbook:
Although this code should not bother your data at all, just to be safe, I
recommend creating a copy of your workbook and using it to try all this out
with.

Open the workbook. Press [Alt]+[F11] to open the VBA Editor (VBE). In the
VBE select Insert | Module from its menu. Then cut and paste the code below
into it and make any changes to the code that you need to. Close the VBE.

Use Tools | Macro | Macros to access the macro and run it. Examine the
results.

Here is the code:

Sub DataReorganization()
'change these constants to work with
'your workbook layout and contents
Const sourceSheetName = "Sheet1" ' current data sheet
Const destSheetName = "Sheet2" ' new organized list sheet
Const dateColumn = "A" ' column with dates in it
Const sectionPhrase = "Section " ' general identifying part
Const termPhrase = "Sum:" ' id's end of section
Const firstProductColumn = "B"
Const maxProductsInSection = 4
'end of values for you to change

Dim sourceSheet As Worksheet
Dim sourceRange As Range
Dim anySourceEntry As Range
Dim destSheet As Worksheet
Dim currentSectionID As String
Dim prodRow As Long
Dim cOffset As Integer
Dim baseCell As Range
Dim FirstProdColNum As Integer

Set sourceSheet = ThisWorkbook.Worksheets(sourceSheetName)
Set sourceRange = sourceSheet.Range(dateColumn & "1:" & _
sourceSheet.Range(dateColumn & Rows.Count).End(xlUp).Address)
Set destSheet = ThisWorkbook.Worksheets(destSheetName)
FirstProdColNum = Range(firstProductColumn & 1).Column

destSheet.Cells.Clear
For Each anySourceEntry In sourceRange
If Left(anySourceEntry, Len(sectionPhrase)) = _
sectionPhrase Then
'found the start of a section
currentSectionID = anySourceEntry
'** delete next command if you don't
'want separation between sections
destSheet.Range("A" & Rows.Count).End(xlUp). _
Offset(1, 0) = currentSectionID
'**
prodRow = anySourceEntry.Row + 1
ElseIf IsDate(anySourceEntry) Then
' check columns B:E
For cOffset = FirstProdColNum To _
(FirstProdColNum + maxProductsInSection - 1)
If Not IsEmpty(sourceSheet.Cells(prodRow, _
cOffset)) Then
'has a product ID in it
'is there a quantity
If Not IsEmpty(anySourceEntry.Offset(0, _
cOffset - 1)) Then
'there is a quantity, so copy the data
Set baseCell = destSheet.Range("A" & _
Rows.Count).End(xlUp).Offset(1, 0)
'copy the date
baseCell = anySourceEntry
'enter the Section ID
baseCell.Offset(0, 1) = currentSectionID
'enter the product ID
baseCell.Offset(0, 2) = _
sourceSheet.Cells(prodRow, cOffset)
'and the quantity
baseCell.Offset(0, 3) = _
anySourceEntry.Offset(0, cOffset - 1)
End If
End If
Next
End If
Next

Set sourceRange = Nothing
Set baseCell = Nothing
Set sourceSheet = Nothing
Set destSheet = Nothing
End Sub


"aquaxander" wrote:

Hi,
I have several €˜sections with up to 4 products (columns), but some sections
have less than 4 products (columns) but the same products. I have hundreds of
sections. How can I align the columns so that the relevant products are all
above each other? Is there any other way to make this data user friendly for
plotting charts/pivot tables? There are hundreds of sections, any suggestions
appreciated. Sample below:

Section 1
Product A Product B Product C
03-Jan-09 150 245
12-Jan-09 280 159
16-Jan-09 366
18-Jan-09 146 86
20-Jan-09 280 99
29-Jan-09 300
30-Jan-09 280 99
Sum: 3922 1916 86
Section 2
Product A Product B Product D
07-Jan-09 189 199
12-Jan-09 169 149 59
21-Jan-09 179 150 59
28-Jan-09 169 221
Sum: 905 906 118
Section 3
Product B Product C
02-Jan-09 170 210
08-Jan-09 230 151
19-Jan-09 155 226
23-Jan-09 200 186
29-Jan-09 180 211
Sum: 1110 1195

Hi JLatham,
The script you have written works for the data I provided, as you said, I
need to change a couple of values, I think that the only thing that is
stopping the macro from working is the name "Section X". In my original file,
these are "different site names". I have had to change this in the example
due to confidentiality. What/where do I need to change to make this work
please?

"JLatham" wrote:

Probably not many other ways to make it pivot table/charting friendly, so
let's just get it done?

Here is code that will read through data that's set up as you've shown here,
with the limits you provided and copy it into a separate sheet into 4 columns
as:
date ... Section ID ... Product ID ... Quantity
You'll have to format those quantities after the move to get the data to
appear as you want it to (especially column A with the dates in it).

You'll probably need to make some changes to the Const values I've provided
to get it to work with your workbook, since at least the worksheet names will
probably need to be changed. It's assumed that [Sheet2] is an empty sheet
that is available for this use. [Sheet1] is the sheet with the data in it
that needs to be reorganized.

To get the code into your workbook:
Although this code should not bother your data at all, just to be safe, I
recommend creating a copy of your workbook and using it to try all this out
with.

Open the workbook. Press [Alt]+[F11] to open the VBA Editor (VBE). In the
VBE select Insert | Module from its menu. Then cut and paste the code below
into it and make any changes to the code that you need to. Close the VBE.

Use Tools | Macro | Macros to access the macro and run it. Examine the
results.

Here is the code:

Sub DataReorganization()
'change these constants to work with
'your workbook layout and contents
Const sourceSheetName = "Sheet1" ' current data sheet
Const destSheetName = "Sheet2" ' new organized list sheet
Const dateColumn = "A" ' column with dates in it
Const sectionPhrase = "Section " ' general identifying part
Const termPhrase = "Sum:" ' id's end of section
Const firstProductColumn = "B"
Const maxProductsInSection = 4
'end of values for you to change

Dim sourceSheet As Worksheet
Dim sourceRange As Range
Dim anySourceEntry As Range
Dim destSheet As Worksheet
Dim currentSectionID As String
Dim prodRow As Long
Dim cOffset As Integer
Dim baseCell As Range
Dim FirstProdColNum As Integer

Set sourceSheet = ThisWorkbook.Worksheets(sourceSheetName)
Set sourceRange = sourceSheet.Range(dateColumn & "1:" & _
sourceSheet.Range(dateColumn & Rows.Count).End(xlUp).Address)
Set destSheet = ThisWorkbook.Worksheets(destSheetName)
FirstProdColNum = Range(firstProductColumn & 1).Column

destSheet.Cells.Clear
For Each anySourceEntry In sourceRange
If Left(anySourceEntry, Len(sectionPhrase)) = _
sectionPhrase Then
'found the start of a section
currentSectionID = anySourceEntry
'** delete next command if you don't
'want separation between sections
destSheet.Range("A" & Rows.Count).End(xlUp). _
Offset(1, 0) = currentSectionID
'**
prodRow = anySourceEntry.Row + 1
ElseIf IsDate(anySourceEntry) Then
' check columns B:E
For cOffset = FirstProdColNum To _
(FirstProdColNum + maxProductsInSection - 1)
If Not IsEmpty(sourceSheet.Cells(prodRow, _
cOffset)) Then
'has a product ID in it
'is there a quantity
If Not IsEmpty(anySourceEntry.Offset(0, _
cOffset - 1)) Then
'there is a quantity, so copy the data
Set baseCell = destSheet.Range("A" & _
Rows.Count).End(xlUp).Offset(1, 0)
'copy the date
baseCell = anySourceEntry
'enter the Section ID
baseCell.Offset(0, 1) = currentSectionID
'enter the product ID
baseCell.Offset(0, 2) = _
sourceSheet.Cells(prodRow, cOffset)
'and the quantity
baseCell.Offset(0, 3) = _
anySourceEntry.Offset(0, cOffset - 1)
End If
End If
Next
End If
Next

Set sourceRange = Nothing
Set baseCell = Nothing
Set sourceSheet = Nothing
Set destSheet = Nothing
End Sub


"aquaxander" wrote:

Hi,
I have several €˜sections with up to 4 products (columns), but some sections
have less than 4 products (columns) but the same products. I have hundreds of
sections. How can I align the columns so that the relevant products are all
above each other? Is there any other way to make this data user friendly for
plotting charts/pivot tables? There are hundreds of sections, any suggestions
appreciated. Sample below:

Section 1
Product A Product B Product C
03-Jan-09 150 245
12-Jan-09 280 159
16-Jan-09 366
18-Jan-09 146 86
20-Jan-09 280 99
29-Jan-09 300
30-Jan-09 280 99
Sum: 3922 1916 86
Section 2
Product A Product B Product D
07-Jan-09 189 199
12-Jan-09 169 149 59
21-Jan-09 179 150 59
28-Jan-09 169 221
Sum: 905 906 118
Section 3
Product B Product C
02-Jan-09 170 210
08-Jan-09 230 151
19-Jan-09 155 226
23-Jan-09 200 186
29-Jan-09 180 211
Sum: 1110 1195

Would it be possible for you to go through the list and enter a 'standard'
identifying word at the beginning of each of those 'Section' indicators?
Perhaps adding (without quote marks)
"Site: "
to each? Then you could change the line of code that reads
Const sectionPhrase = "Section " ' general identifying part
to become
Const sectionPhrase = "Site: " ' general identifying part
and it should work. Otherwise we have to figure out another way to identify
where each of those sections begins at.

"aquaxander" wrote:

Hi JLatham,
The script you have written works for the data I provided, as you said, I
need to change a couple of values, I think that the only thing that is
stopping the macro from working is the name "Section X". In my original file,
these are "different site names". I have had to change this in the example
due to confidentiality. What/where do I need to change to make this work
please?

"JLatham" wrote:

Probably not many other ways to make it pivot table/charting friendly, so
let's just get it done?

Here is code that will read through data that's set up as you've shown here,
with the limits you provided and copy it into a separate sheet into 4 columns
as:
date ... Section ID ... Product ID ... Quantity
You'll have to format those quantities after the move to get the data to
appear as you want it to (especially column A with the dates in it).

You'll probably need to make some changes to the Const values I've provided
to get it to work with your workbook, since at least the worksheet names will
probably need to be changed. It's assumed that [Sheet2] is an empty sheet
that is available for this use. [Sheet1] is the sheet with the data in it
that needs to be reorganized.

To get the code into your workbook:
Although this code should not bother your data at all, just to be safe, I
recommend creating a copy of your workbook and using it to try all this out
with.

Open the workbook. Press [Alt]+[F11] to open the VBA Editor (VBE). In the
VBE select Insert | Module from its menu. Then cut and paste the code below
into it and make any changes to the code that you need to. Close the VBE.

Use Tools | Macro | Macros to access the macro and run it. Examine the
results.

Here is the code:

Sub DataReorganization()
'change these constants to work with
'your workbook layout and contents
Const sourceSheetName = "Sheet1" ' current data sheet
Const destSheetName = "Sheet2" ' new organized list sheet
Const dateColumn = "A" ' column with dates in it
Const sectionPhrase = "Section " ' general identifying part
Const termPhrase = "Sum:" ' id's end of section
Const firstProductColumn = "B"
Const maxProductsInSection = 4
'end of values for you to change

Dim sourceSheet As Worksheet
Dim sourceRange As Range
Dim anySourceEntry As Range
Dim destSheet As Worksheet
Dim currentSectionID As String
Dim prodRow As Long
Dim cOffset As Integer
Dim baseCell As Range
Dim FirstProdColNum As Integer

Set sourceSheet = ThisWorkbook.Worksheets(sourceSheetName)
Set sourceRange = sourceSheet.Range(dateColumn & "1:" & _
sourceSheet.Range(dateColumn & Rows.Count).End(xlUp).Address)
Set destSheet = ThisWorkbook.Worksheets(destSheetName)
FirstProdColNum = Range(firstProductColumn & 1).Column

destSheet.Cells.Clear
For Each anySourceEntry In sourceRange
If Left(anySourceEntry, Len(sectionPhrase)) = _
sectionPhrase Then
'found the start of a section
currentSectionID = anySourceEntry
'** delete next command if you don't
'want separation between sections
destSheet.Range("A" & Rows.Count).End(xlUp). _
Offset(1, 0) = currentSectionID
'**
prodRow = anySourceEntry.Row + 1
ElseIf IsDate(anySourceEntry) Then
' check columns B:E
For cOffset = FirstProdColNum To _
(FirstProdColNum + maxProductsInSection - 1)
If Not IsEmpty(sourceSheet.Cells(prodRow, _
cOffset)) Then
'has a product ID in it
'is there a quantity
If Not IsEmpty(anySourceEntry.Offset(0, _
cOffset - 1)) Then
'there is a quantity, so copy the data
Set baseCell = destSheet.Range("A" & _
Rows.Count).End(xlUp).Offset(1, 0)
'copy the date
baseCell = anySourceEntry
'enter the Section ID
baseCell.Offset(0, 1) = currentSectionID
'enter the product ID
baseCell.Offset(0, 2) = _
sourceSheet.Cells(prodRow, cOffset)
'and the quantity
baseCell.Offset(0, 3) = _
anySourceEntry.Offset(0, cOffset - 1)
End If
End If
Next
End If
Next

Set sourceRange = Nothing
Set baseCell = Nothing
Set sourceSheet = Nothing
Set destSheet = Nothing
End Sub


"aquaxander" wrote:

Hi,
I have several €˜sections with up to 4 products (columns), but some sections
have less than 4 products (columns) but the same products. I have hundreds of
sections. How can I align the columns so that the relevant products are all
above each other? Is there any other way to make this data user friendly for
plotting charts/pivot tables? There are hundreds of sections, any suggestions
appreciated. Sample below:

Section 1
Product A Product B Product C
03-Jan-09 150 245
12-Jan-09 280 159
16-Jan-09 366
18-Jan-09 146 86
20-Jan-09 280 99
29-Jan-09 300
30-Jan-09 280 99
Sum: 3922 1916 86
Section 2
Product A Product B Product D
07-Jan-09 189 199
12-Jan-09 169 149 59
21-Jan-09 179 150 59
28-Jan-09 169 221
Sum: 905 906 118
Section 3
Product B Product C
02-Jan-09 170 210
08-Jan-09 230 151
19-Jan-09 155 226
23-Jan-09 200 186
29-Jan-09 180 211
Sum: 1110 1195

Dear JLatham,
You are a genius. I am soooo happy that this has worked!
I put in a formula:
=IF(A2="Sum:",A2,IF(ISTEXT(A2),"Section "&A2,A2))
to rename the first column, copy, paste, etc, run macro and bingo!
I ran a fantastic pivot. (I get easily excited when things work)
THANKYOU!!!!

"JLatham" wrote:

Would it be possible for you to go through the list and enter a 'standard'
identifying word at the beginning of each of those 'Section' indicators?
Perhaps adding (without quote marks)
"Site: "
to each? Then you could change the line of code that reads
Const sectionPhrase = "Section " ' general identifying part
to become
Const sectionPhrase = "Site: " ' general identifying part
and it should work. Otherwise we have to figure out another way to identify
where each of those sections begins at.

"aquaxander" wrote:

Hi JLatham,
The script you have written works for the data I provided, as you said, I
need to change a couple of values, I think that the only thing that is
stopping the macro from working is the name "Section X". In my original file,
these are "different site names". I have had to change this in the example
due to confidentiality. What/where do I need to change to make this work
please?

"JLatham" wrote:

Probably not many other ways to make it pivot table/charting friendly, so
let's just get it done?

Here is code that will read through data that's set up as you've shown here,
with the limits you provided and copy it into a separate sheet into 4 columns
as:
date ... Section ID ... Product ID ... Quantity
You'll have to format those quantities after the move to get the data to
appear as you want it to (especially column A with the dates in it).

You'll probably need to make some changes to the Const values I've provided
to get it to work with your workbook, since at least the worksheet names will
probably need to be changed. It's assumed that [Sheet2] is an empty sheet
that is available for this use. [Sheet1] is the sheet with the data in it
that needs to be reorganized.

To get the code into your workbook:
Although this code should not bother your data at all, just to be safe, I
recommend creating a copy of your workbook and using it to try all this out
with.

Open the workbook. Press [Alt]+[F11] to open the VBA Editor (VBE). In the
VBE select Insert | Module from its menu. Then cut and paste the code below
into it and make any changes to the code that you need to. Close the VBE.

Use Tools | Macro | Macros to access the macro and run it. Examine the
results.

Here is the code:

Sub DataReorganization()
'change these constants to work with
'your workbook layout and contents
Const sourceSheetName = "Sheet1" ' current data sheet
Const destSheetName = "Sheet2" ' new organized list sheet
Const dateColumn = "A" ' column with dates in it
Const sectionPhrase = "Section " ' general identifying part
Const termPhrase = "Sum:" ' id's end of section
Const firstProductColumn = "B"
Const maxProductsInSection = 4
'end of values for you to change

Dim sourceSheet As Worksheet
Dim sourceRange As Range
Dim anySourceEntry As Range
Dim destSheet As Worksheet
Dim currentSectionID As String
Dim prodRow As Long
Dim cOffset As Integer
Dim baseCell As Range
Dim FirstProdColNum As Integer

Set sourceSheet = ThisWorkbook.Worksheets(sourceSheetName)
Set sourceRange = sourceSheet.Range(dateColumn & "1:" & _
sourceSheet.Range(dateColumn & Rows.Count).End(xlUp).Address)
Set destSheet = ThisWorkbook.Worksheets(destSheetName)
FirstProdColNum = Range(firstProductColumn & 1).Column

destSheet.Cells.Clear
For Each anySourceEntry In sourceRange
If Left(anySourceEntry, Len(sectionPhrase)) = _
sectionPhrase Then
'found the start of a section
currentSectionID = anySourceEntry
'** delete next command if you don't
'want separation between sections
destSheet.Range("A" & Rows.Count).End(xlUp). _
Offset(1, 0) = currentSectionID
'**
prodRow = anySourceEntry.Row + 1
ElseIf IsDate(anySourceEntry) Then
' check columns B:E
For cOffset = FirstProdColNum To _
(FirstProdColNum + maxProductsInSection - 1)
If Not IsEmpty(sourceSheet.Cells(prodRow, _
cOffset)) Then
'has a product ID in it
'is there a quantity
If Not IsEmpty(anySourceEntry.Offset(0, _
cOffset - 1)) Then
'there is a quantity, so copy the data
Set baseCell = destSheet.Range("A" & _
Rows.Count).End(xlUp).Offset(1, 0)
'copy the date
baseCell = anySourceEntry
'enter the Section ID
baseCell.Offset(0, 1) = currentSectionID
'enter the product ID
baseCell.Offset(0, 2) = _
sourceSheet.Cells(prodRow, cOffset)
'and the quantity
baseCell.Offset(0, 3) = _
anySourceEntry.Offset(0, cOffset - 1)
End If
End If
Next
End If
Next

Set sourceRange = Nothing
Set baseCell = Nothing
Set sourceSheet = Nothing
Set destSheet = Nothing
End Sub


"aquaxander" wrote:

Hi,
I have several €˜sections with up to 4 products (columns), but some sections
have less than 4 products (columns) but the same products. I have hundreds of
sections. How can I align the columns so that the relevant products are all
above each other? Is there any other way to make this data user friendly for
plotting charts/pivot tables? There are hundreds of sections, any suggestions
appreciated. Sample below:

Section 1
Product A Product B Product C
03-Jan-09 150 245
12-Jan-09 280 159
16-Jan-09 366
18-Jan-09 146 86
20-Jan-09 280 99
29-Jan-09 300
30-Jan-09 280 99
Sum: 3922 1916 86
Section 2
Product A Product B Product D
07-Jan-09 189 199
12-Jan-09 169 149 59
21-Jan-09 179 150 59
28-Jan-09 169 221
Sum: 905 906 118
Section 3
Product B Product C
02-Jan-09 170 210
08-Jan-09 230 151
19-Jan-09 155 226
23-Jan-09 200 186
29-Jan-09 180 211
Sum: 1110 1195

Glad to have been able to get you moving on this thing. And thanks for
letting me know it finally worked.

"aquaxander" wrote:

Dear JLatham,
You are a genius. I am soooo happy that this has worked!
I put in a formula:
=IF(A2="Sum:",A2,IF(ISTEXT(A2),"Section "&A2,A2))
to rename the first column, copy, paste, etc, run macro and bingo!
I ran a fantastic pivot. (I get easily excited when things work)
THANKYOU!!!!

"JLatham" wrote:

Would it be possible for you to go through the list and enter a 'standard'
identifying word at the beginning of each of those 'Section' indicators?
Perhaps adding (without quote marks)
"Site: "
to each? Then you could change the line of code that reads
Const sectionPhrase = "Section " ' general identifying part
to become
Const sectionPhrase = "Site: " ' general identifying part
and it should work. Otherwise we have to figure out another way to identify
where each of those sections begins at.

"aquaxander" wrote:

Hi JLatham,
The script you have written works for the data I provided, as you said, I
need to change a couple of values, I think that the only thing that is
stopping the macro from working is the name "Section X". In my original file,
these are "different site names". I have had to change this in the example
due to confidentiality. What/where do I need to change to make this work
please?

"JLatham" wrote:

Probably not many other ways to make it pivot table/charting friendly, so
let's just get it done?

Here is code that will read through data that's set up as you've shown here,
with the limits you provided and copy it into a separate sheet into 4 columns
as:
date ... Section ID ... Product ID ... Quantity
You'll have to format those quantities after the move to get the data to
appear as you want it to (especially column A with the dates in it).

You'll probably need to make some changes to the Const values I've provided
to get it to work with your workbook, since at least the worksheet names will
probably need to be changed. It's assumed that [Sheet2] is an empty sheet
that is available for this use. [Sheet1] is the sheet with the data in it
that needs to be reorganized.

To get the code into your workbook:
Although this code should not bother your data at all, just to be safe, I
recommend creating a copy of your workbook and using it to try all this out
with.

Open the workbook. Press [Alt]+[F11] to open the VBA Editor (VBE). In the
VBE select Insert | Module from its menu. Then cut and paste the code below
into it and make any changes to the code that you need to. Close the VBE.

Use Tools | Macro | Macros to access the macro and run it. Examine the
results.

Here is the code:

Sub DataReorganization()
'change these constants to work with
'your workbook layout and contents
Const sourceSheetName = "Sheet1" ' current data sheet
Const destSheetName = "Sheet2" ' new organized list sheet
Const dateColumn = "A" ' column with dates in it
Const sectionPhrase = "Section " ' general identifying part
Const termPhrase = "Sum:" ' id's end of section
Const firstProductColumn = "B"
Const maxProductsInSection = 4
'end of values for you to change

Dim sourceSheet As Worksheet
Dim sourceRange As Range
Dim anySourceEntry As Range
Dim destSheet As Worksheet
Dim currentSectionID As String
Dim prodRow As Long
Dim cOffset As Integer
Dim baseCell As Range
Dim FirstProdColNum As Integer

Set sourceSheet = ThisWorkbook.Worksheets(sourceSheetName)
Set sourceRange = sourceSheet.Range(dateColumn & "1:" & _
sourceSheet.Range(dateColumn & Rows.Count).End(xlUp).Address)
Set destSheet = ThisWorkbook.Worksheets(destSheetName)
FirstProdColNum = Range(firstProductColumn & 1).Column

destSheet.Cells.Clear
For Each anySourceEntry In sourceRange
If Left(anySourceEntry, Len(sectionPhrase)) = _
sectionPhrase Then
'found the start of a section
currentSectionID = anySourceEntry
'** delete next command if you don't
'want separation between sections
destSheet.Range("A" & Rows.Count).End(xlUp). _
Offset(1, 0) = currentSectionID
'**
prodRow = anySourceEntry.Row + 1
ElseIf IsDate(anySourceEntry) Then
' check columns B:E
For cOffset = FirstProdColNum To _
(FirstProdColNum + maxProductsInSection - 1)
If Not IsEmpty(sourceSheet.Cells(prodRow, _
cOffset)) Then
'has a product ID in it
'is there a quantity
If Not IsEmpty(anySourceEntry.Offset(0, _
cOffset - 1)) Then
'there is a quantity, so copy the data
Set baseCell = destSheet.Range("A" & _
Rows.Count).End(xlUp).Offset(1, 0)
'copy the date
baseCell = anySourceEntry
'enter the Section ID
baseCell.Offset(0, 1) = currentSectionID
'enter the product ID
baseCell.Offset(0, 2) = _
sourceSheet.Cells(prodRow, cOffset)
'and the quantity
baseCell.Offset(0, 3) = _
anySourceEntry.Offset(0, cOffset - 1)
End If
End If
Next
End If
Next

Set sourceRange = Nothing
Set baseCell = Nothing
Set sourceSheet = Nothing
Set destSheet = Nothing
End Sub


"aquaxander" wrote:

Hi,
I have several €˜sections with up to 4 products (columns), but some sections
have less than 4 products (columns) but the same products. I have hundreds of
sections. How can I align the columns so that the relevant products are all
above each other? Is there any other way to make this data user friendly for
plotting charts/pivot tables? There are hundreds of sections, any suggestions
appreciated. Sample below:

Section 1
Product A Product B Product C
03-Jan-09 150 245
12-Jan-09 280 159
16-Jan-09 366
18-Jan-09 146 86
20-Jan-09 280 99
29-Jan-09 300
30-Jan-09 280 99
Sum: 3922 1916 86
Section 2
Product A Product B Product D
07-Jan-09 189 199
12-Jan-09 169 149 59
21-Jan-09 179 150 59
28-Jan-09 169 221
Sum: 905 906 118
Section 3
Product B Product C
02-Jan-09 170 210
08-Jan-09 230 151
19-Jan-09 155 226
23-Jan-09 200 186
29-Jan-09 180 211
Sum: 1110 1195