+ Reply to Thread
Results 1 to 8 of 8

Repeated words: remove duplicates, display word along with the number of times repeated

  1. #1
    Registered User
    Join Date
    10-21-2019
    Location
    california, USA
    MS-Off Ver
    2007
    Posts
    16

    Post Repeated words: remove duplicates, display word along with the number of times repeated

    I'm combining text write ups from multiple cells into one.
    I'm hoping to be able to remove ANY/ALL words that repeat, but keep a count of the total number of times they repeat:

    IE
    A1=apple. pear. fork. knife.
    A2=Pear. fork. mouse
    A3=dog. cat. goat. pear

    A4=Apple. Pear(x3). Fork(x2). Knife. Mouse. Dog. Cat. Goat

    Is this possible?

    Note:
    -the words wont repeat within the same source cell
    -looking to combine capitalized and non-capitalized
    -would be even better if it could identify repeated statements (separated by periods) as well,
    IE
    A1=apple. pear. fork. knife. Clean pears
    A2=Pear. fork. mouse. paint the cat green
    A3=dog. cat. goat. pear. Clean Pears. paint the cat green

    A4=Apple. Pear(x3). Fork(x2). Knife. Clean Pears(x2). Mouse. Paint the cat green(x2). Dog. Cat. Goat
    Last edited by number9; 10-21-2019 at 08:59 PM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Repeated words: remove duplicates, display word along with the number of times repeate

    If you want all the stuff in A4 to be in one cell, you will need VBA.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Repeated words: remove duplicates, display word along with the number of times repeate

    If you have small data set you can go with some helper column for achieving desired result.

    Excel 2016 (Windows) 64 bit
    A
    B
    C
    D
    E
    F
    G
    1
    2
    apple. pear. fork. knife. Clean pears apple pear fork knife Clean pears
    3
    Pear. fork. mouse. paint the cat green Pear fork mouse paint the cat green
    4
    dog. cat. goat. pear. Clean Pears. paint the cat green dog cat goat pear Clean Pears paint the cat green
    5
    Apple. Pear(X3). Fork(X2). Knife. Clean Pears(X2). Mouse. Paint The Cat Green(X2). Dog. Cat. Goat
    6
    7
    apple
    1
    apple
    8
    pear
    3
    apple. pear(x3)
    9
    fork
    2
    apple. pear(x3). fork(x2)
    10
    knife
    1
    apple. pear(x3). fork(x2). knife
    11
    Clean pears
    2
    apple. pear(x3). fork(x2). knife. Clean pears(x2)
    12
    mouse
    1
    apple. pear(x3). fork(x2). knife. Clean pears(x2). mouse
    13
    paint the cat green
    2
    apple. pear(x3). fork(x2). knife. Clean pears(x2). mouse. paint the cat green(x2)
    14
    dog
    1
    apple. pear(x3). fork(x2). knife. Clean pears(x2). mouse. paint the cat green(x2). dog
    15
    cat
    1
    apple. pear(x3). fork(x2). knife. Clean pears(x2). mouse. paint the cat green(x2). dog. cat
    16
    goat
    1
    apple. pear(x3). fork(x2). knife. Clean pears(x2). mouse. paint the cat green(x2). dog. cat. goat
    17
    18
    19
    Sheet: Sheet1

    B2=IF(COLUMNS($B2:B2)>LEN($A2)-LEN(SUBSTITUTE($A2,".",""))+1,"",TRIM(MID(SUBSTITUTE($A2,".",REPT(" ",LEN($A2))),(COLUMNS($B2:B2)-1)*LEN($A2)+1,LEN($A2))))

    Copy across H4

    B7=IFERROR(INDEX(Table,MIN(IF(COUNTIFS($B$6:B6,Table)=0,ROW(Table)-MIN(ROW(Table))+1)),MATCH(0,COUNTIFS($B$6:B6,INDEX(Table,MIN(IF(COUNTIFS($B$6:B6,Table)=0,ROW(Table)-MIN(ROW(Table))+1)),,1)),0)),"")

    confirm with control+shift+enter and drag down.

    C7=IF(B7="","",SUMPRODUCT(--(Table=B7)))
    D7=IF(C6="",B7,C6&". "&B7&IF(C7=1,"","(x"&C7&")"))

    Drag down both

    Result cell A7=PROPER(LOOKUP("zzzz",D7:D16))

    Check the attached file hope this would help you.
    Attached Files Attached Files
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Repeated words: remove duplicates, display word along with the number of times repeate

    How to install your new code
    1. Copy the Excel VBA code
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

    Please Login or Register  to view this content.

    Using space as separator

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Using dot as separator

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  5. #5
    Registered User
    Join Date
    10-21-2019
    Location
    california, USA
    MS-Off Ver
    2007
    Posts
    16

    Re: Repeated words: remove duplicates, display word along with the number of times repeate

    So, First let me apologize for not responding for all this time. The whole reason I'm trying to build this is because I'm running out of time and hoping this will help reduce paperwork in the future!

    Thank you all for the detailed responses!
    While the data entered will be on mobile, so VBA will not work immediately, the sheets will be later opened and combined on computer so the VBA that
    Sixthsense posted should be exactly what I'm looking for!

    Unfortunately, now I'm stuck trying to apply this.
    I need to make it work with the cell ranges that are dependent on other cells being filled. For the example above, the data set in column A would only be used if something was entered in column B of the corresponding row.
    (IE if using A1:A3, and B1 is blank but B2 and B3 had something entered, then only B2 and B3 would be used.)

    I'd tried to mix it into my first try using IF and ISBLANK but haven't got there yet.

    Originally I had this idea started in the following rudimentary attempt(prior to reading your responses):
    =((IF(ISBLANK(B2),"",A2))&"."&(IF(ISBLANK(B3),"",A3))&"."&(IF(ISBLANK(B4),"",A4))&"."&(IF(ISBLANK(B5),"",A5))&"."&(IF(ISBLANK(A6),"",H6))&"."&(IF(ISBLANK(B7),"",A7)))

    A range like the one in the VBA posted by Sixthsense would be much better though (to allow for additional rows of data to be entered easily). Also the idea I had returns multiple "."s when cells are left blank.

    Thanks again in advance and I'm so sorry if I'm delayed in getting back to you!

  6. #6
    Registered User
    Join Date
    10-21-2019
    Location
    california, USA
    MS-Off Ver
    2007
    Posts
    16

    Re: Repeated words: remove duplicates, display word along with the number of times repeate

    Thank you! Please see thread reply below!

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Repeated words: remove duplicates, display word along with the number of times repeate

    Replace this

    Please Login or Register  to view this content.
    With

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

  8. #8
    Registered User
    Join Date
    10-21-2019
    Location
    california, USA
    MS-Off Ver
    2007
    Posts
    16

    Re: Repeated words: remove duplicates, display word along with the number of times repeate

    Quote Originally Posted by :) Sixthsense :) View Post
    Replace this

    Please Login or Register  to view this content.
    With

    Please Login or Register  to view this content.

    Thank you! works great!

+ 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: 1
    Last Post: 05-18-2018, 06:38 AM
  2. Need help copying rows that contain duplicates based on how many times they are repeated
    By bethanywalters in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-15-2015, 10:00 PM
  3. [SOLVED] Number of times same text is repeated
    By fedfed in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-09-2014, 07:43 AM
  4. Replies: 4
    Last Post: 04-17-2012, 03:24 PM
  5. remove the repeated words/numbers in a cell
    By darkhorse4321 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-24-2011, 02:17 AM
  6. Replies: 3
    Last Post: 07-14-2005, 08:53 AM
  7. find out number repeated max. times in column.
    By vimivijay in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-10-2005, 06:59 AM

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