Table Inside Out

Harryooo

New Member
Joined
Sep 25, 2008
Messages
6
I have a table of values that are computed using input values from the first column (y's) and first row (x's). Each value in the table (z's) has a unique set of x and y.

In order to plot z vs x and z vs y I need to create a list of unique x,z and another of y,z. My question is how most efficiently to thus turn this table "inside out." The table will be very large, or I would do it by hand.

Excel
Excel
 
Last edited:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Here's the html of the sheet
Excel Workbook
ABCDE
1TABLE
21357
325
441719
58657389113
616257265281305
7321025103310491073
8
9EXTRACTED LIST
10521
111741
121943
136581
147383
158985
1611387
17257161
18265163
19281165
20305167
211025321
221033323
231049325
241073327
2523
2625
2727
2845
2947
Sheet1
 
Upvote 0
:stickouttounge:
I have a table of values that are computed using input values from the first column (y's) and first row (x's). Each value in the table (z's) has a unique set of x and y.

In order to plot z vs x and z vs y I need to create a list of unique x,z and another of y,z. My question is how most efficiently to thus turn this table "inside out." The table will be very large, or I would do it by hand.

Excel
Excel
 
Upvote 0
I finally understand what it is you want to do but don't know how to achieve it.

You want to extract the numbers from your table in an order from left to right, omitting blanks and going to the next row. Then you want to show its row & column number.

Here's an illustration I just made:

n65fld.jpg
 
Upvote 0
Thanks for helping with the clarification. Actually, The order could be by columns instead of rows. Hope someone can solve it.
Harold
 
Upvote 0
What pattern do your numbers follow? Is it y<sub>1</sub>*y<sub>1</sub>+x<sub>1</sub>?<script>(function () { var ytLoop = false; var ytPlayList; var ytPLIndex; loopy = document.createElement("div"); loopy.id = "eLoopy"; a = document.createElement("label"); a.id = "eOnOff"; a.innerHTML = "Loop"; a.title = "Enable auto replay"; a.setAttribute("onClick", "LoopyOnOff(); return false;"); a.setAttribute("class", "LoopyOff"); if (window.location.href.toLowerCase().indexOf("feature=playlist") > 0) { a.innerHTML = "Loop PlayList"; urlArgs = window.location.href.slice(window.location.href.indexOf("?") + 1).split("&"); for (var i = 0; i < urlArgs.length; i++) { arg = urlArgs.split("="); if (arg[0].toLowerCase() == "p") { ytPlayList = arg[1]; } else if (arg[0].toLowerCase() == "index") { ytPLIndex = parseInt(arg[1]) + 1; } } if (ytPlayList == getCookie("LoopyPL")) { a.title = "Disable auto replay"; a.setAttribute("class", "LoopyOn"); ytLoop = true; } } loopy.appendChild(a); window.setTimeout(function () {initLoopy(true);}, 500); window.setTimeout(function () {initLoopy(false);}, 1500); window.setTimeout(function () {initLoopy(false);}, 3500); function initLoopy(addElement) { if (addElement) { document.getElementById("watch-player-div").appendChild(loopy); } ytPlayer = document.getElementById("movie_player"); ytPlayer.addEventListener("onStateChange", "onPlayerStateChange"); } onPlayerStateChange = function (newState) {if (ytLoop && newState == "0") {if (typeof ytPlayList != "undefined") {if (ytPLIndex == document.getElementById("playlistVideoCount_PL").innerHTML) {var url = document.getElementById("playlistRow_PL_0").getElementsByTagName("a")[0].href + "&playnext=1";window.setTimeout(function () {window.location = url;}, 60);}} else {window.setTimeout(function () {ytPlayer.playVideo();}, 60);}}}; LoopyOnOff = function () {if (ytLoop) {if (typeof ytPlayList != "undefined") {setCookie("LoopyPL", null);}document.getElementById("eOnOff").title = "Enable auto replay";document.getElementById("eOnOff").setAttribute("class", "LoopyOff");ytLoop = false;} else {if (typeof ytPlayList != "undefined") {setCookie("LoopyPL", ytPlayList);}document.getElementById("eOnOff").title = "Disable auto replay";document.getElementById("eOnOff").setAttribute("class", "LoopyOn");ytLoop = true;}}; function getCookie(name) { var results = document.cookie.match("(^|;) ?" + name + "=([^;]*)(;|$)"); if (results) { return unescape(results[2]); } else { return null; } } function setCookie(name, value) { document.cookie = name + "=" + escape(value); } if (typeof GM_addStyle == "undefined") { GM_addStyle = function (text) {var head = document.getElementsByTagName("head")[0];var style = document.createElement("style");style.setAttribute("type", "text/css");style.textContent = text;head.appendChild(style);}; } GM_addStyle("\t\t\t\t\t\t\t\t#eLoopy {\t\t\t\t\t\t\t\twidth: 28px;\t\t\t\t\t\t\tmargin-left: auto;\t\t\t\t\t\ttext-align: center;\t\t\t\t\t\tbackground: #EFEFEF;\t\t\t\t\t\tborder-left: #B1B1B1 1px solid;\t\t\t\t\tborder-right: #B1B1B1 1px solid;\t\t\t\tborder-bottom: #B1B1B1 1px solid;\t\t\t\tpadding: 1px 4px 1px 4px;\t\t\t\t\tmargin-bottom: 5px; }\t\t\t\t\t#eOnOff {\t\t\t\t\t\t\t\tfont-weight: bold;\t\t\t\t\t\ttext-decoration: none;\t\t\t \t\t\t-moz-user-select: none;\t\t\t \t\t\t-khtml-user-select: none;\t\t \t\t\tuser-select: none; }\t\t\t\t\t.LoopyOff {\t\t\t\t\t\t\t\tcolor: grey !important; }\t\t\t\t.LoopyOff:hover {\t\t\t\t\t\t\tcolor: black !important; }\t\t\t\t.LoopyOn {\t\t\t\t\t\t\t\tcolor: crimson !important; }"); })()</script><script>(function () { var ytLoop = false; var ytPlayList; var ytPLIndex; loopy = document.createElement("div"); loopy.id = "eLoopy"; a = document.createElement("label"); a.id = "eOnOff"; a.innerHTML = "Loop"; a.title = "Enable auto replay"; a.setAttribute("onClick", "LoopyOnOff(); return false;"); a.setAttribute("class", "LoopyOff"); if (window.location.href.toLowerCase().indexOf("feature=playlist") > 0) { a.innerHTML = "Loop PlayList"; urlArgs = window.location.href.slice(window.location.href.indexOf("?") + 1).split("&"); for (var i = 0; i < urlArgs.length; i++) { arg = urlArgs.split("="); if (arg[0].toLowerCase() == "p") { ytPlayList = arg[1]; } else if (arg[0].toLowerCase() == "index") { ytPLIndex = parseInt(arg[1]) + 1; } } if (ytPlayList == getCookie("LoopyPL")) { a.title = "Disable auto replay"; a.setAttribute("class", "LoopyOn"); ytLoop = true; } } loopy.appendChild(a); window.setTimeout(function () {initLoopy(true);}, 500); window.setTimeout(function () {initLoopy(false);}, 1500); window.setTimeout(function () {initLoopy(false);}, 3500); function initLoopy(addElement) { if (addElement) { document.getElementById("watch-player-div").appendChild(loopy); } ytPlayer = document.getElementById("movie_player"); ytPlayer.addEventListener("onStateChange", "onPlayerStateChange"); } onPlayerStateChange = function (newState) {if (ytLoop && newState == "0") {if (typeof ytPlayList != "undefined") {if (ytPLIndex == document.getElementById("playlistVideoCount_PL").innerHTML) {var url = document.getElementById("playlistRow_PL_0").getElementsByTagName("a")[0].href + "&playnext=1";window.setTimeout(function () {window.location = url;}, 60);}} else {window.setTimeout(function () {ytPlayer.playVideo();}, 60);}}}; LoopyOnOff = function () {if (ytLoop) {if (typeof ytPlayList != "undefined") {setCookie("LoopyPL", null);}document.getElementById("eOnOff").title = "Enable auto replay";document.getElementById("eOnOff").setAttribute("class", "LoopyOff");ytLoop = false;} else {if (typeof ytPlayList != "undefined") {setCookie("LoopyPL", ytPlayList);}document.getElementById("eOnOff").title = "Disable auto replay";document.getElementById("eOnOff").setAttribute("class", "LoopyOn");ytLoop = true;}}; function getCookie(name) { var results = document.cookie.match("(^|;) ?" + name + "=([^;]*)(;|$)"); if (results) { return unescape(results[2]); } else { return null; } } function setCookie(name, value) { document.cookie = name + "=" + escape(value); } if (typeof GM_addStyle == "undefined") { GM_addStyle = function (text) {var head = document.getElementsByTagName("head")[0];var style = document.createElement("style");style.setAttribute("type", "text/css");style.textContent = text;head.appendChild(style);}; } GM_addStyle("\t\t\t\t\t\t\t\t#eLoopy {\t\t\t\t\t\t\t\twidth: 28px;\t\t\t\t\t\t\tmargin-left: auto;\t\t\t\t\t\ttext-align: center;\t\t\t\t\t\tbackground: #EFEFEF;\t\t\t\t\t\tborder-left: #B1B1B1 1px solid;\t\t\t\t\tborder-right: #B1B1B1 1px solid;\t\t\t\tborder-bottom: #B1B1B1 1px solid;\t\t\t\tpadding: 1px 4px 1px 4px;\t\t\t\t\tmargin-bottom: 5px; }\t\t\t\t\t#eOnOff {\t\t\t\t\t\t\t\tfont-weight: bold;\t\t\t\t\t\ttext-decoration: none;\t\t\t \t\t\t-moz-user-select: none;\t\t\t \t\t\t-khtml-user-select: none;\t\t \t\t\tuser-select: none; }\t\t\t\t\t.LoopyOff {\t\t\t\t\t\t\t\tcolor: grey !important; }\t\t\t\t.LoopyOff:hover {\t\t\t\t\t\t\tcolor: black !important; }\t\t\t\t.LoopyOn {\t\t\t\t\t\t\t\tcolor: crimson !important; }"); })()</script>
 
Upvote 0
The above is an example. Actually the values are somewhat random. That's why I am investigating them. They could even be complex, which is a complication that I'd rather not address at this time.:confused:
 
Upvote 0
This table will create the complex values of magic squares, i.e. 3,4,5. for all prime numbers and their doubles. The rules are for Y>X, Y is even. The resultant is x^2 + y^2. :):confused::)
 
Upvote 0

Forum statistics

Threads
1,224,558
Messages
6,179,512
Members
452,921
Latest member
BBQKING

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top