How to "Flatten" a table?

Meredith

New Member
Joined
Oct 2, 2006
Messages
26
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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi

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

Meredith

New Member
Joined
Oct 2, 2006
Messages
26
Cust ID (like Costco) is always in A1


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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Meredith

Are there more worksheets/customers?

Which row does the data actually start on?
 

Meredith

New Member
Joined
Oct 2, 2006
Messages
26

ADVERTISEMENT

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

New Member
Joined
Oct 2, 2006
Messages
26
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Oct 2, 2006
Messages
26
There are about 200 products with subtotals.


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

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
Let's see if this works.
You need to adjust "h_r"

Code:
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
 

Forum statistics

Threads
1,137,367
Messages
5,681,076
Members
419,950
Latest member
BeckiJae

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top