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

Sub aaa()
  Dim OutSH As Worksheet
  Set OutSH = Sheets("Sheet2")
  
  OutSH.Cells.ClearContents
  Range("A1", Cells(1, Columns.Count).End(xlToLeft)).Copy Destination:=OutSH.Range("B1")
  
  lastrow = Cells.Find(what:="*", after:=Range("A1"), searchdirection:=xlPrevious, searchorder:=xlByRows).Row
  For Each ce In Range("A2", Cells(lastrow, Cells(1, Columns.Count).End(xlToLeft).Column))
    If Not IsEmpty(ce) Then
    If WorksheetFunction.CountIf(OutSH.Range("A:A"), ce.Value) = 0 Then
      OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = ce.Value
      Set findrow = OutSH.Range("A:A").Find(what:=ce.Value)
      Set findcol = OutSH.Range("1:1").Find(what:=Cells(1, ce.Column))
      OutSH.Cells(findrow.Row, findcol.Column).Value = OutSH.Cells(findrow.Row, findcol.Column).Value + 1
    Else
      Set findrow = OutSH.Range("A:A").Find(what:=ce.Value)
      Set findcol = OutSH.Range("1:1").Find(what:=Cells(1, ce.Column))
      OutSH.Cells(findrow.Row, findcol.Column).Value = OutSH.Cells(findrow.Row, findcol.Column).Value + 1
    End If
    End If
    
  Next ce
      
  With OutSH
    .Rows("2:" & .Cells(Rows.Count, 1).End(xlUp).Row).Sort key1:=.Range("A2"), order1:=xlAscending
    totrow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    totcol = .Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Column
    
    .Cells(totrow, 2).Formula = "=sum(B2:B" & totrow - 1 & ")"
    .Cells(totrow, 2).AutoFill Destination:=.Range(.Cells(totrow, 2), .Cells(totrow, totcol - 1))
    .Cells(2, totcol).Formula = "=sum(B2:" & .Cells(2, totcol - 1).Address(rowabsolute:=False) & ")"
    .Cells(2, totcol).AutoFill Destination:=.Range(.Cells(2, totcol), .Cells(totrow - 1, totcol))
    
    
  End With
  
End Sub

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