How to list all positions of a variable in a list?

Question

I have a large list of text strings. I know that some strings occur more than once in this list. The frequency with which the strings occur are in the next column.

Now, let's say the text string "hello" occurs 17 times. How would I get all positions of this text within the list?

Any help is appreciated.

Answer 1

Pieter,

Check out this post of Stack Overflow.

I have also attempted to adjust their sample code to match your case, I'm sorry though that this may not compile, I cannot test it at the moment and my VBA is a little rusty, but you get the idea.

Function CheckValues1(byval columnToCheck as Integer, byval stringToFind as String) as Integer()
    dim rwIndex As Integer
    dim index as Integer
    dim occurrences() As Integer
    index = 0

    For rwIndex = 0 to Cells(Rows.Count, columnToCheck).End(xlUp).Row

       ReDim Preserve occurrences(0 to index)

       If Cells(rwIndex, columnToCheck).Value = stringToFind Then
           occurrences(index) = rwIndex
           index = index + 1
       End If

    Next rwIndex

    dim output as String
    output = output & (Len(occurrences)-1) & " occurrences found." & vbcrlf & vbcrlf
    For index = 0 to LBound(occurrences)

        output = output & "Line: " & index & vbcrlf

    Next index
    MsgBox output

End Sub

This function should return an array of all of the occurances for which you can handle however your like. You just need to pass in the String your are looking for and the column number to search within.

I hope this helps!

Tony

Answer 2

Asuming that all your strings are in one column, you can add a second column with their position (line number) and do a pivot table across the strings displaying the counts.

In order to get the position(s) of a string you are interested in, you use the pivot table's drill-down function (double click on the count next to a string) which will create a new sheet with all detail records - and the position will be displayed

Hope that helps

Good lock

EDIT after comments exchanged:

I would go for a solution that scans your data only 1 time and not recursively, copying values to a second sheet:

for each string in sourcetable
   if found in targettable
      increase targettable.counter by 1 (remark: in column_2)
   else
      put sourcetable.string at end of targettable
      put "1" in targettable.counter (remark: occurence = 1 in column_2)
   endif
   put sourcetable.index into targettable.column(counter+2)
next

so far the metacode .... do you need more help to actually code this in VBA?

EDIT 2

OK .... made a quick & dirty one ....

Sub CountString()
Dim S As Range, T As Range, Idx As Long, Jdx As Long

    Set S = Worksheets("Sheet1").[A2] ' first row is header
    Set T = Worksheets("Sheet2").[A2] ' first row is header
    Idx = 1

    Do While S(Idx, 1) <> ""
        Jdx = FindInRange(T, S(Idx, 1))
        If T(Jdx, 1) = "" Then
            T(Jdx, 1) = S(Idx, 1)
            T(Jdx, 2) = 1
            T(Jdx, 3) = Idx
        Else
            T(Jdx, 2) = T(Jdx, 2) + 1
            T(Jdx, T(Jdx, 2) + 2) = Idx
        End If
        Idx = Idx + 1
    Loop
End Sub

Function FindInRange(R As Range, A As String) As Long
Dim Idx As Long

    Idx = 1
    Do While R(Idx, 1) <> ""
        If R(Idx, 1) = A Then
            Exit Do
        End If
        Idx = Idx + 1
    Loop
    FindInRange = Idx
End Function

tested with 500 words from "Lorem ipsum" - takes below 1 second, output in sheet_2 looks like

String     Count   Position ...                                                        
Lorem        1        1                                                        
ipsum        6        2        45        65       232       323       462                
dolor        5        3        42       214       321       335                        
sit          6        4        79       148       249       295       415                
amet         6        5        80       149       250       296       416                
consectetur  8        6       117       288       298       396       457       473        486
adipiscing   3        7       180       402                                        

Hope that helps