Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query on MS Excel Rows and Columns

Posted on 2011-10-31
10
Medium Priority
?
332 Views
Last Modified: 2012-05-12
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
Comment
Question by:MCaliebe
  • 6
  • 4
10 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 37058870
How can there be a difference?

Isn't there only 1 value for each part number/date period match?
0
 

Author Comment

by:MCaliebe
ID: 37058950
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
 

Author Comment

by:MCaliebe
ID: 37058957
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 35

Accepted Solution

by:
Norie earned 500 total points
ID: 37059130
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
 

Author Closing Comment

by:MCaliebe
ID: 37059220
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
 

Author Comment

by:MCaliebe
ID: 37059411
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
 
LVL 35

Expert Comment

by:Norie
ID: 37059823
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
 

Author Comment

by:MCaliebe
ID: 37059853
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
 
LVL 35

Expert Comment

by:Norie
ID: 37059894
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
 

Author Comment

by:MCaliebe
ID: 37062776
Excellent!  Thank you.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

804 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question