now more dictionary oriented (this is exemplary why/when you should use dictionaries)
Sub M_snb()
sn = Split("abe abi eve cath ivy jan dee fay bea hope gay _bob cath hope abi dee eve fay bea jan ivy gay _col hope eve abi dee bea fay ivy gay cath jan _dan ivy fay dee gay hope eve jan bea cath abi _ed jan dee bea cath fay eve abi ivy hope gay _fred bea abi dee gay eve ivy cath jan hope fay _gav gay eve ivy bea cath abi dee hope jan fay _hal abi eve hope fay ivy cath jan bea gay dee _ian hope cath dee gay bea abi fay ivy jan eve _jon abi fay jan gay eve bea dee cath ivy hope", "_")
sp = Split("abi bob fred jon gav ian abe dan ed col hal _bea bob abe col fred gav dan ian ed jon hal _cath fred bob ed gav hal col ian abe dan jon _dee fred jon col abe ian hal gav dan bob ed _eve jon hal fred dan abe gav col ed ian bob _fay bob abe ed ian jon dan fred gav col hal _gay jon gav hal fred bob abe col ed dan ian _hope gav jon bob abe ian dan hal ed col fred _ivy ian col hal gav fred bob abe ed jon dan _jan ed hal gav abe bob jon col ian fred dan", "_")
Set d_00 = CreateObject("scripting.dictionary")
Set d_01 = CreateObject("scripting.dictionary")
Set d_02 = CreateObject("scripting.dictionary")
For j = 0 To UBound(sn)
d_00(Split(sn(j))(0)) = ""
d_01(Split(sp(j))(0)) = ""
d_02(Split(sn(j))(0)) = sn(j)
d_02(Split(sp(j))(0)) = sp(j)
Next
Do
For Each it In d_00.keys
If d_00.Item(it) = "" Then
st = Split(d_02.Item(it))
For jj = 1 To UBound(st)
If d_01(st(jj)) = "" Then
d_00(st(0)) = st(0) & vbTab & st(jj)
d_01(st(jj)) = st(0)
Exit For
ElseIf InStr(d_02.Item(st(jj)), " " & st(0) & " ") < InStr(d_02.Item(st(jj)), " " & d_01(st(jj)) & " ") Then
d_00(d_01(st(jj))) = ""
d_00(st(0)) = st(0) & vbTab & st(jj)
d_01(st(jj)) = st(0)
Exit For
End If
Next
End If
Next
Loop Until UBound(Filter(d_00.items, vbTab)) = d_00.Count - 1
MsgBox Join(d_00.items, vbLf)
End Sub
Bookmarks