# Sorting, associating or linking sorted values • ### Question

• I have a Word document containing unique words. I am extracting character information from this collection of words (like number of a, b, c, etc). I am able to obtain the counts and able to bubble sort the totals; however, I don’t know how to associate the counts back to the associated letter. For example, I may have 200 a, 50 b, 75 c and the bubble sort produces:

50

75

100

So, how would I associate 50 to the “b”, 75 to the “c” and 100 to the “a”?  I have considered a 2 dimensional array but I don’t know how to make the association.

Tuesday, July 3, 2012 2:05 AM

• Try

```...
For i = 1 To 26
strLetters(i) = Chr(96 + i)
Next i
For i = 1 To 25 ' **** note the difference ****
For j = i + 1 To 26 ' *** different too ***
If lngLtrCount(i) < lngLtrCount(j) Then
lngTemp = lngLtrCount(i)
lngLtrCount(i) = lngLtrCount(j)
lngLtrCount(j) = lngTemp
strTemp = strLetters(i)
strLetters(i) = strLetters(j)
strLetters(j) = strTemp
End If
Next j
Next i
For i = 1 To 26
list = list & vbCrLf & strLetters(i) & "  " & lngLtrCount(i)
Next i
MsgBox list
End Sub```

Regards, Hans Vogelaar

• Marked as answer by Thursday, July 5, 2012 8:17 PM
Thursday, July 5, 2012 7:26 PM

### All replies

• You can use two arrays: one for the counts and one for the characters, and sort them simultaneously:

```Sub SortEm()
Dim lngCounts(1 To 26) As Long
Dim strLetters(1 To 26) As Long
Dim i As Long
Dim j As Long
Dim lngTemp As Long
Dim strTemp As Long
' Fill lngCounts with character counts
' ...
' Fill strLetters with letters of the alphabet
For i = 1 To 26
strLetters(i) = Chr(64 + i)
Next i
' Sort both arrays simultaneously
For i = 1 To 25
For j = i + 1 To 26
If lngCounts(i) > lngCounts(j) Then
lngTemp = lngCounts(i)
lngCounts(i) = lngCounts(j)
lngCounts(j) = lngTemp
strTemp = strLetters(i)
strLetters(i) = strLetters(j)
strLetters(j) = strTemp
End If
Next j
Next i
' Display result
For i = 1 To 26
Debug.Print strLetters(i), lngCounts(i)
Next i
End Sub```

Regards, Hans Vogelaar

Tuesday, July 3, 2012 5:38 AM
• Getting errors coming out of the bubble sort.

Test words:  bac bde bafc ghic

Here are the counts passed to lngLtrCount(i) from the code checking the words.

2a, 3b, 3c, 1d, 1e, 1f, 1g, 1h, 1i, 0j, 0k,… rest all zeros.

Note at this point all counts are correct (of course the letters were not included with the counts)

lngLtrCount(i) is then passed to a bubble sort that produces:

b 3

c 3

d 2

f 1

g 1

h 1

i 1

j 0

Remaing values are 0

Notice the 3<sup>rd</sup> position is d2 should be a2.  Also note the 10<sup>th</sup> position does not have a letter associated to the number.

Below is the bubble sort which includes Hans suggestion to add a second array for the letter matching-up to the counts. It seems straight forward but for the last two days I have not be able to determine why the above error is produced. Not that it matters but I am using MS Word 2002 SP3.

‘ ...

Dim i As Long

Dim j As Long

Dim lngTemp As Long

Dim strTemp As String

Dim list As Variant

Dim strLetters(26) As String

For i = 1 To 26

strLetters(i) = Chr(i + 96)

For j = 1 To 26

If lngLtrCount(i) > lngLtrCount(j) Then

lngTemp = lngLtrCount(i)

lngLtrCount(i) = lngLtrCount(j)

lngLtrCount(j) = lngTemp

strTemp = strLetters(i)

strLetters(i) = strLetters(j)

strLetters(j) = strTemp

End If

Next j

Next i

For i = 1 To 26

list = list & vbCrLf & strLetters(i) & "  " & lngLtrCount(i)

Next i

MsgBox list

End Sub

Does anyone see where the problem occurs?

Thursday, July 5, 2012 6:03 PM
• Try

```...
For i = 1 To 26
strLetters(i) = Chr(96 + i)
Next i
For i = 1 To 25 ' **** note the difference ****
For j = i + 1 To 26 ' *** different too ***
If lngLtrCount(i) < lngLtrCount(j) Then
lngTemp = lngLtrCount(i)
lngLtrCount(i) = lngLtrCount(j)
lngLtrCount(j) = lngTemp
strTemp = strLetters(i)
strLetters(i) = strLetters(j)
strLetters(j) = strTemp
End If
Next j
Next i
For i = 1 To 26
list = list & vbCrLf & strLetters(i) & "  " & lngLtrCount(i)
Next i
MsgBox list
End Sub```

Regards, Hans Vogelaar

• Marked as answer by Thursday, July 5, 2012 8:17 PM
Thursday, July 5, 2012 7:26 PM
• Perfect!!!  Works Great. Problem Solved.

I think I now understand why 25, instead of 26, is used for the i index in the sort loop. The last entry to be tested is the smallest because has percolated to the bottom of the list.

Hans, I appreciate your time and experience.

THANKS.

Thursday, July 5, 2012 8:17 PM