+ Reply to Thread
Results 1 to 6 of 6

Extract unigue phrases from range of strings and count them

  1. #1
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,094

    Extract unigue phrases from range of strings and count them

    Hi all,

    I have the following problem and I cannot find a way to succeed it. I have a column of data that contains strings of text separated by comma or line breaks. What I need to do is to extract all the unique phrases from that column to another sheet already created and count the munber of appearences in the column. I've attached a sample file with expected results.

    Many thanks in advance,
    Paul
    Attached Files Attached Files

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Extract unigue phrases from range of strings and count them

    I misread before
    this should be ok

    Please Login or Register  to view this content.

    D
    E
    1
    Comments Count
    2
    Break-out room Layou
    1
    3
    Catering order comme
    1
    4
    Delivery Location
    3
    5
    Duration in Hours
    1
    6
    Event Service Level
    1
    7
    Event Status
    2
    8
    Joining instruction
    1
    9
    Min Participants
    1
    10
    Needed equipment
    1
    11
    Optimum Registration
    2
    12
    Primary Trainer Vend
    34
    13
    Provider comments
    4
    14
    Room – Comments
    2
    15
    Schedule description
    1
    16
    Seat based/Session b
    1
    17
    Secondary Trainer Ve
    3
    18
    Service Level
    1
    19
    Trainer comments
    33
    20
    Training material co
    9
    21
    Type of Training Mat
    6
    22
    Type of catering
    1
    23
    secondary trainer ve
    5
    24
    trainer comments
    1


    Edit:
    I forgot to add info: done with PowerQuery (add-in for Ex2013)
    Last edited by sandy666; 05-17-2018 at 06:28 AM.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,094

    Re: Extract unigue phrases from range of strings and count them

    Thank you for your response sandy, but I am not entirely sure if it will work. I need to have a column of single phrases without commas and the count for each one. and this is part of something bigger and has to be reportable(kinda of). and I am not familiar with power query

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Extract unigue phrases from range of strings and count them

    refresh thread and see again. no commas and no CR

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,191

    Re: Extract unigue phrases from range of strings and count them

    Please Login or Register  to view this content.
    see the attached file
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,094

    Re: Extract unigue phrases from range of strings and count them

    Actually I managed to found a solution made by a code and a formula found below, but your solution is way better nflsales. Thank you.

    Please Login or Register  to view this content.
    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. [SOLVED] Help needed with VBA code to extract Phrases/ords from text string
    By rv02 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-10-2015, 09:08 AM
  2. [SOLVED] VBA code required to Extract the content between 2 words/Phrases
    By mchilapur in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-13-2015, 01:20 AM
  3. [SOLVED] How to extract strings from hyphen separated strings
    By sharathnarayanan in forum Excel General
    Replies: 5
    Last Post: 11-22-2014, 10:01 AM
  4. [SOLVED] Count the number of unigue numbers based on number in another column.
    By ksmith4809 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-27-2013, 09:11 AM
  5. Compare lists of phrases in two workbooks and import only missing phrases
    By Fireblejd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-10-2011, 06:59 AM
  6. Custom Function: Count number of strings in range?
    By milz326 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-25-2010, 04:04 PM
  7. how to count phrases in a cell
    By rcorkum in forum Excel General
    Replies: 1
    Last Post: 06-26-2010, 09:34 PM

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