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..
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 Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
try this, see the attachment. What do you want this for (out of interest)
Undergraduate didn't say anything about the sample having 10 rowsOriginally Posted by Leith Ross
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
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.
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 08:47 PM.
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 08:47 PM.
many thanks, that works perfectly~!
no problem, happy to help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks