# list box calculations item / item count / of total count for item

using vb6 I have this in a list box

 12345467 12345467 12345467 12345555 12345467 12347777 12348888 12345555 12346677 12345467 12346677 and need to convert it to this 12345467/1/5 12345467/2/5 12345467/3/5 12345555/1/2 12345467/4/5 12347777 12348888 12345555/2/2 12346677/1/2 12345467/5/5 12346677/2/2

Visual Basic Classic

Last Comment
Martin Liss
Éric Moreau

is this data coming from SQL Server? If that's the case, Row_Number() and COUNT are your friends
Jim Horn

Knee-jerk reactions...
• The slash / character will force these to be character values and not numeric values, which means it will sort per character rules.
• Explain how '12348888' sorts before '12345555/2/2', as character sorting would have handled that sample set up to that point.  Same goes for '12346677/1/2' and '12346677/2/2 ' having the value '12345467/5/5' in between.   Is there another column that sort order rules require, such as a timestamp?
Martin Liss

Try this in a command button.

``````Private Sub Command1_Click()
Dim colLI As New Collection
Dim lngEntry As Long
Dim lngList As Long
Dim lngMatch As Long
Dim intCount As Integer
Dim intTot As Integer

For lngEntry = 0 To List1.ListCount - 1
On Error Resume Next
On Error GoTo 0
Next

For lngEntry = 1 To colLI.Count
' How many match
For lngMatch = 1 To colLI.Count
intTot = 0
For lngList = 0 To List1.ListCount - 1
If List1.List(lngList) = colLI(lngEntry) Then
intTot = intTot + 1
End If
Next
Next
' Markup with counts
If intTot > 1 Then
intCount = 0

For lngList = 0 To List1.ListCount - 1
If InStr(1, List1.List(lngList), colLI(lngEntry)) > 0 Then
intCount = intCount + 1
List1.List(lngList) = colLI(lngEntry) & "/" & intCount & "/" & intTot
End If
Next
End If
Next

End Sub
``````

I suggest you use a multi-column listbox instead if the slashes.
rjef

Liss

this
1
1
1
2
2

came out
1/1/3
2/1/2
1/3/3
2/2/2
2/3/2

should be
1/1/3
1/2/3
1/3/3
2/1/2
2/2/2
rjef

Knee-jerk reactions..
it doesn't need to be sorted,  just counted for x of y
Martin Liss

``````Private Sub Command1_Click()
Dim colLI As New Collection
Dim lngEntry As Long
Dim lngList As Long
Dim lngMatch As Long
Dim intCount As Integer
Dim intTot As Integer
Dim intCounts() As Integer

For lngEntry = 0 To List1.ListCount - 1
On Error Resume Next
On Error GoTo 0
Next

ReDim intCounts(1 To colLI.Count)
For lngEntry = 1 To colLI.Count
' How many match
intTot = 0
For lngList = 0 To List1.ListCount - 1
If List1.List(lngList) = colLI(lngEntry) Then
intTot = intTot + 1
End If
Next
intCounts(lngEntry) = intTot
Next
' Markup with counts
For lngEntry = 1 To colLI.Count
intCount = 0
For lngList = 0 To List1.ListCount - 1
If List1.List(lngList) = colLI(lngEntry) Then
intCount = intCount + 1
List1.List(lngList) = colLI(lngEntry) & "/" & intCount & "/" & intCounts(lngEntry)
End If
Next
Next

End Sub
``````
rjef

don't mean to be picky but i do not need single number to be formated    ie if there is a number that is just listed only once then it would not need the  x/1/1
should i just loop through again and remove the 1/1?
Martin Liss

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Martin Liss

Was that the answer you needed?
Visual Basic Classic

Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.

165K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY