+ Reply to Thread
Results 1 to 6 of 6

Display Number of Occurances from Unique List Against Bulk List

  1. #1
    Registered User
    Join Date
    11-01-2006
    Posts
    5

    Display Number of Occurances from Unique List Against Bulk List

    Hi All

    I have a couple of problems that are beyond my excel knowledge; I would much appreciate it if any could help me on this?

    I have one bulk list and one unique list that I have removed the duplicates out of. Now what I would like to do I compare my unique list to the bulk list and in another column display how many instances of each word in the unique column are in the bulk list.

    For instance next to each word in the unique list I would like to it display a number, so a 3 next to my unique list would mean that particular word was in the bulk list three times?

    Can anyone figure out a method to achieve this?

    Thanks again Marx

  2. #2
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Occurances

    Take alook at the example attached.
    Let me know if this helps.
    modytrane
    Attached Files Attached Files

  3. #3
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Count occurrences of a word in a range.

    With
    A2:A1000 containing individual words (duplicates and blanks allowed)
    and
    B2:B30 containing a list of unique words

    This formula returns the count of the B2 words that are in A2:A1000
    Please Login or Register  to view this content.
    Copy C2 down as far as you need.

    Does that help?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Registered User
    Join Date
    11-01-2006
    Posts
    5
    Wow thats great, seems to be doing the job, one thing I cant figure out is how to expand the furthest left complete list box. When I edit the unique list formula it puts a blue box around your list, how do I expand this list as mine is a few thousand long ?

    Thanks Marx

  5. #5
    Registered User
    Join Date
    11-01-2006
    Posts
    5
    Quote Originally Posted by Ron Coderre View Post
    With
    A2:A1000 containing individual words (duplicates and blanks allowed)
    and
    B2:B30 containing a list of unique words

    This formula returns the count of the B2 words that are in A2:A1000
    Please Login or Register  to view this content.
    Copy C2 down as far as you need.

    Does that help?
    You legend, that has done it

  6. #6
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Occurances

    Ron's solution is simple enough, as long as it helps you.

    My example creates unique list automatically. So if you add few items to your master list, all unique items will be added and occurances calculated for you.

    You can define your master list range in D5 and D6. Also, change the input range for "rngUserInput". Follow instructions on the attached sheet.

    Good Luck,

    modytrane
    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)

Similar Threads

  1. Selecting a number of requirements from a large list
    By DentonHTHS in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-20-2008, 09:15 PM
  2. How to Display List in a single row
    By p_dhoke in forum Excel General
    Replies: 8
    Last Post: 03-01-2007, 03:18 AM
  3. Return a list of unique matches
    By NickySA in forum Excel General
    Replies: 10
    Last Post: 01-24-2007, 04:39 AM
  4. ADVANCED: Extracting unique records
    By UKNOWN in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-05-2007, 06:12 PM
  5. Unique List from table
    By snoops in forum Excel General
    Replies: 5
    Last Post: 12-10-2006, 12:04 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