Reverse-tracing a list of numbered items

Question

Is there a programmatic, hopefully non-macro way to reverse-trace a set of numbered items?

Example:

The first two columns are ones which data is entered in. You can see that item A has a relationship with items 1, 2, and 3. In the right-most column, items 1, 2, and 3 are all traced back to item A.

If this absolutely requires a custom macro, can you provide some suggestions on how to pre-process it with built-in functions to make writing the macro easier?

Answer 1

I don't believe there is a way to do the kind of lookup you're looking for on this without a macro. I could be wrong, but if I'm not, here's a custom function for you.

This thing seems to work without any pre-processing. You would use it as such:

In the cell next to 1, the formula would be

=ReverseTrace(A10,$B$1:$B$7,-1)

Assuming:

• A10 is the location of "1" (the value you're looking up)
• $B$1:$B$7 is the location of your list of items (1, 2, 3 down to 2, 4)
• -1 is the offset from the column of this $B$1:$B$7 list that you want to query

Next to 2, it would therefore be =ReverseTrace(A11,$B$1:$B$7,-1)

Function ReverseTrace(varValue As Variant, lookupRange As Range, intTraceOffset As Integer)

Dim rngCell As Range

    For Each rngCell In lookupRange
        If InStr(1, CStr(rngCell.value), CStr(varValue)) > 0 Then

            If Len(ReverseTrace) > 0 Then
                ReverseTrace = ReverseTrace & ", " & rngCell.Offset(0, intTraceOffset)
            Else
                ReverseTrace = rngCell.Offset(0, intTraceOffset)
            End If

        End If
    Next

End Function

Answer 2

I've had a crack at this using array formulae, without much success.

I've almost got there, so have decided to post my (lack of) results so far, in case someone else can flesh out the final step I'm missing.

Here's what I've got so far. It almost works, but since the IF doesn't seem to return an array, only the first item, you end up just with just first category ("A") instead of the set. I'm not sure if it's possible to fix this, does anyone else have any ideas?

CONCATENATE( IF( NOT( ISERROR( SEARCH(D1,$B$1:$B$7) ) ), $A$1:$A$7 , "" ) )

Ranges in the array:

• $B$1:$B$7 are you lookup vals ("1,2,3" etc)
• $A$1:$A$7 are the categories ("A","B",etc)
• D1 is the value you are investigating