• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

a "LOOKUP" function which returns an array but not like the INDEX function, but rather like applying a filter on a table

RomeoNastase

New Member
Dear All,

Please help me to solve the problem I tried to explain in the attached sample file.

Basically, in a table with all Children and their related Parents (BTW, those Parents may be Childred of some other Parents...) I need to get for every Parent the array formed by their direct respective Children.

More details and 2 examples of how I would need to use the data, in the attached file.

Thank you!
Romeo
 

Attachments

  • problem with Parents and Children.xlsx
    15.9 KB · Views: 9
Dear All,

Please help me to solve the problem I tried to explain in the attached sample file.

Basically, in a table with all Children and their related Parents (BTW, those Parents may be Childred of some other Parents...) I need to get for every Parent the array formed by their direct respective Children.

More details and 2 examples of how I would need to use the data, in the attached file.

Thank you!
Romeo

Dear All,

Reading more on the subject, I have realized I am trying to do in MS classic Excel what FILTER function does in Excel365.
As I am not able to use Excel365, I am forced to solve the problem using classic Excel.

I have solved it by writing a custom VBA function in MS Access and using an Excel file as the source of my Children-Parents table.
But... when I want to get back the data from MS Access to Excel by linking to the query I wrote (which refers to the custom VBA function I have created), this query simply does not appear in the Navigator window of the Excel feature Get Data->From Database->From MS Access Database :(

Maybe, I can re-use the existing VBA function from MS Access to Excel?...
But I don't know how to refer to Excel tables and their related columns, instead of Access Tabels&Querries.

Please let me know any idea you may have.

Thanks a lot!
Romeo

p.s. below the VBA solution :

73761

VBA Code:

>>> use code - tags instead of any indents <<<

Code:
Option Compare Database
Option Explicit
Public view As Boolean

Function Passing_Property_from_Children_to_Parents(ParentsAndChildren As String, Child As String) As Boolean
Dim rst_main As Recordset
Dim rst_sub As Recordset
Set rst_main = CurrentDb.OpenRecordset("SELECT * FROM [" & ParentsAndChildren & "] WHERE [Child]='" & Child & "'", dbOpenDynaset)
view = rst_main![Property] Or False
Set rst_sub = CurrentDb.OpenRecordset("SELECT * FROM [" & ParentsAndChildren & "] WHERE [Parent]='" & Child & "'", dbOpenDynaset)
With rst_sub
If .EOF Then
view = view Or False
GoTo forced_exit
Else
While Not .EOF
If ![Property] = True Then
view = True
GoTo forced_exit
Else
view = view Or Passing_Property_from_Children_to_Parents(ParentsAndChildren, ![Child])
End If
.MoveNext
Wend
End If
End With
forced_exit:
rst_sub.Close
rst_main.Close
Passing_Property_from_Children_to_Parents = view
End Function
 
Last edited by a moderator:
Not being familiar with Access VBA, most of your function is foreign to me, but I do fancy I see some recursion in there?
I need to get for every Parent the array formed by their direct respective Children.
So I had a go with this, using some sort of recursion. In the attached is a macro blah which calls another sub FindParent.

But I don't know how to refer to Excel tables and their related columns, instead of Access Tabels&Querries.
You'll see examples of that in the code and in the formulae I applied to cells H6 & I6 and copied down.
 

Attachments

  • Chandoo45954problem with Parents and Children.xlsm
    26.3 KB · Views: 1
Back
Top