Thread: How to plot discontinuous/mixed data sets as scatter graphs?

Greetings All!

I'm in need of a function or methodology which would allow me to select data series from discontinuous data within a data set. I'm sure that none of you have a clue what I'm talking about so I'll illustrate

Code:

     Compound     |     Retention Factor
----------------------------------------
     MINO                 0.122
     TRP                   0.234
     TERB                 0.455
     CAF                   1.223
     MINO                 0.131
     TRP                   0.256
     TERB                 0.501
     CAF                  1.119
     MINO                0.119
     TRP                  0.246
     TERB                0.478
     CAF                 1.299
     etc...

In this example I wish to plot all of the Retention Factors for the Compound TRP as a single series on a scatter graph. If there perhaps a way of using VLOOKUP to achieve this, if so please elaborate since it has been a while since I used VLOOKUP (or in fact anything more complicated than AVERAGE and STDEV).

Please bear (sp?) in mind that my data set is extremely long and while it is possible for me to manually select disjointed data such as these when selecting a scatter graph data series it's frankly an ar*e ache. :P

Any help you could provide would be greatly appreciated.

Kind Regards,

Nubey.

My word that's so simple; I feel like a complete moron now. Me thinks I need to get reacquainted with excel soon.

Anyway thank you kindly to sweep for saving me hours of needless point & clickage.

Best Wishes,

Nubey.

Hello again,

I've identified a slight problem with sweeps approach (using filters) in that the selected data series only remains valid so long as the filter remains applied within the original spread sheet.

In MS Excel 2007 filters seem to work by hiding rows which do not fit the criterion of the filter. Thus:

MINO
TRP
TERB
CAF
MINO
TRP
TERB
CAF
etc...

becomes

TRP
TRP
etc...

when I invoke a filter for TRP only. If I then select the column (which at this point appears to comprise only the TRP dataset) and plot a scatter graph the result is as desired. However when I revoke the above mentioned filter the hidden rows reappear and those datasets that reside between the two extremes of the TRP dataset are added to the scatter graph dataset thus the desired dataset

TRP
TRP
TRP
TRP
etc...

becomes

MINO
TRP
TERB
CAF
MINO
TRP
TERB
CAF
MINO
TRP
etc...

After I revoke the filter. This of course renders the filter approach (at least so far as MS Excel 2007 is concerned) a non option since I wish to plot each dataset individually (i.e. not just TRP) which will require me to invoke/revoke several filters.

Any other ideas? I could always use scattered columns for my retention factors although that's not as asthetically pleasing.

Kind Regards,
Nubey

Hi Andy,

The x axis is a relative time plot.

That all seems a little too complicated, I guess I'll just have to sort the columns alphabetically to make the datasets continuous.

I was hoping to keep the data ordered chronologically but meh.

Thanks for the info though Andy, it could come in handy some day.

Regards,

Nubey.