cancel
Showing results for
Did you mean:
cancel
Showing results for
Did you mean:

SOLVED
Newbie

Pivot table values from excel inserted to a matrix

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Regular Member
(in response to sfares)

Re: Pivot table values from excel inserted to a matrix

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
Regular Member
(in response to sfares)

Re: Pivot table values from excel inserted to a matrix

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

Re: Pivot table values from excel inserted to a matrix

Thank you so much Stuart!

Regular Member
(in response to sfares)

No worries. 🙂

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!

Regular Member
(in response to sfares)

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("",hdrT)).

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)

hdrk <- concat(substr(shdr,k,1),num2str(k))

hdr

Stuart

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

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!

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.

Regular Member
(in response to sfares)

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

Announcements