Pivot table values from excel inserted to a matrix

[SFares]

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!

[StuartBruff · Accepted solution]

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

[SFares]

Thank you so much Stuart!

[StuartBruff]

No worries. 🙂

[SFares]

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!

[StuartBruff]

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

[StuartBruff]

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

Stuart

[SFares]

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!

[Werner_E]

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.

[StuartBruff]

Ah, I see you've beaten me to the punch, Werner 🙂

And the image helps to explain the lines ... The first two lines iterate over the header string one character at a time (the substr function), convert the loop index number to a string, concatenate the 'letter' and the 'number' together, and then assign the result to a vector. The other difference of particular note from the original function is that I've created a standard column vector rather then a row vector, simply because it's slightly easier to work with. Hope that helps.

Stuart

[StuartBruff]

😕 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