Making separate lines in a X Y scatter plot for a data set that is not dis-aggregated

**je9183** · 11-19-2021, 07:12 PM

I have a data set that is being produced daily and is outputted by a program into an excel spreadsheet. I would like to make an excel spreadsheet that makes a x-y scatter plot of the quantity of different items versus time with different lines for different sampling points. The data is outputted with time in one column, water quantity of different items in other columns, and the name of the sampling point in a third column (i.e. the sampling point column reads from top to bottom: "point 1, point 2, point 3, point 1, point 1...etc.", the data is not separated by sampling point).

How can I make a quantity of different items versus time with a separate line for each sampling point without dis aggregating the sampling points into different data blocks (i.e. all the data for point 1 together, point 2 together, etc)? I am adding data to this daily and I would like the graph to update automatically without having to go through the process of dis-aggregating it.

**MrShorty** · 11-19-2021, 08:06 PM

We might need to understand exactly what kinds of things are prohibited in what you call "disaggregating", because, I think the easiest way(s) to do this are going to require some level of changing how the data are arranged. We might also need to know exactly how the data are currently arranged.

If the data are currently arranged like this:

Please Login or Register  to view this content.

, then my first thought would be something like:

1) Across the top of the sheet adjacent to the source data, make a horizontal list of the different point value
2) Then fill the body of this helper range with a simple IF() function like =IF($C2=D$1,$B2,NA()). Note the mix of relative and absolute references to make copying easy, then copy/paste/fill as far down and across as you will ever need (or figure out how to turn this into a structured table so that the table formulas will fill down automatically as you add new data to the list).
3) Insert a scatter chart using column A as the X values range and columns D to whatever as the Y values range.
4) Check the "missing and hidden cells" property in the chart to make sure it is set to "interpolate".

As you add data to the sheet, the formulas should automatically copy the data into the appropriate column, and the chart should automatically update.

Did I guess correctly at the current arrangement of the data? Does "using a formula to make suitable copies of the data adjacent to the source data" fit into your "cannot disaggregate the data" requirement or not? Anything else to add that will help us help you?