• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

Query on MS Excel Rows and Columns

I didn't really know how to title this quiestion so I am hoping someone stumbles across it and can assist.

What I have is an excel spread sheet.
Column Heading is a date period, i.e. 10/1/11, 10/8/11, 10/15/11...ect
Rows are Item numbers ie. part# 12345, 34685, 58647, ect
and the field is usage quantity.

Can someone suggest how I import this into Access and use this informatioin to query for differences in the field numbers where part number and date period match?

I'm just drawing a blank as to how I might do this.

Thanks,

MC
0
MCaliebe
Asked:
MCaliebe
  • 6
  • 4
1 Solution
 
NorieVBA ExpertCommented:
How can there be a difference?

Isn't there only 1 value for each part number/date period match?
0
 
MCaliebeAuthor Commented:
That is true, however what I have is a rolling forecast.  I attached a sample.

Each month, I receive an updated file in which the forecast values may change for the same period.

So, in September, (ROW) Part# 12345, (COLUMN) Nov 1, 2011, Field value is 50

Now in October I get the same file with updated forecast numbers for the same period

(ROW) Part# 12345, (COLUMN) Nov 1, 2011, Field value is 72

I don't know how to tell access to import the data as

Table: September
Fields: Part Number, Date, Forecast Value

Then I'd do the same thing for October's numbers
Table: September
Fields: Part Number, Date, Forecast Value

Am I making any sense?
Sample.xls
0
 
MCaliebeAuthor Commented:
Correction:

I don't know how to tell access to import the data as

Table: September
Fields: Part Number, Date, Forecast Value

Then I'd do the same thing for October's numbers
Table: October
Fields: Part Number, Date, Forecast Value

Am I making any sense?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
NorieVBA ExpertCommented:
Access could import the data in it's current format but to get it into the 3 column format would need a lot of work.

It would be easier to deal with the format in Excel.

This code will create a new sheet with the data in 2 columns that can then be imported to Access.
Option Explicit

Sub TransposeData()
Dim wsData As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim LastRowData As Long
Dim NoColsData As Long
Dim LastRowNew As Long
Dim I As Long

    Set wsData = Worksheets("Sheet1")
    Set wsNew = Sheets.Add

    wsNew.Range("A1:C1") = Array("PartNo", "Date", "ForecastValue", "")

    LastRowData = wsData.Range("A" & Rows.Count).End(xlUp).Row
    NoColsData = wsData.Range("IV1").End(xlToLeft).Column

    LastRowNew = 2

    For I = 2 To LastRowData

        Set rng = wsData.Range("A" & I)

        rng.Copy wsNew.Range("A" & LastRowNew).Resize(NoColsData - 1)

        wsData.Range("B1").Resize(, NoColsData - 1).Copy

        wsNew.Range("B" & LastRowNew).PasteSpecial Transpose:=True

        rng.Offset(, 1).Resize(, NoColsData - 1).Copy
        wsNew.Range("C" & LastRowNew).PasteSpecial Transpose:=True

        LastRowNew = LastRowNew + NoColsData - 1

    Next I

End Sub

Open in new window



0
 
MCaliebeAuthor Commented:
I agree.  This is much easier to work with.  I thought that this might have to be reformated prior to import, but I had no idea where to begin.  Excellent work.
0
 
MCaliebeAuthor Commented:
I'd like to ask a follow up question.

My original data has some garbage in row 1 and in column 1, and extends for 73 columns but the last piece of data I need is in column 31.

If I wanted to modify the code to start pulling data from B3 and stop at the last column -32, how would I modify the code?

Thanks again,

MC
0
 
NorieVBA ExpertCommented:
So the part numbers are in column B and the dates in row 2 starting in C2?

Also, the data is in fixed no of columns going from C to AE starting in row 3?
0
 
MCaliebeAuthor Commented:
I was just trying to develop a macro to delete the extra data, however yes...

In the original raw file, the part numbers are in column B, and the date range starts from F2 (I was mistaken say "C" earlier through BE2, however the file actually has data through column BU.

The  ForecastValue data field is F3 through BE499, however  even though the columns are fixed, the rows may grow.

It looks like your code searches out the last Row and the last Column so the number of rows and columns isn't very applicable.
0
 
NorieVBA ExpertCommented:
If rows are being added then finding the last row is probably a good idea.

If you've got a fixed no of columns it actually makes things a little easier as you don't need to find the last column - you know it already.

I mocked something up but all data only goes out to about AZ and the code only goes to AE.

Here it is anyway.
Sample.xls
0
 
MCaliebeAuthor Commented:
Excellent!  Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now