+ Reply to Thread
Results 1 to 3 of 3

Parsing and Counting Text Data?

  1. 04-20-2011, 02:28 PM #1
    guyfromva
    guyfromva is offline
    Registered User
    Join Date
    04-20-2011
    Location
    Washington, DC
    MS-Off Ver
    Excel 2007
    Posts
    1

    Parsing and Counting Text Data?

    Any thoughts on how I can parse and count the frequency of data in this format:


    Control Names
    123 Pryor, Mark; Bingaman, Jeff; Portman, Rob
    345 Portman, Rob; Warner, Mark; Kyl, John
    567 Pryor, Mark; Inhofe, Jim
    324 Reid, Harry
    234 Corker, Bob
    765 Brown, Sherrod; Schumer, Chuck; Reid, Harry

    to this...

    Frequency Name
    2 Pryor, Mark
    1 Bingaman, Jeff
    2 Portman, Rob
    1 Warner, Mark
    1 Kyl, John
    1 Inhofe, Jim
    2 Reid Harry
    1 Corker, Bob
    1 Brown, Sherrod
    1 Schumer, Chuck

    (formating is weird, but the numbers in each of the 2 examples are contained in a separate column)

    Thanks!
    Last edited by guyfromva; 04-20-2011 at 02:32 PM.
    Reply With QuoteRegister To Reply
  2. 04-20-2011, 04:28 PM #2
    Cutter
    Cutter is offline
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Parsing and Counting Text Data?

    I would try Data - Text to Columns on the original list using the ; as the delimiter.
    That will give a series of columns with names.
    You can then sort each of those columns and then cut paste the 2nd & 3rd, etc. below the first.
    Make a copy of that new list and on the copy do a Data - Remove Duplicates.
    Now you have a list of all names and a list of unique names.
    Use a COUNTIF() using the All Names list as the range and the Unique List as the search values.
    Reply With QuoteRegister To Reply
  3. 04-20-2011, 04:58 PM #3
    Palmetto
    Palmetto is offline
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Parsing and Counting Text Data?

    See attached. Adjust code to suit.

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

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1