Wednesday, April 3, 2013

yogi_Compile List Of Children From A Table Of ID (Objects) And Parents


                                          Google Spreadsheet   Post  #1095
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 3, 2013
user lucas cervera :(http://productforums.google.com/forum/?zx=saj65vfnlqxo#!category-topic/docs/spreadsheets/CrGHECidqnI)
hi,

I have a sheet were each row represents an object. Some objects have a parent object. I would like to do the following.

Column A is called "ID" and has a unique ID for each object.
Column B is called "PARENT" and lists the object's parents IDs (separated by commas). Objects can have multiple parents.
Column C is called "CHILDREN" and lists the IDs (separated by commas) of all objects that have that object as parent.

For example

ID           PARENT        CHILDREN
1                                       2,4
2                1                      4
3                                        5
4               1,2
5                 3

The number of objects will grow over time. Is it possible to do this by writing an ARRAYFORMULA in column C?

Thanks in advance,
Lucas
---------------------------------------------------------------------------------------------------------------
following is a solution to the problem
in Sheet1 I used the data as in user Lucas' original post, and
in yogi_Sheet1, I inserted more IDs to see how this solution will scale as more data is inserted ... so far so good