[xwusa]

Hello Excel Expert,

Here is a scenario in an Excel programming I need help to resolve. There is a big data table as the following:

Column 1 ==== Column 2 ==== Column 3 ==== Column 4
*Data 11 ===== *Data 12 ==== *Data 13 ==== *Data 14
Data 21 ====== Data 22 ===== Data 23 ===== Data 24
Data 31 ====== Data 32 ===== Data 33 ===== Data 34
Data 41 ===== *Data 42 ===== *Data 43 ===== Data 44
*Data 51 ====== Data 52 ===== *Data 53 ==== *Data 54
Data 61 ====== Data 62 ===== Data 63 ===== Data 64
*Data 71 ===== *Data 72 ===== Data 73 ===== Data 74
Data 81 ====== Data 82 ===== Data 83 ===== Data 84

Conditions:
1. The data started with a special character, *, defines its group for the rest of the data in its column until it hits another *, which is the stat point of the next group.

For example, the groups are as the following:
Group 1: Data 11, Data 21, Data 31, Data 41,
Group 2: Data 51, Data 61
Group 3: Data 71, Data 81
Group 4: Data 12, Data 22, Data 32
Group 5: Data 42, Data 52, Data 62
Group x: ……..

2. The table is big. It contains any number of group ID, *, and any members within a group (the minimum is one). The table can be updated with new data randomly for both addition and/or deletion.

The objective:
When a user inputs a data ID (for example: “Data 32”), the excel needs to find out the first member in its group (such as "Data 12" in this example).

Due to the large table, use a manual process, such as GUI-based define names, may not be practicable. We have to find out a way for automatically programmable solution.

Looking forward to your help.

Best regards,

Wei

[StripedMeow]

I can think of 2 ways to do this, one would be to use range(x,x).find and set your direction to up, with find what being "*" with a partial match

I'm not sure if it would take the asterisk as a wildcard, however...i've never tried using a .find with an asterisk, but easy enough to test.

The other option would be to to create a for/next loop, with a step -1 from the cell 1 above your data ID (i.e., if Data32 were in B20, it would start searching @ B19, stepping -1 through each row in tat column.

You could do this with a command button on the spreadsheet with an inputbox for the user to enter the data ID they're searching for, and then either select the first group member in the spreadsheet, or pop up a message box with the first group member ID.

VBA would be easiest.

[StripedMeow]

Sample:

Sub TestFind()
    Dim rngFind As Range
    Dim strWhat As String
    
    strWhat = InputBox("Enter your Find")
    
    Set rngFind = ActiveSheet.[a:a].Find(what:=Val(strWhat))
    If Not rngFind Is Nothing Then
        Set rngFind = ActiveSheet.Range(Cells(rngFind.Row - 1, rngFind.Column), Cells(1, rngFind.Column)).Find(what:="~*", lookat:=xlPart, searchdirection:=xlUp)
        If Not rngFind Is Nothing Then
            MsgBox rngFind & " " & rngFind.Address
        End If
    End If
End Sub