+ Reply to Thread
Results 1 to 4 of 4

Find/list unique values in range. Lookup A cell on same line(s) as value(s). Big data set.

  1. #1
    Registered User
    Join Date
    10-03-2012
    Location
    Near Tampa, Florida
    MS-Off Ver
    | Work: 2007 | Home: 2013 |
    Posts
    25

    Question Find/list unique values in range. Lookup A cell on same line(s) as value(s). Big data set.

    I'm looking for a method to flip my information around. Instead of Cell A listing corresponding words in column B, I've been tasked with finding all the unique words in Column B and listing all the A cells which correspond.

    This is what I am working with below. I have DOCUMENT names running down column A. I have 25,000 lines. The DOCUMENT cells are document names in my real world. Column B cells are technically "tags" for my documents. These tags are separated with commas. These can be delimited across the row if easier to work with.

    DOCUMENT-000001|dog,cat,ball,purple,9er,TEST 123,thanks
    DOCUMENT-000002|dog,purple,thanks
    DOCUMENT-000007|cat,ball,9er,TEST 123


    This is how I will ultimately need the data presented. Column A is all the unique values which were previously comma delimited in the above Column B. Then next to each of those tags I need to list each of the document names which were in the previous Column A, in the corresponding cell of all the tags.

    dog|DOCUMENT-000001,DOCUMENT-000002
    cat|DOCUMENT-000001,DOCUMENT-000007
    ball|DOCUMENT-000001
    purple|DOCUMENT-000001,DOCUMENT-000002,DOCUMENT-000007
    9er|DOCUMENT-000001,DOCUMENT-000007
    TEST 123|DOCUMENT-000001,DOCUMENT-000007
    thanks|DOCUMENT-000001,DOCUMENT-000002


    Manually parsing this data isn't too practical for me. I have 25,000 lines and, if delimited, there are hundreds of unique tags. Some rows have every single tags, some have half of those tags, some only have one or two which makes manual sorting so daunting.

    I'm looking for some VBA help. I've had great experiences here in the past learning VBA which I have used daily in my work. I'm looking for anything which can help me. Sample code, a link to a website explaining how to perform one component of the overall macro, or even if you're to say "try this" and say the name of something for me to google. I appreciate any of your help.

    I'll respond quickly if you require any additional information or if you need me to do something. I've attached an example xlsx. It's just the above information on two sheets. Thank you for your time!
    Attached Files Attached Files

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

    Re: Find/list unique values in range. Lookup A cell on same line(s) as value(s). Big data

    Try
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-03-2012
    Location
    Near Tampa, Florida
    MS-Off Ver
    | Work: 2007 | Home: 2013 |
    Posts
    25

    Re: Find/list unique values in range. Lookup A cell on same line(s) as value(s). Big data

    Good lord, that's beautiful: both how it performs and how it looks written out. Thank you, jindon.

    Is there anything I can add or edit which would separate the tags with commas instead of spaces in the finished product? I can surely do some manual search and replace given my data set.

    Additionally, would you or anyone be able to give me a super quick explanation of how it works?

    Even if you don't have the time. Thank you again. You've saved me countless hours!

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

    Re: Find/list unique values in range. Lookup A cell on same line(s) as value(s). Big data

    Miised..
    Please Login or Register  to view this content.
    1) Load whole data in an array "a" to speed up the process.
    2) Prepare out put array "b".
    3) loop through array "a" and loop each elements of 2nd column delimited by a comma.
    4) Set the position for out put array "b" for each unique element and place in 1st col in "b" and 2nd col from 1st col in "a".
    5) If found the element that is already assigned to the output array, it adds 1st col in "a" to 2nd col in "b".

    HTH

+ 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. Check range for values and return list of found unique values
    By kian82 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-13-2017, 04:45 AM
  2. Find unique values from a range using formula?
    By djarcadian in forum Excel General
    Replies: 8
    Last Post: 02-14-2015, 11:12 PM
  3. Dropdown list always shows unique values from a column based on lookup value
    By swood15 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-22-2014, 12:46 PM
  4. [SOLVED] Formula or VBA - Comparison CSV Values between 3 cells for used range. Find unique values.
    By grphillips in forum Excel Programming / VBA / Macros
    Replies: 36
    Last Post: 06-09-2014, 02:12 PM
  5. [SOLVED] find the unique values and new entries from list 1-6
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-16-2013, 02:28 AM
  6. Replies: 8
    Last Post: 12-08-2008, 12:54 PM
  7. Find Unique Values In A Range
    By nevi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-10-2008, 04:40 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