[RESOLVED] Change the way the search works

[debeerj@hotmail.com]

Hello

Before I ask my question, I am not a programmer but understand little things to make my work easier, please be patient.

1) The input data is arranged in columns, and the total rows can vary.

At the moment the program starts at the first row and looks for a match from row 2 on wards for each item in row1 . It looks at each column on each row until it finds a match. Its will loop through until EOF. if no match then it returns a 0

input could look like this data:

row1  200 390 400 387 186
row2  175 643 200 111 213 
row3  186 643 390 206 400

the output file looks like this:

row1  200 390 400 387 186 1 2 2 1 2
row2  387 643 200 111 213 0 1 0 0 0
row3  186 643 390 206 400 0 0 0 0 0

I want to change that the search for row1 starts in column 1 row2, then if it can not find it must go to column 1 row3, column 1 row4 until all the items are found,
then it must look for the items of row1 in column 2 row2 then if it can not find it must go to column 2 row3 until all the items are found.

the output file should look like this then

row1  200 390 400 387 186 0 0 0 1 2 0 0 0 0 0 1 2 0 0 0 0 0 0 0 0 0 0 0 0 0
row2  387 643 200 111 213 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
row3  186 643 390 206 400 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Thanks for your help in advance
Johan


the current code

Private Sub AddAgeLocations(sourceFile As String, destinationFile As String)
        Dim records As New List(Of List(Of String))
        Dim offsets As New List(Of String)

        'fetch all the lines from the file, split each line into a list of ages (strings) and sort the list
        'store each list as an element of another list
        For Each line As String In IO.File.ReadAllLines(sourceFile)
            records.Add(line.Split(CChar(vbTab)).ToList)
            ' no need to sort yet        records.Add(line.Split(CChar(vbTab)).OrderBy(Function(a) CInt(a)).ToList) 
        Next

        'iterate through each record (list of age strings)
        'only going to be checking for matches starting at the next record (lineToScanFrom)
        For lineIndx As Integer = 0 To records.Count - 1
            Dim lineToScanFrom As Integer = lineIndx + 1
            Dim linesLeftToScan As Integer = records.Count - lineToScanFrom
            offsets.Clear()
            'iterate through each age in the record   'phil  For Each age As String In records(lineIndx)

            For Each age As String In records(lineIndx)
                Dim ageToFind As String = age
                'find the index of the next record containing that age (or "0"if doesn't exist)
                'and keep a record in a separate list (offsets)
                Dim offset As Integer = records.FindIndex(lineToScanFrom, linesLeftToScan, Function(nextLine) nextLine.Contains(ageToFind))
                'offsets.Add(If(offset = -1, "0", offset.ToString)) delete later
                offsets.Add(If(offset = -1, "0", (offset - lineIndx).ToString))
            Next
            'append the offset data to the current record of ages
            records(lineIndx).AddRange(offsets)
        Next

        'save the data
        File.WriteAllLines(destinationFile, records.Select(Function(r) String.Join(vbTab, r)))

[debeerj@hotmail.com]

I have tried to do it myself, but find it difficult to only specify that it should look for the first row in the 1st column and then the 2nd until all the columns have been searched. It then goes to the second line until eof.
Thanks
Johan

[debeerj@hotmail.com]

Hi
I am using excel at the moment but is is so time consuming.

Thanks
Johan

[wes4dbt]

I've noticed you haven't gotten any help. Maybe because the question is hard to understand? Maybe if you try explaining what your trying to achieve, what is the purpose of this matching numbers search, why does it have to be done in such an unusual manner. Then hopefully someone can help you.

[passel]

It took me a while to understand how your output line example correlated to your example inputs and your description.

But, based on the example, I wrote out some pseudo code to work through the logic. When something is complex like this, that is what I do. Try to write out the logic on paper first.

So, you want to loop through the rows of data, checking the values in each one, so a loop through the rows will be the outer loop.
You want to check each value in the row against one column, then check all the values in the row against the next column, so the loop through the column is the "slower" loop so comes next.
That is followed by the loop of values you are going to check against the column, followed by the loop of following rows in that column, so you end up with four levels of nested loops.
Below is the pseudo code I wrote down. I didn't write any code to test the logic, but it appears it should work to me. Someone better at Database type queries and organization can probably come up with better methods of gathering and processing the data, but the below logic should produce the example output you've given if implemented.

For each Row
  topRow = Row
  Output topRow values

  For each Column  'column to search
    CheckColumn = Column

    For each Column  'value to check
      searchValue = (TopRow, Column)  'The value you are searching for in the column you're checking
      RelativeRow = 0
      ReturnValue = 0

      For each row below the TopRow
        RelativeRow += 1
        If (row,CheckColumn) = searchValue Then
          ReturnValue = RelativeRow
          Exit For
        End If
      Next

      Output ReturnValue
    Next  'input column
  Next  'search column

  Output New Line
Next   'Row

[debeerj@hotmail.com]

Thank you Passel,

Sometimes its hard to say what you would like to achieve, also thank you for the way you explained the logic.

Thanks
Johan

[debeerj@hotmail.com]

Hi wes4dbt,

The code that I have included in my first post search each row below using all the columns to find a match, calculating how far(total rows) below it found the match.
row1 200 390 400 387 186 1 2 2 1 2
row2 387 643 200 111 213 0 1 0 0 0
row3 186 643 390 206 400 0 0 0 0 0

So in this example it found 200 in row 1, 390 in row 2

I would like to change the code that I have included in my post to do the following.

Search the contents of each row below using the 1st column, then the second, then the 3rd and so on,
the output file should then look like this:
row1 200 390 400 387 186 0 0 0 1 2 0 0 0 0 0 1 2 0 0 0 0 0 0 0 0 0 0 0 0 0
row2 387 643 200 111 213 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
row3 186 643 390 206 400 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

I hope that makes it a bit clearer.

I understand the logic and the way PASSEL explained it but looking at my code that works, how do I change it to search for the contents of each row, one column at a time.

I do not know how to change the list in the search to look up column for column.

Thanks
Johan

[debeerj@hotmail.com]

To me it seems I need to change the list in the loop.

[passel]

Rather than explain it again, I commented out your inner loop, and put in my nested loop as explained before, but using your variables for the most part, and your structures.

You have a list, which can be indexed by an integer (i.e. records(0)), so you index the list using (index).
That list contains another list, so that can be indexed the same way (index2), so records(0)(1) would be essentially saying records(row)(column). That is the way you index a particular column, you change the row index but keep the column index the same.

    Private Sub AddAgeLocations(sourceFile As String, destinationFile As String)
        Dim records As New List(Of List(Of String))
        Dim offsets As New List(Of String)

        'fetch all the lines from the file, split each line into a list of ages (strings) and sort the list
        'store each list as an element of another list
        For Each line As String In IO.File.ReadAllLines(sourceFile)
            records.Add(line.Split(CChar(vbTab)).ToList)
            ' no need to sort yet        records.Add(line.Split(CChar(vbTab)).OrderBy(Function(a) CInt(a)).ToList) 
        Next

        'iterate through each record (list of age strings)
        'only going to be checking for matches starting at the next record (lineToScanFrom)
        For lineIndx As Integer = 0 To records.Count - 1
            '    Dim lineToScanFrom As Integer = lineIndx + 1
            '    Dim linesLeftToScan As Integer = records.Count - lineToScanFrom
            offsets.Clear()
            For CheckColumn As Integer = 0 To records(lineIndx).Count - 1
                'iterate through each age in the record   'phil  For Each age As String In records(lineIndx)
                For column As Integer = 0 To records(lineIndx).Count - 1
                    Dim searchValue As String = records(lineIndx)(column)
                    Dim RelativeRow As Integer = 0
                    Dim ReturnValue As Integer = 0

                    For searchRow As Integer = lineIndx + 1 To records.Count - 1
                        RelativeRow += 1
                        If records(searchRow)(CheckColumn) = searchValue Then
                            ReturnValue = RelativeRow
                            Exit For
                        End If
                    Next
                    offsets.Add(ReturnValue.ToString)
                Next
            Next

            'For Each age As String In records(lineIndx)
            '    Dim ageToFind As String = age
            '    'find the index of the next record containing that age (or "0"if doesn't exist)
            '    'and keep a record in a separate list (offsets)
            '    Dim offset As Integer = records.FindIndex(lineToScanFrom, linesLeftToScan, Function(nextLine) nextLine.Contains(ageToFind))
            '    'offsets.Add(If(offset = -1, "0", offset.ToString)) delete later
            '    offsets.Add(If(offset = -1, "0", (offset - lineIndx).ToString))
            'Next
            'append the offset data to the current record of ages

            records(lineIndx).AddRange(offsets)
        Next

        'save the data
        File.WriteAllLines(destinationFile, records.Select(Function(r) String.Join(vbTab, r)))
    End Sub

By the way, in your first post you said the first element of the second row was 175, but mysteriously changed to 387 when you output it.
Also, in the example of what you wanted the output to be, on the first row you don't show a 2 in the third column from the end (which would be the 400 match from the first row to the last column).

[wes4dbt]

I hope that makes it a bit clearer.

I understood what you were asking, I was trying to get a better picture of why you were doing it. My thought was there might be a better way, if I understood the process. Where does the data come from, why are you searching for duplicate, why are you adding the results back to the file.

[debeerj@hotmail.com]

Hello Wes4dbt,

The data comes form a txt file:
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click


Dim sourcePath As String = "C:\data.txt"
Dim destPath As String = "C:\data1_Indexed.txt"

AddAgeLocations(sourcePath, destPath)

End Sub

The actual data represent the ages of people and academic achievements codes, with years and time taken codes. The data is harvested and I get them in rows and columns. To change this process is not easily done, I rather opted to change the code that I have.

The results is then appended to the existing data and read back into excel.

Hope it make sense.
Would you like to see an actual output from the current code?

[wes4dbt]

row1 200 390 400 387 186 1 2 2 1 2
row2 387 643 200 111 213 0 1 0 0 0
row3 186 643 390 206 400 0 0 0 0 0

So in this example it found 200 in row 1, 390 in row 2

I don't see 390 in row 2. I see 390 in row1 column 2 and in row3 column 3.

So does each column represent something different?

Like column 1 represents Age

200
387
186

(those are some old people)

It could be helpful to see the actual Excel file but I don't think you can upload an Excel file so you would need to post an image or zip the file.

[passel]

The "rows" he states are relative, so "row 1" in the context of the match is 1 row below the row being used as input.
The file's row 1 is the input, and the 200 in input row 1 is first found 1 row below it.
Likewise the 390 in row 1 is first found 2 rows below it.

Essentially this is creating a linked list of equivalent values, using relative offsets as the links.
If you had more lines of data in the example, you could see that 200 in the first line indicates there is also a 200 one row below.
And if there was a 200 further down in the records, then the 200 in the second row would have a relative offset to the next row that has a 200 in it.
When you reach a 200, whose "next link" relative index is 0, there are no more 200s in the rest of the data, you have reached the end of that linked list.

The code I posted in post #9 creates the output file as debeerj requested so should work for his immediate need, but I'm sure there could be better ways to link these values as this type of thing really seems tailored toward a database implementation. But, it is also fun to solve a problem within the given constraints as a bit of entertatinment.

[wes4dbt]

passel,

Yeah I'm not surprised by your explanation but it is a strange scenario and I would like to see to see the finished Excel file, for a little clarity.

The code I posted in post #9 creates the output file as debeerj requested so should work for his immediate need, but I'm sure there could be better ways to link these values as this type of thing really seems tailored toward a database implementation. But, it is also fun to solve a problem within the given constraints as a bit of entertatinment.

It's just hard to say at this point. But I do wonder why the OP hasn't mentioned if your code works as needed or not.

[debeerj@hotmail.com]

Hello Guys,

The code works like a charm......Thank you.

I have learned loads and I do not have to spend hours fiddling with excel.

Now that I could read more files I can do more work.

Thank you so much.
Johan