+ Reply to Thread
Results 1 to 13 of 13

Extract unique values...with a twist

  1. #1
    Registered User
    Join Date
    06-04-2019
    Location
    Melbourne, Australia
    MS-Off Ver
    365 ProPlus
    Posts
    9

    Extract unique values...with a twist

    Hi All,

    I need help with a vba script.
    I have two lists, a couple of columns apart. The columns contain, and must contain, blank spaces. The number of rows in each of these columns varies.

    I need to be able to extract unique values from each of these, and combine them into one column.

    I have found other scrips online which kind of do the trick but:
    1. Break when the range selected goes pass the last cell with values
    2. Are assigned to two different buttons on the same sheet (I tried using one script and one button per column, and then combine)
    3. Can, seemingly at random, produce a list of unique values with a bunch of blank spaces in random places.

    Any ideas?

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow

    Hi !

    Try an advanced filter …

  3. #3
    Registered User
    Join Date
    06-04-2019
    Location
    Melbourne, Australia
    MS-Off Ver
    365 ProPlus
    Posts
    9

    Re: Extract unique values...with a twist

    I don't want to manually be filtering anything - I want to just run the script and not have to alter the original list.

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow


    Doable via an advanced filter ! Another way via a Dictionary. See both in VBA inner help …

  5. #5
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Extract unique values...with a twist

    Hi thenewbie22,

    Welcome to the forum!! As per Marc L's Dictionary suggestion, try this:

    Please Login or Register  to view this content.
    Just change the lines of code I've marked with "Change to suit" to meet your specific requirements (layout).

    Regards,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extract unique values...with a twist

    Try
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-04-2019
    Location
    Melbourne, Australia
    MS-Off Ver
    365 ProPlus
    Posts
    9

    Re: Extract unique values...with a twist

    This works wonderfully, except the headings of the columns are also in the 'Unique' column.

  8. #8
    Registered User
    Join Date
    06-04-2019
    Location
    Melbourne, Australia
    MS-Off Ver
    365 ProPlus
    Posts
    9

    Re: Extract unique values...with a twist

    Hi Robert,
    Thank you very much for your help. I am getting an error at Sub Macro1(). ' Expected end sub'

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extract unique values...with a twist

    Then change 2 to 1
    Range(e & "1:" & e & ....

  10. #10
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Extract unique values...with a twist

    Hi Robert,
    Thank you very much for your help. I am getting an error at Sub Macro1(). ' Expected end sub'
    You just haven't copied the the last line of code. I would use jindon's solution in any case.

  11. #11
    Registered User
    Join Date
    06-04-2019
    Location
    Melbourne, Australia
    MS-Off Ver
    365 ProPlus
    Posts
    9

    Re: Extract unique values...with a twist

    Perfect. Thank you very, very much!

  12. #12
    Registered User
    Join Date
    06-04-2019
    Location
    Melbourne, Australia
    MS-Off Ver
    365 ProPlus
    Posts
    9

    Re: Extract unique values...with a twist

    Final query: Can you recommend a VBA training online resource? I have tried a few over the years, and I must admit they are hard to follow.

  13. #13
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Extract unique values...with a twist

    Final query: Can you recommend a VBA training online resource?
    I can't I'm sorry but I'm sure someone on the forum can

+ 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: 11
    Last Post: 10-27-2016, 07:33 PM
  2. Extract Unique Values that do NOT Contain...
    By nickmangan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-03-2016, 11:47 AM
  3. Replies: 2
    Last Post: 02-13-2013, 03:14 PM
  4. Extract unique values
    By bourke.margaret in forum Excel General
    Replies: 2
    Last Post: 07-04-2011, 10:41 AM
  5. Extract All Unique Values
    By Jbm444 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-12-2010, 06:44 PM
  6. Extract Unique Values, Then Extract Again to Remove Suffixes
    By Karl Burrows in forum Excel General
    Replies: 23
    Last Post: 06-25-2005, 08:05 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