+ Reply to Thread
Results 1 to 8 of 8

Extract Unique points from table column and sort by smallest to largest for IndexMatch use

  1. #1
    Registered User
    Join Date
    04-01-2014
    Location
    Two Rivers, WI
    MS-Off Ver
    Excel Professional Plus 2010
    Posts
    20

    Extract Unique points from table column and sort by smallest to largest for IndexMatch use

    I have a spreadsheet that pulls in date from a table with date/time corresponding to a number. I need to be able to pull out all dates and times that are unique, sort them by smallest to largest.

    Currently I am using

    =INDEX(Table_ExternalData_1[SampleDateTime],MATCH(0,INDEX(COUNTIF($E$2:E3,Table_ExternalData_1[SampleDateTime]),0,0),0))

    to extract from the External Data Table for unique values, but the values extract as they are found in the table. The makeup of the data is such that every 10-15 data points correspond to a unique tracked component, so the dates will never automatically be in the correct sequence.

    The current attempt I am trying to make is to
    1. import the data from an external data source.
    2. extract all unique date and times.
    3. sort the date/time by smallest to largest on separate sheet.
    4. sort component values by date time into columns using Index/Match.

    I am running into a problem getting 3 to work with a changing dataset. I think I am missing something simple, and I apologize if I am.

    The test spreadsheet is attached to the post.

    Thank you for the help.
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,168

    Re: Extract Unique points from table column and sort by smallest to largest for IndexMatch

    Hi joprlw,

    Your problem sounds like you need a Pivot Table answer. The word "unique" in the problem makes Pivots come to mind. Pivots can then easily sort the data and even group it by year, month or day (or even week). See the Pivot I've done on your data to see if it helps. Start learning more about Pivot Tables and what they can do.
    Pivot Table to get Unique Row.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    04-01-2014
    Location
    Two Rivers, WI
    MS-Off Ver
    Excel Professional Plus 2010
    Posts
    20

    Re: Extract Unique points from table column and sort by smallest to largest for IndexMatch

    I am not sure that it is going to do what I need to do. The way the table will be after I am done is that each component will be separated into columns, and placed with the rows being the applicable date/times.

    Is this possible with a pivot table? I have attempted to use them in the past for my needs with this specific type of data sorting, but I never seem to be able to succeed.

    The attached sheet has an example after the pivot table.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,168

    Re: Extract Unique points from table column and sort by smallest to largest for IndexMatch

    Hi joplrw,

    Your attached file won't open for me. I don't know what format you need without an example.

  5. #5
    Registered User
    Join Date
    04-01-2014
    Location
    Two Rivers, WI
    MS-Off Ver
    Excel Professional Plus 2010
    Posts
    20

    Re: Extract Unique points from table column and sort by smallest to largest for IndexMatch

    I have a png of the table example, and attached a renamed example as well.
    Attached Images Attached Images
    Attached Files Attached Files

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,168

    Re: Extract Unique points from table column and sort by smallest to largest for IndexMatch

    Hi,

    Looks like you need a "helper column" to count which date time is next. Then use the Counter column in the Pivot Columns area. See the attached. Is this what you want? You could create other helper columns if you want to round to the minute and merge two values.
    Pivot Table to get Unique Row and columns.xlsx

  7. #7
    Registered User
    Join Date
    04-01-2014
    Location
    Two Rivers, WI
    MS-Off Ver
    Excel Professional Plus 2010
    Posts
    20

    Re: Extract Unique points from table column and sort by smallest to largest for IndexMatch

    Is there a way to get the items to show up as actual values in the pivot table? I am not nearly as fluent with pivot tables as I should be, but I keep only getting the number of instances rather than the actual values after I have the column labels and row labels with what I need them to be.
    Last edited by joplrw10; 06-20-2016 at 08:23 AM.

  8. #8
    Registered User
    Join Date
    04-01-2014
    Location
    Two Rivers, WI
    MS-Off Ver
    Excel Professional Plus 2010
    Posts
    20

    Re: Extract Unique points from table column and sort by smallest to largest for IndexMatch

    Found a solution.

    Utilizing a single row Pivot table, the data sorted the way I needed it to by entering only the data/times into the table. I was then able to use Index/Match to sort the data to the applicable rows.

    Thank you for your help! It is appreciated.

+ 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. Replies: 1
    Last Post: 04-16-2015, 06:29 PM
  2. How Sort by Largest to Smallest Column by Formula
    By termal in forum Excel General
    Replies: 10
    Last Post: 01-08-2014, 01:17 PM
  3. [SOLVED] Extract largest or smallest value from a CELL
    By brad999 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-09-2013, 04:49 AM
  4. Sort largest to smallest - VBA
    By Ricardo Mass in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-12-2013, 09:05 AM
  5. Sort between bold rows with value of largest to smallest in the fourth column
    By minks in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-07-2013, 06:27 PM
  6. Sort Largest to Smallest For Column Pairs
    By bluestarcloudx in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-25-2012, 08:19 PM
  7. Excel 2007 : sort smallest number to largest
    By ravihotwok in forum Excel General
    Replies: 1
    Last Post: 12-06-2011, 11:35 AM

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