Need Excel Function to Manage Eyetracking Data

Question

This is my first time using this, so I apologize if my title was too vague. I'm a researcher whose new to using eye-tracking in studies. Due to that fact, I messed up in programming and now my data may take months to be analyzed. I think a good excel function could help me, I'm here for your advice.

Basically, I need a function that can search along a row (20 columns) for a value A. Value A is in a specific column (lets call it column A) that is tied to column B (20 possible options). Column B contains value B, the value I am interested in.

A few complicating factors:

• Value A is a coordinate value. It is in brackets.
• There are 20 columns that possess a possible 'value A'
• Value B is in the form XXXX.jpg
• There are 20 columns that possess a possible 'XXXX.JPG'
• 'Column A is tied to column B'. In the eye-tracking program the values in column A and B are properties of the 'image resource'. Participants saw 20 images on the screen in a grid arrangement. Each images has its' own image resource. Column A refers to the position that an image appeared in and Column B refers to the specific image that appeared.

For example, Column A is called 'pos1_1' and Column B is called 'image1_1'

More in depth explanation...

• The function should be able to search along the row (for 20 columns (Z2 - AS2)) to find a coordinate. E.g. (240.0,108.0)
• Identify the column that this coordinate is in. E.g column pos1_1
• Identify the value in the same row in another column (F2 - Y2). Continuing this example, column image1_1 (this column will always have the same numbers as the 'pos' column).

So if the function searches along row 2 (columns Z2 - AS2) and finds (240.0,108.0) in AJ2 then it should identify name in cell AJ1. For example,'pos1_1'. Then it should search along row 1 (columns F1 - Y1) to find 'image1_1' and produce the value in AJ2, 'XXXXX.jpg'

Does anyone have any ideas about where I could start? I'm thinking LOOKUP but it's giving me some trouble. I think because of the brackets around the values in the 'pos' column.

Answer 1

i think i get you right but in case not so then let me know .

Function FindValue(searchValue As String) As String
    Dim coordinate As String
    Dim imageColumn As String
    Dim posColumn As String
    
    ' Loop through each row in the range
    For Each cell In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        ' Check if the cell value contains the search value
        If InStr(1, cell.Value, searchValue) > 0 Then
            ' Get the coordinate value
            coordinate = cell.Value
            
            ' Get the corresponding image column
            imageColumn = "image" & Mid(coordinate, 2, Len(coordinate) - 2)
            
            ' Get the corresponding pos column
            posColumn = "pos" & Mid(coordinate, 2, Len(coordinate) - 2)
            
            ' Return the image column value
            FindValue = Cells(cell.Row, Range(imageColumn & "1").Column).Value
            
            ' Exit the function or can be customize if you want
            Exit Function
        End If
    Next cell
    
    ' If the search value is null, return an empty str. or else...
    FindValue = ""
End Function

in any cell, enter the following formula: =FindValue("[coordinate_value]"), here replace [coordinate_value] with the specific coordinate value you want to search for (e.g., (240.0,108.0) or what ever you want). then press enter to get the corresponding image value in the tied column.