Join or sign in Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below. Use Your Email Use FacebookUse Linkedin

Excel horiz to vertical w/2 col repeated

Locked

I currently output an Access query to an Excel spreadsheet comprised of rows of two distinct data elements followed by seven repeating sets of three:

Resource ID|Project ID|Monday Date\Monday Hrs|Monday Cost|Tuesday Date\Tuesday Hrs|Tuesday Cost|Wenesday Date\Wednesday Hrs|Wednesday Cost|Thursday Date\Thursday Hrs|Thursday Cost|Friday Date\Friday Hrs|Friday Cost|Saturday Date\Saturday Hrs|Saturday Cost|Sunday Date\Sunday Hrs|Sunday Cost|

I need to reconfigure it to more “vertical” rows asfollows:

Resource ID|Project ID\Date|Hrs|Cost
Resource ID|Project ID\Date|Hrs|Cost
Resource ID|Project ID\Date|Hrs|Cost

Topic

Excel horiz to vertical w/2 col repeated

In reply to Excel horiz to vertical w/2 col repeated

The simplest way to achieve this is to re-write your query, I get the impression that you have a flat table something like

[resourceid],[projectid], Monday Date, Monday Hrs, Monday Cost etc.

If this is the case then you will need a UNION Query along the lines of

SELECT ResourceID,ProjectID,MondayDate as RptDate,MondayHrs as Hrs,MondayCost as Cost UNION
SELECT ResourceID,ProjectID,TuesdayDate as RptDate,TuesdayHrs as Hrs,TuesdayCost as Cost
UNION (do the same for Wednesday etc.)
If you don’t have a flat table as I described above then a simple join query (See the access online help) will suffice

Excel horiz to vertical w/2 col repeated

In reply to Excel horiz to vertical w/2 col repeated

I cannot rewrite the query. I am getting this data out of a Blob through the vendor’s query facility which is proprietary and which does not allow me to query based on the data elements.

Excel horiz to vertical w/2 col repeated

In reply to Excel horiz to vertical w/2 col repeated

If the resulting spreadsheet has each of the data elements in a separate cell then

1) you can link the spreadsheet into a .mdb file as a table and run a union query against the linked table as I described before.

or

2) you can manually create a mask worksheet that uses links to transform the existing structure to the required structure (only do this if the number of rows in your result set is constant)

or

3) Write some VBA code to scan through the rows and either build an array of items to be written out once the scan is complete or write them out to another sheet during the scan. the VBA code would look something like

rowidx = 1
with worksheets(“Sheet1”)
while not isempty(.cells(rowidx,1).value)
‘get monday’s date
mondaydate = .cells(rowidx,3).value
‘ do something with monday’s date here etc.
‘ then move onto the next row
rowidx = rowidx + 1
wend

If the data elements are not in separate cells then you need to scan through the cells (as in 3) above)and parse the contents of the cell to extract the data elements

Hope this is more helpful

Excel horiz to vertical w/2 col repeated

In reply to Excel horiz to vertical w/2 col repeated

Thank you very much,

Charlie

Excel horiz to vertical w/2 col repeated

In reply to Excel horiz to vertical w/2 col repeated

This question was closed by the author

[Author]

Replies