Move Value to Column, Determined by Value of Neighbouring Cell in a Separate Table.

[Swalesy]

Hello everyone,

I hope this is in the correct section of the forum. I have a problem that I can't solve, or even know if it's possible; I've attached a reduced version of the original spreadsheet below. It has a few Macro's, but I assure you there is nothing malicious in them.

So, on the "Raw Data" sheet, you'll see the first body of data, divided into 10 "situations". For each situation there are two pieces of information; a row of smaller numbers, which is to represent time (seconds), and the larger numbers which accumulate over said time. The purpose of all this is to show the information in a scatter chart with lines, and how much the value for each situation increases over time. The specific values of time are important and required.

At the bottom of the Raw Data sheet I've extracted all time values only, put them on one row, sorted them from smallest to largest and removed duplicates (this is what one of the Macro's is for).

Now on the "Graph Data" sheet, I have all time values in order, with the accumulative values for each situation in the rows below. But, the accumulative values are in the wrong place/column, as they're stacked at the start.

Is there any way to move/index those accumulative values, for each situation, so that they are back in the correct column that matches the time above them, in row 1, whilst remaining in their corresponding "Situation" row? It's worth mentioning that, although all zeros can be ignored in this example, except for those at the start, there may come a situation where all of them are populated. Situations 8 - 10 are not used in this example, but they also could be in the future. There are duplicate 'accumulative values' for each situation. The "Situation Data" sheet shows the information source in it's most basic form, although doesn't really need to be referenced.

I don't mind if it can be completed and updated live/automatically, or if there are a few Macro's that I need to run to achieve this, as I can just assign them to buttons. As I've said, I hope to be able to put all this information into a scatter chart with lines, show how the data for each situation accumulates over time.

Thank you for reading, I hope that makes sense.

[JeteMc]

Please paste the following into cell B2 on the Graph Data sheet:

Please Login or Register  to view this content.

Once the formula is pasted into cell B2 drag the fill handle over to cell V2 and then, while B2:V2 are still selected, drag the fill handle down to cell V11.
Let us know if you have any questions.

[Swalesy]

Wow! Thank you so much, JeteMc, it worked first time, exactly how I wanted it to. You've just saved me a lot of time in the long run, so thank you again!

If it's not too much to ask, would you be able to quickly breakdown the formula and how it works? Just so I know for future, as it may help me do something similar again, and I'd also like to learn more about Excel and these features anyway.

Thank you.

[JeteMc]

('Raw Data'!$A$2:$A$20=$A2) produces a true/false array depending on whether or not the situation in cells A2:A20 of the Raw Data sheet is the same as the situation in cell A2 of the Graph Data sheet.
('Raw Data'!$B$1:$AC$19=B$1) produces a true/false array depending on whether or not the value in B1:AC19 of the Raw Data sheet is the same as the value in cell B1 of the Graph Data sheet.
The * could be read as the word and, so that combining the the two arrays will produce an array of 1's and 0's in which there is only one 1.
('Raw Data'!$B$2:$AC$20) lists all of the values in B2:AC20.
Again read the * as the word and, so that combining the arrays will produce an array of 0's along with the one value that matched the only one in the previous array (I hope that makes sense).
It may be helpful to select cell F2 on the Graph Data sheet and use the Evaluate Formula feature to see the formula in action.
If this answers your questions, then please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

[Swalesy]

('Raw Data'!$A$2:$A$20=$A2) produces a true/false array depending on whether or not the situation in cells A2:A20 of the Raw Data sheet is the same as the situation in cell A2 of the Graph Data sheet.
('Raw Data'!$B$1:$AC$19=B$1) produces a true/false array depending on whether or not the value in B1:AC19 of the Raw Data sheet is the same as the value in cell B1 of the Graph Data sheet.
The * could be read as the word and, so that combining the the two arrays will produce an array of 1's and 0's in which there is only one 1.
('Raw Data'!$B$2:$AC$20) lists all of the values in B2:AC20.
Again read the * as the word and, so that combining the arrays will produce an array of 0's along with the one value that matched the only one in the previous array (I hope that makes sense).
It may be helpful to select cell F2 on the Graph Data sheet and use the Evaluate Formula feature to see the formula in action.
If this answers your questions, then please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

Thank you JeteMc, I'll take a look at the Evaluate Formula feature. Really appreciate the help, and you taking the time to explain the formula to me. Thank you so much.