Get Help

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Community
- :
- PTC Mathcad
- :
- PTC Mathcad
- :
- Pivot table values from excel inserted to a matrix

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

09-18-2014
11:43 AM

09-18-2014
11:43 AM

Hello,

please see attachment . i am manually copying and pasting each value from the pivot table into the matrix. is there an easier way to automate this process? the size of the table can be any square table meaning number of rows= number of columns.Thanks!

Solved! Go to Solution.

Labels:

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

09-18-2014
12:03 PM

09-18-2014
12:03 PM

You need to generate your header, then stack it on top of the array,

then transpose the header, stack a space on top of it, then augment it to the back of the previous array.

Stuart

10 REPLIES 10

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

09-18-2014
12:03 PM

09-18-2014
12:03 PM

You need to generate your header, then stack it on top of the array,

then transpose the header, stack a space on top of it, then augment it to the back of the previous array.

Stuart

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

09-18-2014
12:11 PM

09-18-2014
12:11 PM

Re: Pivot table values from excel inserted to a matrix

Thank you so much Stuart!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

09-18-2014
12:38 PM

09-18-2014
12:38 PM

Re: Pivot table values from excel inserted to a matrix

No worries. 🙂

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

09-18-2014
12:48 PM

09-18-2014
12:48 PM

Re: Pivot table values from excel inserted to a matrix

Stuart,

generate header function assumes i have X1,Y2, X3,Y4,X5,Y6.... but some times i might have X1,Y2,Y3,X4,Y5... in other words the numbers will be in order, but the X,Y may not be in the same order i have in the example. is there a way where i can define the header first then use it for the first row and last column in that matrix. i like what you did at the bottom. can you integrate what you did at the bottom with what i am suggesting to you. My origin in the big mathcad sheet i am working has an orign of 1. Thanks!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

09-18-2014
01:11 PM

09-18-2014
01:11 PM

Re: Pivot table values from excel inserted to a matrix

Sorry, I'm just in the process of shutting my PC down - have to go somewhere, and have closed Mathcad, ...

but from the sound of it you need replace the 'n' argument I used in the function with your hdr, then just return augment(stack(hdr,K),stack("",hdr^{T})).

If you have some way of passing the X,Y list information then that could help to create the header. Alternatively, to ease creating it, you could simply create a string of the sequence, eg shdr:="XYYXYXXY", and then loop through the string to create a label vector, eg ["X1" "Y2" "Y3" "X4" "Y5" "X6" "X7" "Y8"]. Something like

for k = 1 to strlen(shdr)

hdr_{k} <- concat(substr(shdr,k,1),num2str(k))

hdr

Stuart

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

09-19-2014
04:31 AM

09-19-2014
04:31 AM

Re: Pivot table values from excel inserted to a matrix

Just in case my hastily scribbled message wasn't sufficiently clear, have a look at the attached worksheet ...

Stuart

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

09-19-2014
09:20 AM

09-19-2014
09:20 AM

Re: Pivot table values from excel inserted to a matrix

Thank you so much Stuart. I like it

The only issue that i see is it skipped the first X in strHeader, which left the 8 w/o Y.

if you have time , could you explain to me the first two lines of the addHeader function? Thanks again!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

09-19-2014
10:08 AM

09-19-2014
10:08 AM

Re: Pivot table values from excel inserted to a matrix

sameer fares wrote:

Thank you so much Stuart. I like it

The only issue that i see is it skipped the first X in strHeader, which left the 8 w/o Y.

Go to Menu - Tools - Worksheet Options... - Calculation - Check "Use ORIGIN for string indexing"

If you don't want to do that you'll have to change the routine a bit

Best option in my opinion is to leave ORIGIN at the default 0 and just change the first line in the routine as shown above.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

09-19-2014
10:18 AM

09-19-2014
10:18 AM

Re: Pivot table values from excel inserted to a matrix

😕 I would explain them except I'm Mathcadless until Monday and don't remember what I wrote. I suspect the skipping problem is to do with the origin setting for strings ... have a look in Help. Sorry I can't be of more assistance at the moment.

Stuart