+ Reply to Thread
Results 1 to 11 of 11

unique values into an array?

  1. #1
    Registered User
    Join Date
    02-27-2008
    Location
    Irving, Texas
    MS-Off Ver
    MS Office 2016
    Posts
    96

    unique values into an array?

    Hi all,

    I've searched here and on google, but haven't found what I'm looking for. So, I'll post it instead:

    I have a long list of values in Column A, several thousand of them. However, only 10 or so are unique values. I already have some code (below) to count the unique values, but what I'd also like is to create an array of those values as text to be used later for making sheets with a specific name. Here's the counting code I found:

    Please Login or Register  to view this content.
    How do I push each unique value into an array element?

    Thanks,
    Adam
    -Adam Hartman
    Mechanical Engineer

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: unique values into an array?

    Advanced Filter?
    Please Login or Register  to view this content.
    The top row must be a header.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: unique values into an array?

    Functions are awesome for returning calculations into a cell with nothing more than a formula...later when you're employing the function, that is.

    If your goal is to evaluate a column and create a set of worksheets with the names of those values, I would opt for a regular macro to accomplish that. I do that very process quite often.

    Also, in doing so, getting unique values from a column is instantaneous with the Advanced Filter.

    Here's my stock macro for taking a set of data on a specific sheet...creating an array of the values in a specified column (column A = 1), then copy the rows of data from that data set to new sheets. Each sheet is named for the unique value and results in only that value's rows of data. The technique employed here creates an array called MyArr that holds all your unique values from column A, just like you've requested.
    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    02-27-2008
    Location
    Irving, Texas
    MS-Off Ver
    MS Office 2016
    Posts
    96

    Re: unique values into an array?

    Thanks for the code, JBeaucaire! It works well and is a great starting block for me to build from. If I've already got plans to use autofilters on the Data sheet, what's the tweak to get your code to only copy the non-filtered rows to the new sheets? And, how do you 'cleanup' the fact that your Data sheet now has put data in the EE column and 65536 row? I'd like Ctrl+Shft+End to still take me to the last cell of actual data after all's said and done.

    -Adam
    Last edited by shg; 03-08-2010 at 03:07 PM. Reason: deleted spurious quote

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: unique values into an array?

    How do you 'cleanup' the fact that your Data sheet now has put data in the EE column and 65536 row?
    You must have stopped the code midstream. There is code in there to clear out the temporary listing in column EE.
    Please Login or Register  to view this content.


    I'd like Ctrl+Shft+End to still take me to the last cell of actual data after all's said and done.
    Try this:
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    02-27-2008
    Location
    Irving, Texas
    MS-Off Ver
    MS Office 2016
    Posts
    96

    Re: unique values into an array?

    I'm not stopping the code midstream, but it is inconsistent, so I must not be replicating the initial conditions the same every time. I'll look at that.

    To my other question, is there a way to only copy the non-filtered values for each set corresponding to the new sheet?

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: unique values into an array?

    Quote Originally Posted by ahartman View Post
    ...it is inconsistent, so I must not be replicating the initial conditions the same every time. I'll look at that.
    Do let me know. I've never had that column left uncleared, so if there's a hole I would definitely like to plug it. Post up a misbehaving workbook if you can replicate that...thanks for the feedback.

    To my other question, is there a way to only copy the non-filtered values for each set corresponding to the new sheet?
    I'm not sure what you mean by non-filtered rows. This is designed to split a larger dataset up by the column you select, so all the copy jobs are based on the values being filtered.

    Are you saying that each time you filter the data, you want to see all the rows that DON'T match that particular value in the filtered column? If so, change the criteria on the filter to:
    Please Login or Register  to view this content.
    See if that works for you. If not, more detail on what you mean by non-filtered data, or perhaps a small sample workbook demonstrating your goal.

  8. #8
    Registered User
    Join Date
    02-27-2008
    Location
    Irving, Texas
    MS-Off Ver
    MS Office 2016
    Posts
    96

    Re: unique values into an array?

    What I'm saying is this:

    1) I will filter the data based on some criteria.
    2) This will leave a data set that has perhaps 10% of the original rows, but still have many instances of unique values in column A. The data are parts listed for a job, with multiple jobs listed one after another.
    3) I would like to copy all the rows of data which have survived my filtering, such that all those parts belonging to Job1 get copied to the Job1 sheet, those for Job2 go to the Job2 sheet, etc. I get this functionality by applying all my filters manually, then filtering for Job1, selecting all the data (Ctrl+Shft+End), copying, and pasting to a new sheet.
    4) My end result is that each Job sheet has the small subset of filtered items from the original Data sheet which met my filtering criteria and were listed for that Job#.

    Is that more clear? I'm avoiding filtering the data on each sheet individually, or applying the filters over and over again, simply to speed up execution. I'd want to filter it all the way I want, then pick out each Job's data and copy that.
    Last edited by ahartman; 03-08-2010 at 04:59 PM.

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: unique values into an array?

    Hmm, I really hate working this way. A sample workbook would demonstrate immediately what your after instead of this back and forth, you know?

    So, you want to filter by a column of unique values, then filter THAT data for another subset? That seems straightforward, we'd just need a clearly presented example. Again, a sample workbook is best.

  10. #10
    Registered User
    Join Date
    02-27-2008
    Location
    Irving, Texas
    MS-Off Ver
    MS Office 2016
    Posts
    96

    Re: unique values into an array?

    Here's what the final should look like. If I apply the filters:

    Cost = $2
    Size = m

    I get a certain subset of my original data.

    If I then filter:

    Job = 1

    and copy/paste that data to sheet 1, I get what's shown on sheet 1.

    Repeat for filters:

    Job = 2
    Job = 3

    and the results are shown on sheets 2 and 3, respectively.

    Hopefully, that's more clear?
    Attached Files Attached Files

  11. #11
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: unique values into an array?

    Yeah, that's a bit more steps to go through. If you start with a fully demonstrated sample sheet like that, we can get this done in many fewer posts...

    This requires that those $ symbols stay in the cost column.

    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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