Excel Help - Sort Data

[RTX 3090]

Hi, I'm trying to organise my Arduino sensor data into a graph but am struggling with cleaning up the data. This is a sample of the raw data

 

 #

 

I want excel to separate this into a heading, and two variables. (Datapoint is supposed to go up by 1 every time it checks the sensor again, but havent got that code working yet)

This is what I want to turn it into automatically somehow

I want to do this so I can get a nice graph with 2 lines for the different sensors (MQ2 and MQ3)

 

Any help is greatly appreciated

[manikyath]

you're not gonna do this with formula's.

 

either you prep the data before loading it into excel (with python or something of that nature) or you use a VBA macro in excel to prep the data.

[Kilrah]

Make your code write out the data in csv format.

Header with titles, then one line per point with the values.

 

Datapoint,MQ2,MQ3
0,234,134
1,245,21
2,21,356

Then you can import that into excel directly and graph the columns.

[Zando_]

15 minutes ago, manikyath said:

you're not gonna do this with formula's.

Just did. Sort of. 

 

Changed the values to be unique so it's easier to see what it's doing: 

 

For this to work, you do need the DataPoint # to be working though. And you will need to manually drag the header # column out for as many datapoints as you have (if you grab a few cells then drag to fill, excel will notice you're counting up one by one and continue it for you). Does also use an INDEX formula which can get pretty performance heavy depending on how big your data set is IIRC, I've only lightly mucked about with them. Then you use this formula and it works:

 

=INDEX($B$2:$B$10,MATCH(CONCAT($D$2," ",E2),$B$2:$B$10,0)+1,0) if you want to paste it in, you add +2 instead of +1 after the MATCH function for the 2nd row (MQ3). This will break the instant the format changes or again, if the Datapoint # isn't working. 

 

Best solution is what @Kilrahsaid, make it spit out a CSV. 

 

[manikyath]

1 minute ago, Zando_ said:

 

For this to work, you do need the DataPoint # to be working though. And you will need to manually drag the header # column out for as many datapoints as you have (if you grab a few cells then drag to fill, excel will notice you're counting up one by one and continue it for you). Does also use an INDEX formula which can get pretty performance heavy depending on how big your data set is IIRC, I've only lightly mucked about with them. Then you use this formula and it works:

pretty impressive actually, but you'll still need to find a way to slice the cells in half (the value is only what's after the space) and that's already something i wouldnt want to rely on for larger datasets.

 

either way, you're twisting excel in all sorts of ways that it shouldnt be taken, for something a 5-line python script or VBA script will blast trough in milliseconds.

 

i'm not going to do midnight programming because it'll keep me up all night if i do, but essentially this is a very quick and dirty solution:

- open the source file (i'm assuming a .txt dump from a serial prompt, or a .csv file with horrid formatting)

- open a destination .csv

- prep the destination .csv with "DataPoint <tab> MQ2 <tab> MQ3)

- loop until end of file:

-- read a line into string

-- if string contains 'DataPoint', output newline

-- split the string at space, and output the second segment. then output <tab> (you might have to slice off a newline marking from the string too, not 100% on that)

- after the loop, close the two files, and you're done.

 

the reason i say output a <tab> instead of csv ('comma separated values') is because excel's csv processor expects tabs by default.

 

 

OR.. ofcourse.. arduino sided soluton:

 

instead of printing 3 lines you just do this:

Serial.print(intDataPoint);

Serial.print(',');

Serial.print(intMQ2value);

Serial.print(',');

Serial.print(intMQ3value);

Serial.println();

 

poops out 'correct enough' csv for excel's csv import function to understand. replace commas with tabs for something excel will import more easily.

 

in fact.. i'm pretty sure if you stick spaces in there with the above formatting, arduino IDE's serial monitor will make the graph just off of the data stream.

 

[Avocado Diaboli]

Assuming you're importing the data like this:

 

Then this macro should do the trick.

 

Sub SortData()
    
    Dim arr() As Variant
    arr() = ThisWorkbook.Sheets(1).Range("A1").CurrentRegion.Value2
    
    Dim arraySize As Long
    arraySize = (UBound(arr) / 3) - 1
    
    ReDim arr1(arraySize) As Long, arr2(arraySize) As Long, arr3(arraySize) As Long
    
    Dim i As Long, j As Long
    j = 0
    Dim val() As String
    
    For i = LBound(arr) To UBound(arr)
        
        val = Split(arr(i, 1), " ")
        
        Select Case val(0)
            Case "DataPoint"
                arr1(j) = val(1)
            Case "MQ2"
                arr2(j) = val(1)
            Case "MQ3"
                arr3(j) = val(1)
                j = j + 1
        End Select
        
    Next i
    
    arraySize = arraySize + 1

    With ThisWorkbook.Sheets(1)
        .Range("D1").Resize(, arraySize).Value2 = arr1
        .Range("D2").Resize(, arraySize).Value2 = arr2
        .Range("D3").Resize(, arraySize).Value2 = arr3
    End With
    
End Sub

 

Works like this:

[Zando_]

57 minutes ago, manikyath said:

pretty impressive actually, but you'll still need to find a way to slice the cells in half (the value is only what's after the space) and that's already something i wouldnt want to rely on for larger datasets.

Oh derp, that would be a simple function or two to delete the spaces and 1st 3 characters, but yeah it approaches a nice level of clusterfuckery that snowballs the instant anything changes