+ Reply to Thread
Results 1 to 8 of 8

Create a list of unique values from several columns in a dynamic table.

  1. 09-26-2018, 06:18 PM #1
    schurchill39
    schurchill39 is offline
    Registered User
    Join Date
    09-24-2018
    Location
    Calgary, AB
    MS-Off Ver
    Office 365
    Posts
    6

    Create a list of unique values from several columns in a dynamic table.

    This is a continuation of questions I have for a the data set I have in a post that the gracious members have helped me solve already.

    Lets say my data was formatted as a table with a starting date of August 11th, and my very last line item actually ended on August 14th (ie row 52 was start date: August 13th, 2018 5:55pm to End Date: August 14th 6:23PM). Id like to create a list of unique dates from the columns Table1[Start Date YYYY-MM-DD] and Table1[End Date YYYY-MM-DD]. I solved it originally using an array index match looking strictly at my start date, however I realized that if I have an end date that isn't also a start date it won't add to the list, also my function gets a little funny because it will repeat dates for some reason on this example file...

    From researching this website and google I have found lots of results for a predefined column range so you can always look for say $A$2:$A$52, list could have as many as 500 entries or as few as 1 which is why I like formatting it as a table. However, it will always only ever be limited to the two columns titled Start Date and End Date.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 09-27-2018, 01:38 AM #2
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Create a list of unique values from several columns in a dynamic table.

    tRY

    h2
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Reply With QuoteRegister To Reply
  3. 09-27-2018, 01:50 AM #3
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Create a list of unique values from several columns in a dynamic table.

    Another way.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Dave
    Reply With QuoteRegister To Reply
  4. 09-27-2018, 11:24 AM #4
    schurchill39
    schurchill39 is offline
    Registered User
    Join Date
    09-24-2018
    Location
    Calgary, AB
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Create a list of unique values from several columns in a dynamic table.

    FlameRetired,

    I am not sure how aggregate works but with some small tweaking for my actual data set this worked beatutifully. I see that it references J$2:J2 which doesn't actually exist, that was just to show everyone what I wanted the list to look like. If I remove ROWS(J$2:J2) then my cells turn blank, or if I put any value in those cells it doesn't seem to have an effect on the results from the formula. So I left it in there and seems to still work fine.
    Last edited by jeffreybrown; 09-28-2018 at 01:20 PM. Reason: Remove full quote
    Reply With QuoteRegister To Reply
  5. 09-27-2018, 12:18 PM #5
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Create a list of unique values from several columns in a dynamic table.

    AGGREGATE 15 operates the same as the SMALL function. The ROWS(J$2:J2) part is the k value of the small function. As ROWS(J$2:J2) fills down it returns 1,2,3 ... telling the formula to return the 1st, 2nd, 3rd ... smallest number in the array.

    The selection of J$2:J2 initiates k from the current cell. It insures that if any rows or columns are inserted/deleted or the formula is moved the k value returns will not be compromised by the position changes.
    Reply With QuoteRegister To Reply
  6. 09-28-2018, 09:48 AM #6
    schurchill39
    schurchill39 is offline
    Registered User
    Join Date
    09-24-2018
    Location
    Calgary, AB
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Create a list of unique values from several columns in a dynamic table.

    So essentially J$2:J2 could be any blank column as its only being used as a reference?
    Reply With QuoteRegister To Reply
  7. 09-28-2018, 01:10 PM #7
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,577

    Re: Create a list of unique values from several columns in a dynamic table.

    IN J2 then copy down.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.
    Reply With QuoteRegister To Reply
  8. 09-28-2018, 06:51 PM #8
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Create a list of unique values from several columns in a dynamic table.

    Quote Originally Posted by schurchill39 View Post
    So essentially J$2:J2 could be any blank column as its only being used as a reference?
    It is only being used as a counter, and it doesn't have to be blank. Presence of data does not affect this. It is just my custom to use the current column/row with the considerations I listed. I will often opt to use column A when I am in a hurry.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Get list of unique values from dynamic range
    By BG1983 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-09-2018, 02:15 PM
  2. dynamic list of unique values
    By td3201 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-14-2017, 12:38 AM
  3. Create a unique list with a count against the unique values
    By barber87 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-12-2017, 10:13 AM
  4. [SOLVED] Unique Dynamic list of numbers from multiple columns
    By juriemagic in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 06-30-2016, 05:31 AM
  5. [SOLVED] Dynamic Unique List from multiple dynamic columns
    By JO505 in forum Excel General
    Replies: 7
    Last Post: 06-11-2015, 05:41 PM
  6. Create unique list from dynamic list with dupes
    By kfryar in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-10-2014, 09:20 PM
  7. create a "live" input table for product prices from a list of automatic unique values
    By andymcnichol in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-11-2012, 06:13 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1