Wednesday, April 27, 2011

yogi_Split Data With Dependencies

Yogi Anand, D.Eng, P.E.                                    Google Spreadsheet                    www.energyefficientbuild.com
serept said:
I have some Data with dependencies in the following format
Name | Id | Dependents
Alpha | 23 |
Gamma | 12 | Alpha
Beta |15 | Alpha; Gamma
Teta | 81 | Delta; Alpha; Kappa
The thing is there could be none or varying number of dependents (no max) for each Name. I want to have a column with dependent Id's based on names. Example a column with values "23; 41; 1" instead of "Delta; Alpha; Kappa"
Since VLOOKUP does not work with ArrayFormula it is not an option.
I tried combining INDEX,VLOOKUP, or FILTER with ArrayFormula but am unable to get a working formula.
This is the formula I am trying to use
=INDEX(B:B,ARRAYFORMULA(MATCH(ARRAYFORMULA(Transpose(split(C20,";"))),A:A,0)=C:C))
The part MATCH(ARRAYFORMULA(Transpose(split(C20,";"))),A:A,0) is giving me a set of row index values; but based on these row ids I need the actual Name Id's which I can concatenate.
----------------------------------------------------------------------------------
In the following solution I used primarily SPLIT and CONCATENATE functions to obtain the desired results.