Thread: Consolidate and sort data

Hi, I'm having some issues trying to consolidate and sort data with their time stamp.

Basically I have a couple of different series and I'm looking to plot them in excel. The problem is that the time stamps are different as is the sampling rate. Is there an easy way to sort this out, I've tried to use the sort function but I just end up with a whole lot of empty spaces and a big mess.

I've attached an example, the data set itself is much bigger and there are many many more columns.

Any help would be very much appreciated,

Cheers

Hello ArnoOtto,

Plot the series in a XY chart instead of a line chart. You can have a line for each series and the x axis values will be aligned.

If you post a spreadsheet instead of an image, you'll get a chart suggestion in return.

cheers

Arno,

click the chart, then the Design tab on the Chart tools,
click Select Data and then the button "Hidden and Empty Cells"
click "Connect data points with line" and OK

If that does not help, please post a workbook with sample data and the chart.

Arno,

although the cells appear to be empty and have a length of zero, they are not truly blank.

If you select each cell and hit the delete key, you will see your chart starting to tidy up.

Now, normally, I'd go F5 - Special - Blanks to select them all, but this does not work, since they are not blank. They contain a null string "".

I tried Find and replace, but to no avail.

So, out with the bigger guns.

Copy the code below, then right-click on the sheet tab and select View Code. Paste the code into the code window. Click somewhere in the code and hit F5 to run the code.

Before you do that, arrange your windows so you can see both the code window and your chart. You'll see the zero plot points disappear in a nice pseudo animation.

Sub RemoveBlanks()
Dim cel As Range, x As Long
x = Cells(Rows.Count, "A").End(xlUp).Row
For Each cel In Range("B1:B" & x)
    If cel = "" Then cel.ClearContents
Next

End Sub

cheers