Importing Data and Creating Charts from Spreadsheet

[Jeff Rasnick]

Greetings -

Sorry if the subject is not very explanitory, wasn't sure how to word it.
Basically my issue is I am trying to create some different charts from a
spreadsheet where there might be multiple rows for the same values that will
go on the X axis. For example this spreadsheet might have the following
Data:

ABC Company $500
BCD Company $332
XYZ Company $500
XYZ Company $200

When that chart is created (company on x axis, amount on Y axis) it creates
4 different columns (one each for ABC Company and BCD Company and two for
XYZ Company). What I would like it to do is to create only 3 different
columns (one each for the 3 companies). So the value for XYZ Company would
be $700 instead of two different columns with one for $500 and one for $200.
Is there a way to get Excel to add those together automatically?

This spreadsheet is created by importing 2 seperate files (from different
programs) into a spreadsheet, and each text file might have values for the
same companies in them, which is why there could be duplicates. There are
hundreds of companies in each file and each file might have some companies
that are not in the other file so not all will be duplicates.

If charting cannot do this automatically, does anyone know if there is a way
to add amounts together when importing a file. I am meaning something like
this: the first file is imported and the data added, then when the second
file is imported it would check to see if that company already exists, and
if so it just adds the amounts to the current row instead of creating a new
row.

Thanks for any help,
Jeff

 

[Del Cotter]

Sorry if the subject is not very explanitory, wasn't sure how to word it.
Basically my issue is I am trying to create some different charts from a
spreadsheet where there might be multiple rows for the same values that will
go on the X axis. For example this spreadsheet might have the following
Data:

ABC Company $500
BCD Company $332
XYZ Company $500
XYZ Company $200

It's tricky, because what you'd like is to use the XY chart (Scatter
chart) type instead of a line chart, which as you've observed, treats
each row as a new category on the Category axis. You want the X axis to
be an interval scale, not a category scale. The problem is that
intervals need numbers, not words, so to start with, you have to have
data that looks like this:

1 $500
2 $332
3 $500
3 $200

Now the chart will look right, but how do you ensure that the company
names have a matching number? I always use a separate lookup table, and
then the VLOOKUP() formula to determine the numbers.

Then, once you've used the numbers to make the scale, you have to put
the words back in to provide a labelled X axis. How? You can use the
Dummy series method as shown by Jon Peltier or Tushar Mehta:

http://peltiertech.com/Excel/Charts/ArbitraryAxis.html
www.tushar-mehta.com/excel/newsgroups/flexible_log_scale/

This is a lot of trouble to go to, but the graph type you are trying to
design is often worth it. It's a type I design frequently.

 

[Tushar Mehta]

Create a PivotTable (and an associated PivotChart). Put the company
name as the row field and the $ amount as the data field. Excel should
automatically pick Sum of $ amount but if it doesn't make sure you
change it appropriately.

 

[Del Cotter]

Tushar Mehta said:

(e-mail address removed) says...

Create a PivotTable (and an associated PivotChart). Put the company
name as the row field and the $ amount as the data field. Excel should
automatically pick Sum of $ amount but if it doesn't make sure you
change it appropriately.

Ah. I hadn't read properly. Yes, that's the way to produce a sum, and a
much simpler process than my answer, which won't produce a sum, but only
plots the two values together in one column instead. Although why the
original poster couldn't have put something about adding the results
actually in the title I don't know.

I stand by what I said about my technique leading to worthwhile charts
though