Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Query on MS Excel Rows and Columns

Posted on 2011-10-31
Medium Priority
332 Views
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
Question by:MCaliebe
• 6
• 4

LVL 35

Expert Comment

ID: 37058870
How can there be a difference?

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

Author Comment

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

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

LVL 35

Accepted Solution

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")

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
``````

0

Author Closing Comment

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

ID: 37059411

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

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

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

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

ID: 37062776
Excellent!  Thank you.
0

## Featured Post

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
Course of the Month21 days, 7 hours left to enroll