+ Reply to Thread
Results 1 to 4 of 4

Reduce the large dataset into smaller dataset using conditions

  1. #1
    Registered User
    Join Date
    09-24-2010
    Location
    Auburn, US
    MS-Off Ver
    Excel 2007
    Posts
    23

    Reduce the large dataset into smaller dataset using conditions

    I want to reduce a very large dataset with two variables into a smaller file. What I want to do is I need to find the data points with the same values and then I want to keep only the starting and ending values and then remove all the data points in between them. For example the sample dataset looks like following :

    363.54167 23.3699
    363.58333 23.3699
    363.625 0
    363.66667 0
    363.70833 126.16542
    363.75 126.16542
    363.79167 126.16542
    363.83333 126.16542
    363.875 126.16542
    363.91667 0
    363.95833 0
    364 0
    364.04167 0
    364.08333 0
    364.125 0
    364.16667 0
    364.20833 0
    364.25 127.79872
    364.29167 127.79872
    364.33333 127.79872
    364.375 127.79872
    364.41667 127.79872
    364.45833 127.79872
    364.5 0
    364.54167 0
    364.58333 0
    364.625 0
    364.66667 0
    364.70833 127.43202
    364.75 135.44052
    364.79167 135.25522
    364.83333 135.12892
    364.875 20.32986
    364.91667 0
    364.95833 0
    Here, the first two points have same values i.e 26.369 so I will keep them as it is. I need to write a condition i.e if two or more data points have same values then keep only starting and ending data points. Then the next two values also have same value i.e. 0 and i will keep these two. However, after that there are 5 data points with the same values. I need to write a program such that I want to write just two data points i.e 363.708 & 363.875 and remove data points in between them. After that I will keep only two data points with zero values i.e 363.91667 and 364.20833.

    The sample output I am looking for is as follows:

    363.54167 23.3699
    363.58333 23.3699
    363.625 0
    363.66667 0
    363.70833 126.16542
    363.875 126.16542
    363.91667 0
    364.20833 0
    364.25 127.79872
    364.45833 127.79872
    364.5 0
    364.66667 0
    364.70833 127.43202
    364.75 135.44052
    364.79167 135.25522
    364.83333 135.12892
    364.875 20.32986
    364.91667 0
    364.95833 0

  2. #2
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Reduce the large dataset into smaller dataset using conditions

    Are the two values in two separate columns?
    Acts 4:12
    Salvation is found in no one else, for there is no other name under heaven given to mankind by which we must be saved.

  3. #3
    Registered User
    Join Date
    09-24-2010
    Location
    Auburn, US
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Reduce the large dataset into smaller dataset using conditions

    Yes they are in two different columns.

  4. #4
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Reduce the large dataset into smaller dataset using conditions

    Assuming your data is in columns A:B, then in another column, starting in row 2, put the following formula and drag it down to the second-last row. Exclude the last row because Excel thinks a zero and a null are the same thing, so if the value in the last row is a 0, the formula will tell you to delete it.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then filter for "Delete", and select and delete those rows.

+ 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. [SOLVED] Vertical chart dataset needs to be converted into pivotable dataset
    By aspak84 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 09-17-2013, 06:06 PM
  2. Copying a dataset multiple times - and selecting titles for this dataset
    By alocke in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-07-2012, 10:15 PM
  3. graphing daily dataset with weekly dataset
    By [email protected] in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 03-27-2009, 09:39 AM
  4. assign dataset to matching dataset
    By Michael Dirksen in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-16-2006, 11:40 AM
  5. [SOLVED] Converting A Quarterly Dataset to Weekly Dataset
    By Dan Thompson in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-25-2005, 05:30 PM

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