+ Reply to Thread
Results 1 to 6 of 6

Excel Macro to remove spurious numbers in column of data help please

  1. #1
    Registered User
    Join Date
    08-17-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    11

    Excel Macro to remove spurious numbers in column of data help please

    I log many engine parameters on my track car from the aftermarket ECU. After logging to a .csv I run a macro which converts some same data into useful information, ie lattitude and longitude.

    The processed .csv is then used for a graphic overlay of speed, rpm, gear, throttle, brake pressure etc on my videos.

    The ECU calculates which GEAR I`m in on the fly by comparing rpm and rotation of the driveshaft which it compares to a table in the ECU. I can't log gear-shaft so am unable to carry out the same calculation afterwards which would probably make life easier.

    The displayed GEAR is fine for 99% of the time, but when I change gear I get several incorrect gear values. I believe this is due to the fact the clutch is still being re-engaged and the engine RPM vs driveshaft calculation 'thinks' I'm in a lower gear until the clutch is fuly engaged and the rpm vs output matches what I expect in the table.

    The datalogs are usually over 50,000 lines long.

    At the moment I go through the table looking for gearchanges and manually changing the 2, 3 or 4 consecutive incorrect cells.

    My question is, is there some way of comparing the cells either side of the current gear value in the same column and then correcting the 'spurious' gear value. When looking at it by eye it's immediately obvious, but I don't know how to even start doing this in VBA
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,468

    Re: Excel Macro to remove spurious numbers in column of data help please

    You are looking at continuous readings of Maximum 3 (readings Row 48,49,50). What is the max readings that are to looked into. Is it 3 as shown in file or still more.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  3. #3
    Registered User
    Join Date
    08-17-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Excel Macro to remove spurious numbers in column of data help please

    Quote Originally Posted by kvsrinivasamurthy View Post
    You are looking at continuous readings of Maximum 3 (readings Row 48,49,50). What is the max readings that are to looked into. Is it 3 as shown in file or still more.
    very occasionally it`s 4 in a row, but usually 2 or less frequently 3.

    The values are between 2 and 6 (ie the gears)

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,468

    Re: Excel Macro to remove spurious numbers in column of data help please

    Here is the code . It copies D column into E then it is processed.
    NOTE: Even though you have told it is 4 but in the file change over only for max of 3 not for 4. Row 7 to 10. so i have restricted to 3 not 4.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 12-16-2020 at 07:18 AM.

  5. #5
    Registered User
    Join Date
    08-17-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Excel Macro to remove spurious numbers in column of data help please

    That is absolutely brilliant !

    Thank you

    Even thought I`ve looked at what you wrote, I still woudln`t have ever come up with that by myself.

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,468

    Re: Excel Macro to remove spurious numbers in column of data help please

    Thanks for feed back.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to remove first character "K" from column of numbers
    By chimukanm2 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-19-2018, 10:32 AM
  2. [SOLVED] Macro to remove lines in Data set if Column A value is blank
    By sx200n in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-03-2017, 06:39 AM
  3. Macro to remove duplicate data in one column and keep highest value in another column
    By Renejorgensen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-18-2016, 08:56 AM
  4. Remove Numbers from texts in excel column
    By glfarnando in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-31-2014, 05:03 AM
  5. [SOLVED] [SOLVED] Macro/Wildcard to remove only numbers excluding text with numbers
    By indianhp in forum Word Programming / VBA / Macros
    Replies: 6
    Last Post: 06-21-2013, 08:16 AM
  6. Macro to remove rows of data when cells in a column contain certain text
    By RF8899 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-05-2012, 07:35 PM
  7. Excel macro to automatically remove leading apostrophe from entire column
    By hk106 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-10-2012, 10:38 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1