+ Reply to Thread
Results 1 to 7 of 7

Merging 2 or more Charts, or manually creating each bar

  1. #1
    Registered User
    Join Date
    05-11-2019
    Location
    Almerķa, Spain
    MS-Off Ver
    2016
    Posts
    3

    Question Merging 2 or more Charts, or manually creating each bar

    Hi!

    I've been trying to look for a way to "merge" two or more bar charts, with no success over google nor MrExcel.
    I'm trying to merge two bar charts into one.As said, one image > 1.000 words, here's an image of what i want:

    merging-charts.png

    Creating 3 series and making a stacked bar chart would do the job, but wouldn't fit my purposes, as I will be having more data in the nearest columns , concerning different years, and i want also to show the same chart for all of the columns. I also have the same need in other sheets with different charts, so it would be great to find a solution without having to reorganise the data, or creating auxiliary tables for each year.

    As asked, here's a kick excel sheet which shows the data, what i'm obtaining, and what i want to get:
    charts1615.png

    To sum up:
    Is there a way to build each bar in a chart manually selecting it?
    Once i get to the solution, i would also like to ask excel to show the same chart, but getting the data from next column. Is there a way to do so?


    Thanks in advance to everyone!

    PD: I posted the same question in other forums, because it's been a while since i posted the first one and i didn't get the solution i wanted, so i tried in different communities. Once I get the solution i will post it in every thread so everyone can benefit from it.
    It seems that i'm not allowed to post any URL for the moment as asked in a reply, so i the only way i figured out to do so is by attaching a text file with them.
    Attached Files Attached Files
    Last edited by javibg6; 05-13-2019 at 04:14 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,463

    Re: Merging 2 or more Charts, or manually creating each bar

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,463

    Re: Merging 2 or more Charts, or manually creating each bar

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Read this to understand why we (and other sites like us) consider this to be important.

    As you have half indicated where you cross posted and you are new on the forum I am adding the link here https://www.mrexcel.com/forum/excel-...ml#post5273609

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,936

    Re: Merging 2 or more Charts, or manually creating each bar

    After more than a day and no responses in either location, I will venture to respond. I think there is a good deal of uncertainty on our end trying to understand exactly what you want and exactly what your limitations are.

    I also have the same need in other sheets with different charts, so it would be great to find a solution without having to reorganise the data, or creating auxiliary tables for each year.
    What I see in your pictures are clustered and stacked bar charts, and the usual approach to a clustered and stacked bar chart is all about rearranging data in the spreadsheet -- often leading to auxiliary tables. In fact, I would go so far as to say that the hardest part of creating these charts is building the auxiliary table that would make creating the chart fairly easy. I think the first thing I would want to understand is why you are opposed to using an auxiliary table to build this chart.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    05-11-2019
    Location
    Almerķa, Spain
    MS-Off Ver
    2016
    Posts
    3

    Re: Merging 2 or more Charts, or manually creating each bar

    Hi MrShorty! Thanks a lot for replying!

    When i first posted this question, i got a reply proposing to rearrange the data (or to create an auxiliary table) like this:

    url].png

    This solution works, but i wanted to take all the necessary data from just one column, as i have more data in the nearest columns, concerning different years. With these columns, i want also to get the same chart.

    Furthermore, I have the same need concerning other data, here is a quick example:
    Concerning data from my company's balance sheet, I want to show a column that represents the working capital. This column must be placed between the assets' column and the liabilities' column.
    Here's the result i would like to get, but instead of in three charts, just in one:

    Attachment 624256

    I thought it might be a common issue, and there would be a way to manually tell excel, for each column, where to get each data from, but i'm starting to think there is no other way to do so.

    Is this subject clearer now? Anything else to clarify?

    Thanks in advance!

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,936

    Re: Merging 2 or more Charts, or manually creating each bar

    I thought it might be a common issue, and there would be a way to manually tell excel, for each column,
    The auxiliary table described is the easy way to tell Excel which value(s) go(es) in which stacked bar series. I'm sure there are other ways to do this that will avoid having the auxiliary table in the spreadsheet (probably something involving array constants stored in the chart rather than references to the spreadsheet).

    I find that this kind of programming question is not usually about what is possible. I tend to be optimistic that, with enough time and effort and ingenuity, almost anything is possible. I find it usually comes down to more of a cost-benefit analysis. You've shown that the chart is quite easy to create using an auxiliary table. The question is more about is the benefit (eliminating the auxiliary table) worth the cost (more time/effort to create the chart and more time and effort to change/maintain the chart in the future). Before going to the extra effort of creating a chart without the auxiliary table, I am hoping to understand why you want to avoid the standard, easy way to do this.

  7. #7
    Registered User
    Join Date
    05-11-2019
    Location
    Almerķa, Spain
    MS-Off Ver
    2016
    Posts
    3

    Re: Merging 2 or more Charts, or manually creating each bar

    Yeah, i think you're right. It's been prooven that in matter of cost-benefit,it's not worth it. I'd rather make an auxiliary table. Thought it would be easy the other way.

    Now doing so, i'm getting a little issue with the names of the series. Each bar's data has it's own name, but excel doesn't get it. Any clue on how to solve it? Here's what i'm getting and what i'm looking for:

    Attachment 624479

    Attached is the example excel sheet too.

    Thanks for all the help!
    Attached Files Attached Files

+ 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] Manually specify date ranges in charts - Excel asks for datevalues?
    By bjornas in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 04-30-2018, 08:58 AM
  2. [SOLVED] Difference between manually creating Form and programmatically creating
    By saziz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-10-2015, 01:06 PM
  3. merging data between columns - do I have to do this manually?
    By cnctech in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-06-2011, 10:09 AM
  4. Creating a bar graph manually
    By Biff in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-06-2005, 06:05 AM
  5. [SOLVED] Creating a bar graph manually
    By WLMPilot in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  6. [SOLVED] Creating a bar graph manually
    By WLMPilot in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. [SOLVED] Creating a bar graph manually
    By WLMPilot in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-23-2005, 02:05 PM

Tags for this Thread

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