+ Reply to Thread
Results 1 to 13 of 13

Unigue list from items in each line of list

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,706

    Unigue list from items in each line of list

    Sorry I didnt really know how to phrase my subject line. My data is arranged like this:
    Please Login or Register  to view this content.
    I need a unique list of each of those 6 digit codes. Only way I can figure it is a long way around:
    1) used excels Duplicate Finder to get my unique fields (for instance, in the above lines 2 and 3 match, so DupeFinder would delete one).
    2) Use TextToColumns to separate each 6-digit code
    3) Put them all in a column and used Advance filter to get a list of unique values.

    Is there a better way?

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,741

    Re: Unigue list from items in each line of list

    Hi.

    http://excelxor.com/2014/09/30/list-...rated-strings/

    in which you'd make a minor change to two of the constructions I give to account for the fact that your entries are separated by commas, not spaces, viz:

    Arry1 becomes:

    =1+LEN(Range1)-LEN(SUBSTITUTE(Range1,",",""))


    Arry3 becomes:

    =INDEX(TRIM(MID(SUBSTITUTE(Range1,",",REPT(" ",999)),TRANSPOSE(999*(ROW(INDIRECT("1:"&MAX(Arry1)))-1)+1),999)),N(IF(1,1+INT((Arry2-1)/MAX(Arry1)))),N(IF(1,1+MOD(Arry2-1,MAX(Arry1)))))

    If you have any difficulties adapting that solution to meet your needs then just let me know.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,706

    Re: Unigue list from items in each line of list

    XOR LX,
    I think I followed everything, but I'm having an issue. My range doesn't start in the same place your example did, so I had to make some changes. I get a correct count, and the first unique item shows up fine, but not the others. Can you take a look at my example and advise? Thanks!
    Attached Files Attached Files

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,741

    Re: Unigue list from items in each line of list

    Sure. The first thing I notice is that you haven't committed the column Q formulas as array formulas.

    Regards

  5. #5
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,706

    Re: Unigue list from items in each line of list

    And that was exactly it! Thanks so much. THIS time it took longer doing it the formula way, but NEXT time this is going to come in real handy. Thanks so much for showing me the way. Next step for me is to figure out how to map all the nameranges and formulas to a function so I can easily repeat this on demand; I've bookmarked the thread you've referenced so I can use it when I'm ready for my next step.

  6. #6
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,741

    Re: Unigue list from items in each line of list

    You're welcome!

    Actually I also noticed something a bit more interesting, which occurs on my machine using Excel 2010 (though perhaps it's not an issue for you?), and that is that you are missing an 8th result (in cell Q12), i.e. 7672AZ.

    This is because the initial clause in the formula in that cell, i.e.:

    IF(ROWS($1:8)>$P$5

    is evaluating to TRUE, and so returning a blank, the reason being that, if you change the cell format for P5 to e.g. Number with 15 decimal places you'll see that it's not actually 8 at all, but 7.999999999999980!

    Sometimes these floating-point arithmetic rounding errors are a real pain in Excel! Based on this, I would use, for P5:

    =ROUND(SUM((Arry3<>"")/MMULT(0+(Arry3=TRANSPOSE(Arry3)),ROW(INDIRECT("1:"&COUNTA(Arry3)))^0)),0)

    I may update my blogpost as well with this in mind, so thanks for (inadvertently!) bringing it to my attention.

    Regards

  7. #7
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    236

    Re: Unigue list from items in each line of list

    other option by using your uploaded format.



    X5=IF(ROW(A1)=1,N5,X4&N5)
    W5= MID(CLEAN(SUBSTITUTE(SUBSTITUTE(INDEX(X:X,COUNTA(X:X)+4),",",),CHAR(10),)),(ROW(A1)-1)*6+1,6)
    V5=IF(MATCH(W5,W:W,)=ROW(),ROW(),"/")
    S5=IFERROR(INDEX(W:W,SMALL(V:V,ROW(A1))),"")
    Last edited by CAABYYC; 01-08-2016 at 11:44 AM.

  8. #8
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,706

    Re: Unigue list from items in each line of list

    XOR LX, I'm glad you spotted that. I hadn't even noticed that I was missing a value. That would have been a problem later on. Good eye!

    CAABYYC,
    I tried Option 1, concatenating all of my Comptroller Object Codes into A1. I copied your array formula into H1, did a CSE. result was nothing.
    I then tried Option 2, concatenating all of my Comptroller Object Codes into A1. I copied your helper formula into D1. Result was "/".
    I copied the index formula to F1; results was nothing.

  9. #9
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    236

    Re: Unigue list from items in each line of list

    I had deleted my post after I saw your sample format, so please disregard the one with "option 1" and "option 2"
    please do the test again by using following formula in your sample file.


    X5=IF(ROW(A1)=1,N5,X4&N5)
    W5= MID(CLEAN(SUBSTITUTE(SUBSTITUTE(INDEX(X:X,COUNTA(X:X)+4),",",),CHAR(10),)),(ROW(A1)-1)*6+1,6)
    V5=IF(MATCH(W5,W:W,)=ROW(),ROW(),"/")
    S5=IFERROR(INDEX(W:W,SMALL(V:V,ROW(A1))),"")




    Quote Originally Posted by jomili View Post
    XOR LX, I'm glad you spotted that. I hadn't even noticed that I was missing a value. That would have been a problem later on. Good eye!

    CAABYYC,
    I tried Option 1, concatenating all of my Comptroller Object Codes into A1. I copied your array formula into H1, did a CSE. result was nothing.
    I then tried Option 2, concatenating all of my Comptroller Object Codes into A1. I copied your helper formula into D1. Result was "/".
    I copied the index formula to F1; results was nothing.

  10. #10
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,706

    Re: Unigue list from items in each line of list

    CAABYYX,
    I've verified that your method works as well. Great! Now I have two ways to do it. I'll have to dive into both to figure which is more easily repeatable going forward. Thanks so much for showing this method.

  11. #11
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,706

    Re: Unigue list from items in each line of list

    In case anyone is still interested in helping, I've ALMOST go this automated, just hitting a snag on the final formula:
    Please Login or Register  to view this content.
    I'm not sure how to get VAL to countup from 1 to my VAL value, but I know I need that at both the front and back of my formula. Any help is appreciated.

  12. #12
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,706

    Re: Unigue list from items in each line of list

    Okay, I've got a first working solution (see below). Right now it only works for a single column, and the separator has to be a comma. As I find time to work on it I'll add the option for the user to put in the separator. Also would like to figure out how to do the same for a multi-column range. Any help is appreciated. Current code below:
    Please Login or Register  to view this content.

  13. #13
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,706

    Re: Unigue list from items in each line of list

    Okay, finished as far as I know to do it. To use it, select your single-column range of cells containing your separated values, run the macro, key in whatever your separator is. In the top cell next to your selected data the count of unique items in that range will appear. In the next column over the unique values appear.
    Please Login or Register  to view this content.

+ 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: 4
    Last Post: 11-05-2014, 09:38 AM
  2. Combine Line Items From Columns/List
    By BrewsterBruiser in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 07-31-2014, 10:17 AM
  3. Replies: 0
    Last Post: 04-22-2014, 11:03 PM
  4. [SOLVED] code to open/close userform based on if a list box contains list items or not
    By Nitefox in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 01-11-2014, 06:23 PM
  5. Replies: 10
    Last Post: 01-05-2013, 12:31 PM
  6. [SOLVED] Creating a grocery list in a new worksheet based on selected items in a master list
    By jacolli4 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-28-2012, 07:53 AM
  7. Replies: 3
    Last Post: 10-08-2008, 10:32 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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.08054 seconds
  • Memory Usage 9,245KB
  • Queries Executed 16 (?)
More Information
Template Usage (36):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (4)bbcode_code
  • (1)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (13)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (13)postbit_legacy
  • (13)postbit_onlinestatus
  • (13)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (49):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • postbit_attachment
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1