Tranpose Excel worksheet to Access

Hi. Im asking for a friend.

She needs to transpose this to vertical based in access such that it will be

tranpose.png
MPA        PlanningPartGroup                       Platform        Dates Values

FLEX-PTP      SPT MID DW M SFP GROUP      LIMO PRO     8/21    9
FLEX-PTP      SPT MID DW M SFP GROUP      LIMO PRO     8/28   14
FLEX-PTP      SPT MID DW M SFP GROUP      LIMO PRO     9/4     12

Is it possible to be done in access?
Or if not, is it better to transpose it in Excel and then import?

Thanks
Amir RAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
Like I said to import from the POR Plan sheet the data would need to be 'unpivoted' and you can do that using PowerQuery/Get and Transform.

Select the entire range of the pivot table from the headers in row 13 down and across and then goto Data>From Table/Range...

That should load the data into PowerQuery/Get and Transform where you can unpivot by selecting all the date columns and going to Unpivot Columns.

That should transpose the data into the format you want.
0
 
NorieVBA ExpertCommented:
What format is the data source for the pivot table in?
0
 
Amir RAuthor Commented:
Data source points to a table in anther worksheet
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
NorieVBA ExpertCommented:
Is the table that's the data source not in a more suitable format to import into Access?
0
 
Amir RAuthor Commented:
When i tried importing to Access,

Untitled.pngSince the tables arent perfectly formatted, it isnt perfect with the gaps and etc
0
 
NorieVBA ExpertCommented:
The data on the POR Plan sheet could be manipulated to get it into a suitable format for importing to Access.

In fact what you would do is 'unpivot' the data, that's why I'm asking about the format of the source table.

If the source table is in a suitable format it could miss that step out and import directly from the source table into Access.
0
 
Amir RAuthor Commented:
When tried exporting the source table to Access, the Access program closes.
Maybe its due to the large number of data?
0
 
Amir RAuthor Commented:
Upon clicking the Data>From Table/Range.. i was prompted to create a table and once i pressed okay and it gave me with a message box that said "A table cannot overlap a range that contains pivot table report,query..."

Am i doing it the right way to unpivot it?

https://www.excel-university.com/unpivot-excel-data/
I was trying to follow this too but i wasnt able to point up the Query editor.

Thanks
0
 
NorieVBA ExpertCommented:
Try copying the entire pivot table, as values, to a blank sheet and then following the steps in the link.

PS You should be able to import the table data source into Access, importing via the pivot table is adding an unnecessary step.
0
 
Dale FyeCommented:
This message: "A table cannot overlap a range that contains pivot table report,query..."
implies that in order to get the data into that format it has already been pivoted.  Where is the original data, (pre-pivot)?  That is the table you need to link into Access.

However to give you an idea how to address this, if you had data in an Excel spreadsheet, which could be linked to Access in a format similar to:

MPA    Planning Group    Platform            8/21/17         8/28/17             9/4/17
abc      something             xyz                              9                  14                      12

The way to "transpose" this in Access is create a union query, or a series of append queries to append data into a new table.  The syntax of the union query would look like:
SELECT MPA, Planning Group, Platform, #8/21/17# as WeekEnding, [8/21/17] as AMOUNT
FROM yourTable
WHERE [8/21/17] IS NOT NULL
UNION ALL
SELECT MPA, Planning Group, Platform, #8/28/17# as WeekEnding, [8/28/17] as AMOUNT
FROM yourTable
WHERE [8/28/17] IS NOT NULL
UNION ALL
SELECT MPA, Planning Group, Platform, #9/4/17# as WeekEnding, [9/4/17] as AMOUNT
FROM yourTable
WHERE [9/4/17] IS NOT NULL

Open in new window

If there are lots of these date columns, the 2nd option, creating a destination table, and then creating a loop in a VBA procedure to loop through the date columns and create an append query, would be a better option.

But your best option is to figure out where the source data of that pivot table is from, and simply link to that data, rather than the pivot table.
1
 
NorieVBA ExpertCommented:
Dale

I've already mentioned working with the source data here but apparently there was some problem importing to Access.
0
 
Dale FyeCommented:
Norie,

Yep, saw that, and was reinforcing that recommendation.

But also providing the description of how to actually perform a transpose of data within Access.
0
 
Ryan ChongCommented:
you probably can write a macro to copy the cells content from row 13 onward into a temporary worksheet and use it for the importing.

it depends on whether you want to do the data transpose in Access or Excel, but do it in Access it could be faster.
0
 
Amir RAuthor Commented:
Thanks for the comments!

"Where is the original data, (pre-pivot)?  That is the table you need to link into Access."
- I tried linking but since my original table contains more than 255 fields it got cut off.

So I was trying to transpose it in Excel
0
 
NorieVBA ExpertCommented:
Have you tried creating another pivot table, one that doesn't have the extra rows at the top?

By the way, when importing to Access you should get an option to ignore a specified no of rows at the top.

Did you not get that option?
0
 
Amir RAuthor Commented:
I did not get that option to ignore a specified number of rows.
The only option i got was to make the first row as headings.

And After importing the table to Access, how do i go about transposing it since Access does not have a Record macro option?

Cause i thought it will be easier in Excel since i can record macro to paste special(Transpose)
0
 
Dale FyeCommented:
Lets go back to the original data question.

You indicated that the original data contains more than 255 columns?  Is this "original data" in an Excel spreadsheet, or in an external database (SQL Server, Oracle, MySQL, ...)?

Normally well formatted data will not have that many columns, report (and pivot tables) might, but not raw data.  Figure out where the actual data is coming from and use it.

You can use the Access TransferSpreadsheet method to import data from a spreadsheet into an Access table, and this command allows you to specify a Named Range or even the specific cell range Sheet1!A13:Z500  (although I don't remember the exact syntax for that).  This might be your best option for importing the pivot table to Access.
0
 
NorieVBA ExpertCommented:
Amir

How exactly is the source data formatted?

Could you upload a sample workbook?
0
 
Amir RAuthor Commented:
It gave me the "original data contains more than 255 columns and Excel will only upload the first 255" message box.
But it uploaded fine. I think it was my mistake.

Thanks

And this is how the original data looks like original-data.png
0
 
Dale FyeCommented:
Amir,

Don't forget to close out your question and select the solution which resolved your problem.
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.

All Courses

From novice to tech pro — start learning today.