Creating a graph from text

**lordterrin** · 10-18-2010, 12:11 AM

Hello.

I am trying to create a graph that will tell me the following, but I'm not sure how to do it.

I have the following layout of data (simplified for explanation purposes):

____A______________ B

Company A________Coke
Company A________Coke
Company A _______Pepsi
Company B________Coke
Company C________Pepsi
Company D________Pepsi

What I'd like to do is create a graph/chart that shows each company, and how many people in that company prefer which drink. So like - I'm imagining a pie chart, where Company A has two for 2 and 1 for Pepsi, Company B has 1 for coke, etc. Is this possible? I'm driving myself crazy trying to figure this out......

Thanks!

**ron2k_1** · 10-18-2010, 12:36 AM

How would something like this work?

Ron

**lordterrin** · 10-18-2010, 12:54 AM

This does work - but the data I have is over 500 rows, and if possible, I don't want to convert all of the text into numbers. I've tried using the PivotChart feature to make meaningful information out of my data, but it's too complicated for me to understand.

Basically, I have a chart with 8 columns - 3 of which have data in them that I need to analyze.

Column A = Gender, M/F
Column B = Company they work for, There are about 20 different companies
Column C = Gender of Doctor they prefer seeing, (M/F)

What I want to do is create some sort of chart that shows:

how many Males and Females from which companies prefer which gender of doctor. It's too complicated for me though!

So basically there are no numbers anywhere in my sheet....

**ron2k_1** · 10-18-2010, 01:25 AM

Ok, its a bit late here in Belize so why don't you try AutoFilter.

Check the attached, and if that doesn't help, I'll give you a little hand in the morning if you havn't had any head-way by then.

Play around with the headers, and the total number at the bottom will change. On you data, you can go to Data>Filters>AutoFilter

Ron

EDIT: Oh, and you don't have to change all your data to numbers, if you notice on my sheet that has the chart, I used a formula that looks up and count all entries labeled "Pepsi" or "Coke" separately. You can add more data to you columns A and B and just drag the the formulas that I provided. However, you need to make sure that under the Pepsi or coke analysis you have included all the companies on your data set (column A and B), and don't forget to update the chart.

**Andy Pope** · 10-18-2010, 04:31 AM

Sounds like you should be using a pivot table.

**lordterrin** · 10-18-2010, 06:15 PM

Ahhhhh. After a few hours with the pivot table, I finally figured out what I could do with it! Man this is a cool tool. I can't believe I'd never used these before. Thanks a lot guys! I really appreciate the help.