How to return an array of books and subtitles where one column will show the unique books and the second column the unique subtitles for those books

Question

I have this range of books and subtitles:

BOOK	SUBTITLE
BR	ELE
BR	INT
BR	PI
BR	INT
BR	PI
EF	ELE
EF	INT
EF	INT

I want to create a list like this:

BOOK	SUBTITLE
BR	ELE
	INT
	PI
EF	ELE
	INT

I want to do it with either one formula or one formula at the top of each column.

I've managed to do it in two steps with the following:

Original range: A2:B9

In cell D2:

=IF(COUNTIFS($A$2:$A$9, $A2, $B$2:$B$9, $B2)=1, $A2, "")    

In cell E2:

=IF(COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)=1, $B2, "")

Then in cell G2:

=FILTER(D2:E27,E2:E27<>"")

Also, I've managed it by these formulas. In cell D2:

=SORT(UNIQUE(A2:B10),1,TRUE,2,TRUE)

In cell G2:

=ARRAYFORMULA(IF(D2:D7=D1:D6, "", D2:D7))

In cell H2:

=ARRAYFORMULA(E2:E7)

Any help would be much appreciated!

Answer 1

You can use REDUCE to loop inside the unique books, and stack the unique values of the filtered subtitles:

=REDUCE(A1:B1,TOCOL(UNIQUE(A2:A),1),LAMBDA(a,v,IFNA(
  VSTACK(a,
   HSTACK(v  ,  UNIQUE(FILTER(B:B,A:A=v)))))))