Columns with the most similar data

[ocp]

This is my first time using Excel for much besides sorting data, and I figure this has got to be possible, but I have no idea how.

I have a data set that looks something like this:
a| b | c | d
33 | 102 | 1 | 1
34 | - | 2 | 2
119 | - | 33 | 6
- | - | 119 | 33
- | - | - | 119
Where the column header represents a word, and the numbers represent the cities where the word is found. The number of cities connected to a word varies from 1 to 50+. There's about a thousand words.

I'm trying to find which words share the greatest number of cities - that is, the columns with the most cells in common. Ideally, I'd like to calculate this at different thresholds (columns with 2+ cells in common, 3+, etc.)

It'd also be cool to do additional queries, like "for columns that share cell X, what other cells do they share?" But that might be too much to ask for...

Any help would be wonderful!

[rylo]

Hi

Don't know if this will help or not but here goes.

Create a new sheet in the workbook called sheet2. Put the code

Please Login or Register  to view this content.

into a general module, select your data sheet and run.

This will build you a matrix of the words and citites, and provide counts for the cities for a word, and the number of words for a city.

HTH

rylo

[ocp]

Hi Rylo,

Thanks a ton for your help! I made the module and tried running it (Tools > Macros > (select aaa) > Run) and I get Comple error: user-defined type not defined, and it highlights Sub aaa() in the code. Am I running it wrong? (I've never used macros before.)

Thanks again!

[rylo]

Hi

This was running OK for me.

rylo