Swap X and Y axis in Scatter w/ many series.

[KcoonsThur]

Hi,

Please see attached spreadsheet.

I want to swap my X axis with my Y axis in a scatter plot but I have many series. I know I can make the change in 'Select Data' and edit data and swap the axes individually but this becomes an issue when my series number increases. I will need to make this change on thousands of series because we have a large dataset.

I am charting Hammer Blow Counts vs. Depth below ground. The depth below ground runs along the X axis which doesn't make sense visually when interpreting this data. Let me know if you have any questions.

Regards,

K

[MrShorty]

The only way I know of to do this is to go through each data series and switch the xvalues and values ranges. If you don't want to do it manually, then you can set up a VBA macro that will loop through each member of the SeriesCollection collection and change the xvalues and values properties of each series appropriately. Are you allowed to use a VBA macro for this?

https://docs.microsoft.com/en-us/off...next-statement
https://docs.microsoft.com/en-us/off...riescollection

[KcoonsThur]

Yeah, the only thing restricting me from using a macro is my excel/coding abilities. I took C++ in Uni so I'm familiar with very basic coding.

[MrShorty]

This isn't the kind of programming I do, so I had to try a couple of different things. Basically, I:
1) Used a For Each..Next loop to go through each series in the series collection (I assume you learned some loops in C++). https://docs.microsoft.com/en-us/off...next-statement
2) Read the text of the SERIES() formula as a text string and used the Split() function to split into parts. https://docs.microsoft.com/en-us/off...series.formula
https://docs.microsoft.com/en-us/off...split-function
3) Switched the xvalues and values parts of the text array and joined into a new series formula text using the Join() function https://docs.microsoft.com/en-us/off.../join-function
4) Assigned the new formula text to the .Formula property

Please Login or Register  to view this content.

The Stop statements are for debugging purposes and will allow you to see what is happening as the code runs. I did not include any error trapping or variable declaration or anything. Something like that should do the job.

[KcoonsThur]

** I might have figure it out. Ignore below for now.

Thanks for looking into this. I tried to just run the VBA as you attached it and it just made a straight line. Do i need to specifically add my series formula?

I found this macro below that was created for an add-in that supposedly does what I need. When I run it without changes it makes a straight line. When you reference srs.Formula do i need to replace that with =series(blah,blah,blah,blah). What is the code below asking for? The ' ' signify notes if i remember correctly. Are they wanting me to enter my information there or in the srs.Formula?

Please Login or Register  to view this content.

[MrShorty]

Thanks for looking into this. I tried to just run the VBA as you attached it and it just made a straight line. Do i need to specifically add my series formula?

It did not for me. It looked like a perfect "switch" of the x and y axes.

What did you see in debug mode? Did you step through it to see what it was doing that ended up with a straight line? What did you expect it to do at that point?

[KcoonsThur]

Thanks McShorty.

The issues were the author of the chart selected some text as part of the data series and I am an idiot with coding. I adjusted it and it runs perfectly.

Thanks a bunch man, you saved me a tonne of time!