Create a chart based on multiple values in one cell

Hello,

Okay.. so I'm not sure if this is even possible, but I need to create a chart based on multiple values within a cell. I created a VB code that will enter multiple criteria into one cell, but now I need to be able to create a graph based on each independent value. For instance, if "apple" is selected and is included in a cell with "orange, grape, grapefruit" I need the chart to reflect the dollar amount for each independent fruit.

Thanks so much!

Create a chart based on multiple values in one cell.xlsx

It's certainly possible. You just need a subroutine to parse the contents of the cell, looking for any and all fruits. It helps that you have a comma after each fruit, since that could be the character the parser uses to recognize when a fruit ends and when the next one begins.

However, I'm not sure I understand what you want the chart to look like. For example, your first line in the data file has four fruits and an amount of $7,657. How do you want this displayed on your chart? Likewise with the other lines. Are they all on the same chart or individual charts?

A few chart examples of the end result would help.

Hi Ed_Collins,

I want the chart to sum the total of each fruit into a bar graph... I was thinking of creating a formula to search the content for "apple" for instance in column B. I could most likely get that to work.. but do you think there is a better way? Can you create a chart based on variables that have an amount greater than 0? I need to be able to automate the process and not redefine the parameters of the chart each time.

graph: Bar chart
X Axis: Independent Fruit (example: Apple only)
Y Axis: The sum total based on column B which contains apple

Thank you so much!

awww... the source of the data with the formulas can be created as a table and then I can eliminate all variables that are zero Now I just need to figure out how to create the formula to properly sum the total.. right now, it's just finding the first value.

=IF(ISNUMBER(SEARCH("Apple",B:B)),SUMIF(B:B,G5,A:A),NA)

awww... figured this out:

=SUMIF(B:B,"*Apple*",A:A)

If I enter the * it will search for "apple" within the each of the cells... okay, lets see if this works.

Okay.. so the formula above worked but when I create a table and uncheck 'zero' and then add a fruit with a value, the table does not automatically update. Any suggestions for how to automatically create a chart excluding variables that are zero?