Google Sheets to transform a table into a nested tree output or hierarchical structure output

Question

I just wonder if there is an easy way to transform the Google Sheets Original Input cells into a more readable table as in Output A or even Output B?

Original Input			Output A			Output B
Group	Points		Group	Points		Points
A	BBBB		A			A
A	CCCC			BBBB		- BBBB
A	DDDD			CCCC		- CCCC
A	EEEE			DDDD		- DDDD
B	FFFF			EEEE		- EEEE
B	GGGG		B			B
B	HHHH			FFFF		- FFFF
				GGGG		- GGGG
				HHHH		- HHHH

I know I can do it with App Script but I am curious if there are some smart solutions like a combo of query/filter/sort without the need for the App Script solution.

Answer 1

Another approach (for your preferred arrangement, given that the non-header data runs in A3:B):

=ArrayFormula(SPLIT(QUERY({VLOOKUP(UNIQUE(FILTER(A3:A,A3:A<>""))&"*",{A3:A,ROW(A3:A)&"-"&COLUMN(A3:A)},{1,2},FALSE);FILTER({"|"&B3:B,ROW(B3:B)&"-"&COLUMN(B3:B)},A3:A<>"")},"Select Col1 ORDER BY Col2"),"|",1,0))

ADDENDUM (based on additional comment from poster):

This version of the formula will work on unsorted raw data:

=ArrayFormula(SPLIT(QUERY({VLOOKUP(SORT(UNIQUE(FILTER(A3:A,A3:A<>"")))&"*",{SORT(FILTER(A3:A,A3:A<>"")),SEQUENCE(COUNTA(A3:A),1)&"-"&COLUMN(A1)},{1,2},FALSE);SORT(FILTER("|"&B3:B,A3:A<>""),1,1,2,1),SEQUENCE(COUNTA(A3:A),1)&"-"&COLUMN(B1)},"Select Col1 ORDER BY Col2"),"|",1,0))

Answer 2

or:

=ARRAYFORMULA(TRIM(SPLIT(FLATTEN(SPLIT(QUERY(FLATTEN(QUERY(TRANSPOSE(
 QUERY(QUERY({A3:A&"×", "¤"&B3:B&"×", B3:B}, 
 "select Col1,max(Col2) where Col3 is not null group by Col1 pivot Col3"), 
 "offset 1", 0)),, 9^9)),,9^9), "×")), "¤")))

Answer 3

try:

=ARRAYFORMULA(TRIM(FLATTEN(SPLIT(QUERY(FLATTEN(QUERY(TRANSPOSE(
 QUERY(QUERY({A3:A&"×", "- "&B3:B&"×", B3:B}, 
 "select Col1,max(Col2) where Col3 is not null group by Col1 pivot Col3"), 
 "offset 1", 0)),, 9^9)),,9^9), "×"))))