In Column A I have the id of the home team, B the name of the home team, C the id of the visiting team and in D the name of the visiting team:

12345       Borac Banja Luka    98765       B36
678910      Panevezys           43214       Milsami
1112131415  Flora               7852564     SJK
1617181920  Magpies             874236551   Dila

I want to create a column of ids and another of names but keeping the sequence of who will play with whom:

12345       Borac Banja Luka
98765       B36
678910      Panevezys
43214       Milsami
1112131415  Flora
7852564     SJK
1617181920  Magpies
874236551   Dila

Currently (the model works) I'm joining the columns with a special character, using flatten and finally split:

=ARRAYFORMULA(SPLIT(FLATTEN({
                             FILTER(A1:A&"§§§§§"&B1:B,(A1:A<>"")*(B1:B<>"")),
                             FILTER(C1:C&"§§§§§"&D1:D,(C1:C<>"")*(D1:D<>""))
                    }),"§§§§§"))

Is there a less archaic and correct approach to working in this type of case?

Spreadsheet to tests

CodePudding user response：

Stack up the columns using {} and SORT them by a SEQUENCE of 1,2,1,2:

=SORT({A1:B2;C1:D2},{SEQUENCE(ROWS(A1:B2));SEQUENCE(ROWS(A1:B2))},1)

CodePudding user response：

You can also try with function QUERY, enter this formula in F1:

={QUERY((A1:B), "SELECT * WHERE A IS NOT NULL and B IS NOT NULL",1);
QUERY((C1:D), "SELECT * WHERE C IS NOT NULL and D IS NOT NULL",1)}