+ Reply to Thread
Results 1 to 10 of 10

Find duplicate keywords, and list maximum

Hybrid View

  1. 02-13-2010, 03:15 PM #1
    undergraduate
    undergraduate is offline
    Forum Contributor
    Join Date
    01-28-2010
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    105

    Find duplicate keywords, and list maximum

    Hello,
    I am trying to create a macros that does the following.
    I have rows of 10 keywords. I want the macros to compare the first row against all the rows except itself, then compare the second row against all the rows except itself, the third row against all the rows except itself, basically looking for duplicates...It will then record in a column for each respective row, the max # of duplicates found with another row..

    For instance lets say I was looking at the 15th row, and it looked for duplicates elsewhere. It only found duplicates in the 4th row and the 5th row. In the fourth row, there were 5 keywords common, and in the 5th row there were 7 keywords common. Therefore the max # of common words is 7 for the 15th row, and that is what is recorded.

    In addition the number of rows will probably vary it could range from 1 to 500. I included an excel spreadsheet that has visually speaking how this could work, maybe using COUNTIF. Basically it counts the # of common keywords for one row with all the other rows, pastes the count #s to the right, then finds the max of those count #s, copies the max into another column, then clears the count #s, and looks up the next row, repeats the same process. It is easier probably if you look at the attached spreadsheet to get an idea of what i am talking about..
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-13-2010, 05:58 PM #2
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Find duplicate keywords, and list maximum

    Hello undergraduate,

    Please reread your post and edit it for accuracy and completeness. Your sample workbook has 5 rows and 10 columns, not 10 rows. Rows are horizontal and columns are vertical. Please post a workbook that is based on the actual data and not some hypothetical model. Your 15 row example doesn't follow with the posted workbook sample.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)
    Reply With QuoteRegister To Reply
  3. 02-13-2010, 07:13 PM #3
    Mallycat
    Mallycat is offline
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: Find duplicate keywords, and list maximum

    Quote Originally Posted by Leith Ross View Post
    Your sample workbook has 5 rows and 10 columns, not 10 rows.
    Undergraduate didn't say anything about the sample having 10 rows
    Reply With QuoteRegister To Reply
  4. 02-13-2010, 09:14 PM #4
    undergraduate
    undergraduate is offline
    Forum Contributor
    Join Date
    01-28-2010
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    105

    Re: Find duplicate keywords, and list maximum

    I believe my data and explanation is correct, but I will rephrase it so it is easier to understand.

    The # of rows will vary. Thus i gave a small subset of data. I will see the attached macros to see if it works before reposting
    Reply With QuoteRegister To Reply
  5. 02-13-2010, 07:11 PM #5
    Mallycat
    Mallycat is offline
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: Find duplicate keywords, and list maximum

    try this, see the attachment. What do you want this for (out of interest)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 02-13-2010, 09:20 PM #6
    undergraduate
    undergraduate is offline
    Forum Contributor
    Join Date
    01-28-2010
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    105

    Re: Find duplicate keywords, and list maximum

    Mallycat this is fantastic thanks so much.
    Basically this will be used for comparing keywords found in a listing of research papers. To try to assess which papers are the most interrelated. I obviously included filler data, just to protect the data. But once again, thank you so much, this works perfectly.
    Reply With QuoteRegister To Reply
  7. 02-13-2010, 09:39 PM #7
    undergraduate
    undergraduate is offline
    Forum Contributor
    Join Date
    01-28-2010
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    105

    Re: Find duplicate keywords, and list maximum

    I had one more question:
    The macros you supplied me with, is there anyway it can ignore blank cells(and not have them included in count)? For instance if there is a row with two keywords. and another row with the same two keywords. The max count for both rows appears as 66. the anomaly appears to only take place when there are blank cells.
    was wondering if you knew what alteration to make to the previous macros to prevent this. thanks.
    Last edited by undergraduate; 02-13-2010 at 09:47 PM.
    Reply With QuoteRegister To Reply
  8. 02-13-2010, 09:44 PM #8
    Mallycat
    Mallycat is offline
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: Find duplicate keywords, and list maximum

    Yes, you need to add a test on the source cell to see if it is blank, then proceed only if it is not blank. You can ignore the destCell.

    So just add the 2 bold lines into the macro as follows

    For Each sourceCell In mySourceList
    If sourceCell <> "" Then                       
           For Each destCell In myDestList
                  If destCell = sourceCell Then
                          myFound = myFound + 1
                  End If
            Next destCell
      End If
Next sourceCell
    Last edited by Mallycat; 02-13-2010 at 09:47 PM.
    Reply With QuoteRegister To Reply
  9. 02-13-2010, 09:50 PM #9
    undergraduate
    undergraduate is offline
    Forum Contributor
    Join Date
    01-28-2010
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    105

    Re: Find duplicate keywords, and list maximum

    many thanks, that works perfectly~!
    Reply With QuoteRegister To Reply
  10. 02-13-2010, 10:29 PM #10
    Mallycat
    Mallycat is offline
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: Find duplicate keywords, and list maximum

    no problem, happy to help
    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