How to transpose values from vertical cells to horizontal columns/cells

[nature718]

Hi guys -



I am trying to create a macro or query where the data from one template is automatically transposed into another template for a data import.

One template has data that is listed vertically in two columns; however, I need this to be transposed horizontally so that our system can accept it as part of a data import. I have attached a screenshot for your reference.

Can someone help me automate this process so that I can click on a macro or query to automatically transpose that data to the final horizontal template? Any insight would be very much appreciated.

Thanks in advance!

[June7]

Your question is quite broad in scope. Automating can get quite complicated. Cannot provide specifics because don't know your system.

Import from where? Excel has transpose functionality.

A CROSSTAB query requires 3 fields.

[nature718]

Hey June, thanks so much for the prompt response.

I have two separate excel templates: the source one with the data vertically layered, and the target excel template with a horizontal structure. Our system will only take in the latter template, so I need all files to be in this horizontal format. So I want a query that will take this:

Topic	Systems
Subject Area	Business
Business Unit	Legal
System Required?	No
How many users?	100
Deadline?	12/12/2000

And change it into this:

Topic	Subject Area	Business Unit	System Required?	How many users?	Deadline?
Systems	Business	Legal	No	100	12/12/2000

I can manually do this in Excel, but just looking for a query that will automate this. Hope this makes sense now. Thanks again.

[June7]

A CROSSTAB can transpose if there are 3 fields or some way to calculate a third field for unique row ID value. You show only one record so not sure if that is possible. In fact, I don't see any identifier that indicates these 6 rows should be considered a single record. So not sure how even Excel can transpose multiple sets to records.

Otherwise, use VBA opening and manipulating recordset objects in looping structure to write records to table.

[June7]

Actually, without this ID it will be difficult to use a recordset because can't sort records to keep rows of block together. So maybe have to use Excel automation to pull data from worksheet or read from text file.

Another thread with same issue https://www.accessforums.net/showthread.php?t=73700

[JSR]

What does your data look like if you have five of them? Five columns? Or is it the same but the same rows and repeated over and over?

If you're starting with Excel and ending with Excel, I think it would be easier just to stick with Excel (copy, paste special transpose). But Access can do this, though it would require VBA to go through each row and populate the correct field of the destination table. Queries work fine if the data is correctly arranged to begin with, which yours isn't. If you want to do that, post a screenshot of what the data looks like when you have several of them and I will see what can be done.

[June7]

See suggested solution in other thread.