How to "Flatten" a table?

[Meredith]

I have sales guys inputting projections by customer, product, and month

Their input pages it looks something like this:



COSTCO

Jan Feb Mar Apr
VEGETABLE SOUP 20 30
CHICKEN SOUP 40 50
Total Soup 20 70 50

ORANGE JUICE 10 10 10 10
GRAPE JUICE 20
APPLE JUICE 20
Total Juice 30 10 30 10



What I want is to get a flat table out of this that looks more like this:

Customer Product Month Sales
COSTCO VEG SOUP Jan 20
COSTCO VEG SOUP Feb 30
COSTCO CHX SOUP Feb 40
COSTCO CHX SOUP Mar 50


So I can then get it in an access database and slice and dice it however I want.

any ideas that are not too cumbersome ( as of right now there are about 200 products and 100 customers... and we project out 18 months.

 

[jindon]

Hi

We need th Cell addresses for "COSTOCO" and "VEGETABLE SOUP"

 

[Meredith]

Cust ID (like Costco) is always in A1


Product IDs (like VEGSOUP ) are in column A and data is in columns D-O

 

[Norie]

Meredith

Are there more worksheets/customers?

Which row does the data actually start on?

 

[Meredith]

There is a separate tab for each customer.

But for now, let's just pretend only one customer exists. I only mention that there are several customers because I want this to be fairly automatic once it is set up, so I don't have to do the same thing 100 times every month that we update our forecast.


I will end up linking each flat customer sheet to an access table then run a query in access combining all the tables.


Data starts in row 6.

Also, there are totals in the input page, but there is no product code in those rows.

 

[Meredith]

There is a separate tab for each customer.

But for now, let's just pretend only one customer exists. I only mention that there are several customers because I want this to be fairly automatic once it is set up, so I don't have to do the same thing 100 times every month that we update our forecast.


I will end up linking each flat customer sheet to an access table then run a query in access combining all the tables.


Data starts in row 6.

Also, there are totals in the input page, but there is no product code in those rows.

 

[Norie]

Meredith

Sorry for all the questions, but I'm just trying to get my head round the data structure before trying any code.

You have 2 sets of data in your sample, one for soup and one for juice.

Is there any consistency in how these sets are seperated?

And would I be right in assuming there could be more/less data sets?

 

[Meredith]

There are about 200 products with subtotals.


But the lines with subtotals have nothing in column A (where the Product Codes live)

 

[jindon]

Hi
Let's see if this works.
You need to adjust "h_r"

Sub test()
Dim a, b(), i As Long, ii As Integer, n As Long, h_r As Long, ws As Worksheet
h_r = 2  ' <- heading row that has "Jan","Feb","Mar".....
With Activesheet
   n = WorksheetFunction.Count(.Range("m:o"))
   ReDim b(1 To n, 1 To 4) : n = 0
   a = Intersect(.Range("a:o"),.UsedRange).Value
End With
For i = h_r + 1 To UBound(a,1)
   If Not IsEmpty(a(i,1)) Then
      n = n + 1
      b(n,1) = a(1,1)
      For ii = 4 To 15
         If Not IsEmpty(a(i,ii)) Then
            b(n,2) = a(i,ii) : b(n,3) = a(h_r,ii) : b(n,4) = a(i,ii)
            Exit For
         End If
      Next
   End If
Next
On Error Resume Next
Sheets("testresults").Delete
On Error GoTo 0
Set ws = Sheets.Add.Name = "testresults"
ws.Range("a1").Resize(n,3).Value = b
Set ws = Nothing
End Sub