Friday, March 9, 2018

yogi_Parsing hierarchical data to parent/child "indented" two (2) column output

Google Spreadsheet   Post  #2390

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Mar-09-2018

question by: Andon Sawyer
https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!msg/docs/D6MKz8OAaTk/2op_Jw3EBgAJ
Parsing hierarchical data to parent/child "indented" two (2) column output, help please :)
Trying to figure out a way to parse these two columns below... (there are a few hundred rows like this)


This is how I am structuring the hierarchy. Maybe I'm wrong to do it this way?

Kit PartComponents
6172DF-A0-P2-MR-BH6172DFA
6172DF-A0-P2-MR-BH6172DF0
6172DF-A0-P2-MR-BH2DFP2
6172DF-A0-P2-MR-BH6172DFBH
6172DF-A0-P2-MR-BH2DFMR
6172DF-A0-P2-MD-BH6172DFA
6172DF-A0-P2-MD-BH6172DF0
6172DF-A0-P2-MD-BH2DFP2
6172DF-A0-P2-MD-BH6172DFBH
6172DF-A0-P2-MD-BH2DFMD
So I parsed/grouped it to this using formulas thinking it would get me closer to my goal.

UNIQUE(A2:A)JOIN(",",QUERY($A$2:$B,"Select B where A = '"&E2&"'",0))
6172DF-A0-P2-MR-BH6172DFA,6172DF0,2DFP2,6172DFBH,2DFMR
6172DF-A0-P2-MD-BH6172DFA,6172DF0,2DFP2,6172DFBH,2DFMD
6172DF-A0-P2-MH-BH6172DFA,6172DF0,2DFP2,6172DFBH,2DFMH

which is this :

DESIRED OUTPUT
6172DF-A0-P2-MH-BH
6172DFA
6172DF0
2DFP2
6172DFBH
2DFMH
6172DF-A0-P2-MD-BH
6172DFA
6172DF0
2DFP2
6172DFBH
2DFMD
ETC ETC...

I have searched and tried all sorts of query, arrayformula, split, joins, transpose and filters... but I am at a loss.
Maybe it just isn't so easy, and maybe I've hit a wall.

Any input would be graciously received.

Cheers