+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    07-30-2020
    Location
    United Kingdom
    MS-Off Ver
    Microsoft Office Professional 2019
    Posts
    3

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

    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.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,780

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

    Please paste the following into cell B2 on the Graph Data sheet:
    Formula: copy to clipboard
    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    07-30-2020
    Location
    United Kingdom
    MS-Off Ver
    Microsoft Office Professional 2019
    Posts
    3

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

    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.
    Last edited by AliGW; 08-01-2020 at 07:02 AM. Reason: Please don't quote unnecessarily!

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,780

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

    ('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.

  5. #5
    Registered User
    Join Date
    07-30-2020
    Location
    United Kingdom
    MS-Off Ver
    Microsoft Office Professional 2019
    Posts
    3

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

    Quote Originally Posted by JeteMc View Post
    ('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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Color a cell in a table depending on the date on a separate column
    By xlepws in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-26-2020, 01:43 PM
  2. Replies: 4
    Last Post: 04-05-2019, 12:05 PM
  3. Subtracting Column Values determined by another cell value
    By geNebee in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-22-2018, 06:06 AM
  4. Sumproduct of column based on text search of neighbouring column
    By anthropormorph in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-01-2015, 10:51 AM
  5. [SOLVED] Move row based on second column in separate workbook
    By Alimac70 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-03-2013, 07:12 AM
  6. [SOLVED] Construct a formula on a pre-determined cell for a pre-determined duration of cells
    By Shaun Gemiver in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-03-2013, 11:14 AM
  7. 100% Stacked Column Bar Chart color determined by a cell value
    By peterputter in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-03-2012, 08:47 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1