+ Reply to Thread
Results 1 to 12 of 12

Graphing data from multiple cells each containing varying amounts of comma separated value

  1. #1
    Registered User
    Join Date
    11-23-2010
    Location
    Los Angeles, CA
    MS-Off Ver
    Office 365
    Posts
    15

    Question Graphing data from multiple cells each containing varying amounts of comma separated value

    Hello,

    I've got a tricky problem where I'm trying to graph data in a certain way but the data is not in a format to be worked with. The data I'm looking at is exporting from our software tool like this:

    Account Modules Implementation Status
    Account A Module 1, Module 3, Module 5, Module 6 Active, Delayed, Active, On Hold
    Account B Module 2, Module 3 Active, Complete
    Account C Module 7 Active
    Account D Module 1, Module 2, Module 3, Module 4, Module 5, Module 6, Module 7 On Hold, Active, Not Started, Not Started, Active, Active, Not Started

    I want to display this data like this:

    ImplementationStatus.PNG

    I found some tricks to move from individual cells to columns while removing the commas, but then the trouble becomes how to align things for easy counting and graphing.

    Any ideas on how to solve this?

    Thanks,
    -Tom

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

    Re: Graphing data from multiple cells each containing varying amounts of comma separated v

    I found some tricks to move from individual cells to columns while removing the commas...
    Perhaps if we could see a file, not a screen shot, that illustrates what you have done so far it would be easier for someone to propose a solution.
    By the way, are you using the 2003 version of Excel as listed in the profile? This may be a factor in deciding how/if there is way to accomplish the goal.
    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
    11-23-2010
    Location
    Los Angeles, CA
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Graphing data from multiple cells each containing varying amounts of comma separated v

    Hi JeteMc,

    Thank you for responding! It's been quite some time since I last posted a question and I forgot to update the Excel version. I've got the current Office 365 through my work, which I think is 2016?

    I've attached a sample worksheet with 3 tabs. The first tab [Raw Data] shows the format of the raw data (with some extraneous columns removed). There are 3 main columns:
    • Account - the customer account in question
    • Module - A list of all modules the customer has in implementation separated by commas
    • Status - A list of the statuses for each of the modules in column B, listed in order (first module listed correlates to the first status listed, 2nd to the 2nd, etc).

    There can be anywhere from 1 to 15 modules, and the statuses will correlate and match.

    The second tab [Manually Correlated] shows what was able to do so far to get the data out of single cells and lined up. But, this isn't the best way to graph things, or I haven't figured out a clever enough solution.

    The final tab [Desired Result] shows the graph that I'd like to present of the data, which is a summary of modules by status, ignoring the account. This is where I'm struggling - I can't figure out how to get the data into a format that will allow me to do the lookups and finally graph the quantities.

    Thank you again for offering to help. Let me know if I can clarify anything else.

    -Tom
    Attached Files Attached Files

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

    Re: Graphing data from multiple cells each containing varying amounts of comma separated v

    As you are using the 365 version, I tried to come up with a method that would use Get & Transform to totally convert the raw data into the setup in columns A:C on the desired result sheet, however I ended up having to hybrid Get & Transform with formulas.
    Perhaps another contributor will be able to show us both how Get & Transform will accomplish the entire conversion. Until then perhaps the following will help.
    As modeled on the Raw data sheet convert the data in rows 1:4 into a table
    On the Data tab select From Table/Range
    The following is the Power Query advanced editor code that is used to produce the green table seen in rows 9:37
    Please Login or Register  to view this content.
    Columns D:E are then populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Column F is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The formulas you are already using in H2:L8 on the Desired Result sheet should get you home from here.
    Let us know if you have any questions.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-23-2010
    Location
    Los Angeles, CA
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Graphing data from multiple cells each containing varying amounts of comma separated v

    Thanks for the reply. I'm happy to know there will be a way to make this work. I am having some trouble with the advanced formula though. I copied it in directly and it is giving me the error:

    Expression.SyntaxError: Token Identifier expected.

    I updated the query to reflect the name of the table I created from the data per your instructions. Did I miss a step?

    It's also worth noting that there may be up to 15 total modules that go by more specific names that Module A, Module B, etc, and of course I have 163 actual rows of data to work from.

    -Tom

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,499

    Re: Graphing data from multiple cells each containing varying amounts of comma separated v

    I am guessing that the issue is caused by the 15 modules, however to insure that it isn't the name I have let Excel use its own names for the tables this time.
    Power Query advanced editor code:
    Please Login or Register  to view this content.
    Formula for columns D:E =IFERROR(INDEX(Query1[Account],AGGREGATE(15,6,(ROW(Query1[Value])-ROW(Query1[[#Headers],[Value]]))/(LEFT(Query1[[Value]:[Value]],6)="Module"),ROWS(A$1:A1))),"")
    Formula for column F: =IFERROR(INDEX(Query1[Value],AGGREGATE(15,6,(ROW(Query1[Account])-ROW(Query1[[#Headers],[Account]]))/(LEFT(Query1[[Value]:[Value]],6)<>"Module"),ROWS(C$1:C1))),"")
    If this doesn't produce desired results then perhaps you could upload another file that shows as close to actual source data as possible without including sensitive information.
    Let us know if you have any questions.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-23-2010
    Location
    Los Angeles, CA
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Graphing data from multiple cells each containing varying amounts of comma separated v

    Okay, making progress! I was able to get the first step in the Power Query to work and created the first chart.

    After updating the formula for columns D and E to the table name, I'm not getting any results. It comes up blank after entering it. Before when I left "Query1" in the formula it kept coming back with the "did you mean to enter a formula?" error. So I think we're close. To understand what I need to update:

    "Query1" is The name of the data table created after the Power Query, and Account and Value are the headers of the two columns of data?

    Do I need to update any of the column or row indicators (like A$1:A1) depending on where I place the start of the data?

    -Tom

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,499

    Re: Graphing data from multiple cells each containing varying amounts of comma separated v

    Try running the Evaluate Formula feature on one of the cells in column D. That may point to the issue, especially if the column header name has an extra space or some other abnormality.
    If you find that the formula isn't recognizing the columns in the Query1 table then select that argument in the formula, for example Query1[Account] is the array argument for INDEX.
    Once you have selected then move your pointer to the first cell beneath the header in the Account column and press the Ctrl, Shift and down arrow keys.
    That should correct any error to the array argument.
    If necessary repeat the process for the reference arguments in the ROW functions.
    The Text argument for the LEFT function will need to be entered as [[...]:[...]] so that it will lock when dragged to column E
    The array argument for the ROWS function is a counter so the column letters are not important, but be sure that the first counter has the same row number for both sides of the : (i.e. A$1:A1 and NOT A$1:A2)
    The counter will increment as it is dragged down. (I hope that makes sense)
    Let us know if you have any questions.

  9. #9
    Registered User
    Join Date
    11-23-2010
    Location
    Los Angeles, CA
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Graphing data from multiple cells each containing varying amounts of comma separated v

    I'm still stuck. The formula for D and E kept returning a blank. The formula evaluator was difficult to figure out where it was failing, but looked like it returned N/A.

    I've taken my actual data and have anonymized it to Account 1, 2, 3, etc and Module 1, 2, 3 etc. After doing the lookups and pasting in the new values, all of a sudden I started getting results returned in D and E, but not all the way down the list. I'm not sure why it isn't working, so I've attached the sheet to look at. I can reverse the coding I applied to the accounts and module names later.

    Would you mind please looking it over and helping me see what I'm doing incorrectly or where the formula is failing to return the desired information?

    Thanks!

    -Tom
    Attached Files Attached Files

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

    Re: Graphing data from multiple cells each containing varying amounts of comma separated v

    Very minor detail. The formulas in the file had been array entered which locked the reference to the Account column as the formula was copied over to column E.
    To correct select cell D1 and press the F2 key,
    Press the Enter key, or Ctrl and Enter to keep D1 as the active cell,
    Drag the fill handle over to column E,
    While D1:E1 are still selected double click the fill handle for cell E1,
    This should produce values down to row 744 in which D744 displays Account 161 and E744 Displays Module 7
    I suggest that you also select cell F1, press the F2 key and then enter and double click down,
    Cell F744 displays Not started.
    The values displayed in D744:F744 seem correct looking at Table2_2
    Let us know if you have any questions.

  11. #11
    Registered User
    Join Date
    11-23-2010
    Location
    Los Angeles, CA
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Graphing data from multiple cells each containing varying amounts of comma separated v

    Okay that worked, and I should be able to translate this back into the original account and module names. Thank you!

    The only strange thing still happening is if I use the real data with the actual customer names and the actual module names I get blanks. If I copy/paste the real data into the sheet I sent into you, it actually works. I've copied the formula's text from the entry box on the sheet it works back to the sheet that it doesn't, but still getting a blank returned.

    Any clues on that? This one is tricky since I can't actually share the real names of things to you. But what can I look for to determine why it doesn't return the right information when I use the real data?

    Edit: I've narrowed it down to the data in column B. When the real module names are included instead of "Module 1", "Module 2", etc, both columns D and E go blank. If I substitute just one "Module 1" for the real name it comes back blank on the main sheet.

    The test sheet I uploaded acts up as well: On the test sheet I provided you, rename the first instance of Module 1 in cell B2 to any word you want (I used Goofy). When I do this, D2 changed from Account 1 to Account 2, E1 now shows Module 2, and F1 shows Goofy, which isn't where that should show up. Thoughts?

    Edit 2: Looks like it will work if the word Module is included. The formula is looking for the key word Module, correct? If so I might be able to work with that.

    Thanks,

    -Tom
    Last edited by ride_op; 09-16-2020 at 02:04 PM.

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,499

    Re: Graphing data from multiple cells each containing varying amounts of comma separated v

    Responding to Edit 2: Yes, the formula is looking for the word Module to be (or not to be) the first six characters in the Value column of the Query1 table.
    Columns D:E ...(LEFT(Query1[[Value]:[Value]],6)="Module")...
    Column F ...(LEFT(Query1[[Value]:[Value]],6)<>"Module")...
    Let us know if you have any questions.

+ 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. Average of comma separated numbers from multiple cells
    By engineer_infinity in forum Excel General
    Replies: 7
    Last Post: 04-03-2020, 04:48 PM
  2. [SOLVED] Counting Cells with Varying amounts of data
    By bradydecouto in forum Office 365
    Replies: 3
    Last Post: 11-07-2018, 03:38 PM
  3. Separating multiple data separated by a comma into single cells.
    By sgmgrider in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-29-2015, 05:58 PM
  4. Data Validation (comma separated cells)
    By Hellix2 in forum Excel General
    Replies: 4
    Last Post: 03-06-2013, 02:26 AM
  5. Data Validation (comma separated cells)
    By Hellix2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-05-2013, 08:18 PM
  6. List box data to cells separated by comma
    By naflas in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-13-2012, 12:44 PM
  7. [SOLVED] How do i merge data in a row of cells to be comma separated in on.
    By Banana in forum Excel General
    Replies: 1
    Last Post: 02-07-2005, 02:06 PM

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