+ Reply to Thread
Results 1 to 12 of 12

Get distinct values from Named Range, Table Column, or Pivot Table Row Label/Column Label?

  1. #1
    Registered User
    Join Date
    06-18-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    21

    Get distinct values from Named Range, Table Column, or Pivot Table Row Label/Column Label?

    I tried using a pivot table to get summary info and distinct values and this works great. However, I cant seem to get at the Column Labels or Row Labels to get the unique names for a particular Field I'm using which was the point.

    I've also tried simply doing the following formula, but this leaves in blank spaces in place of the duplicate values which I don't want:
    =IF(COUNTIF(A$2:A2,A2)=1,A2,"")

    I've tried this which simply gives me the first cell value only:
    =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)


    I tried setting up a NamedRange which consisted of these text values, which is fine, but how would OR do you actually get these text values to populate into a particular cell(s) as you cant simply say =NamedRange in a cell because you get a #VALUE error?

    This has to be dynamic as well so that when the source updates (minus duplicates), so does the place I'm putting the list of text values. I've seen where you can reference a table column header and do this, but then the header is present, which isn't very useful and creating an Extract which again only works with static data, so no dice there either.

    Any help would be appreciated
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    07-16-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2013
    Posts
    176

    Re: Get distinct values from Named Range, Table Column, or Pivot Table Row Label/Column La

    here you go.
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,331

    Re: Get distinct values from Named Range, Table Column, or Pivot Table Row Label/Column La

    How does the result look like.

    Post it in you file manualy.

    After that I could take a look at it.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Registered User
    Join Date
    06-18-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Get distinct values from Named Range, Table Column, or Pivot Table Row Label/Column La

    thx, igoodable. Instead of a PivotTable though I need just the opposite and need to take the unique (no dup) data FROM a pivot table or table or just a column of data and place it inside a cell(s). Basically reference it and place it else where.

  5. #5
    Registered User
    Join Date
    06-18-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Get distinct values from Named Range, Table Column, or Pivot Table Row Label/Column La

    Quote Originally Posted by oeldere View Post
    How does the result look like.

    Post it in you file manualy.

    After that I could take a look at it.
    hey oeldere...in my file should is a text reference pointing to where and how the data would look:
    Put unique list of CT values here: ->
    3PI_C
    955CTS
    APPAU

    Or are you referring to something else like a formula you'd like for me to provide?
    I'd simply like to get that unique list and be able to place it elsewhere (anywhere) without the duplicates. I just put three different groups of the same data in various forms incase one of those was easier to access or use.
    Last edited by e2Excel; 07-18-2013 at 01:40 PM.

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,331

    Re: Get distinct values from Named Range, Table Column, or Pivot Table Row Label/Column La

    Why not copy column A to column D and use remove duplicates (since your using excel 2010).

  7. #7
    Registered User
    Join Date
    06-18-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Get distinct values from Named Range, Table Column, or Pivot Table Row Label/Column La

    Quote Originally Posted by oeldere View Post
    Why not copy column A to column D and use remove duplicates (since your using excel 2010).
    I'm trying to find a way to automate that. So if I have data that is updated (changes), so do these other values. I'd like to stick those values in a comboBox is my desired end result with no duplicates and with the ComboBox seeing the changes.

    I can update the ComboBox using a named range which references the Table Column I need (CT), but its then populated with duplicates, which is why I was trying to use a formula with this named range to drop the dups OR use the PivotTable which has them dropped already..but no luck.
    Last edited by e2Excel; 07-18-2013 at 02:54 PM.

  8. #8
    Registered User
    Join Date
    06-18-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Get distinct values from Named Range, Table Column, or Pivot Table Row Label/Column La

    Attached is a working version of what I was attempting using two Named Ranges. Updating the CT value in Sheet1 Column A will result in everything else dynamically updating. I'm not sure however, how to remove the extra entries being populated into the ComboBox? I'm guessing its from the CBvals named range, but not sure how to fix it?
    Any thoughts?

    Also any idea why on Sheet2 I'm forced to have to add my formula to A2?? Is there a way I can put this somewhere else??
    Attached Files Attached Files
    Last edited by e2Excel; 07-18-2013 at 04:27 PM.

  9. #9
    Forum Contributor
    Join Date
    07-16-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2013
    Posts
    176

    Re: Get distinct values from Named Range, Table Column, or Pivot Table Row Label/Column La

    The least complicated way is to put it into a pivot table, which you should be able to reference to. The trick to reference to a pivot table it to type in the cell reference rather than clicking on it. The only other way i know of is via Macro.

  10. #10
    Registered User
    Join Date
    06-18-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Get distinct values from Named Range, Table Column, or Pivot Table Row Label/Column La

    Are you saying I can accomplish what I just did without using the Named Ranges and simply by using a Pivot Table and somehow referencing those "dynamic" values I need (The CT column / row data) and add it to a ComboBox?? If so, can you show me how that is done?

  11. #11
    Forum Contributor
    Join Date
    07-16-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2013
    Posts
    176

    Re: Get distinct values from Named Range, Table Column, or Pivot Table Row Label/Column La

    Can it be a data validation box instead of a combobox?
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    06-18-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Get distinct values from Named Range, Table Column, or Pivot Table Row Label/Column La

    Quote Originally Posted by igoodable View Post
    Can it be a data validation box instead of a combobox?
    Nicely done thank you. Yeah I was purposely trying to avoid that control because its never visible to a user. The ComboBox is always there and visible making it very intuitive which was the natural choice

+ 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: 3
    Last Post: 05-24-2013, 06:24 PM
  2. Filter Pivot Table Row Label by Another Row Label
    By bdcelli in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-14-2013, 12:57 PM
  3. Linking column label in Pivot table
    By James_SF in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-07-2011, 10:55 AM
  4. Ling column label in Pivot table
    By James_SF in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-23-2011, 03:10 PM
  5. How to delete column label from pivot table field
    By Evy Lum in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-27-2006, 09:15 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