0

I have a spreadsheet in which each line represents a conference presentation. There are columns for the attributes of each presentation, including for up to six presenters with their names, degrees, city, state, email institution, and bio: enter image description here

I'd like to repeat the speakers' data in a second sheet, stacking them, like so: enter image description here

I've tried a few solutions, and think that this is the closest to working as I expect:

=TRANSPOSE(SPLIT(TEXTJOIN("~",TRUE,TRANSPOSE('Program Submissions'!N2:N),TRANSPOSE('Program Submissions'!X2:X)),"~",FALSE,FALSE))

With this formula at the top of each column (adjusted to reference the appropriate column in the other sheet), it gives me: enter image description here

That's not correct, however. John Doe should only have an MD, and Jane Smith should have an MFA, MBA, and BA. But Jane's 2nd and 3rd degree cells were shifted upward into John's empty cells.

I"ve also tried this:

=UNIQUE({'Program Submissions'!N2:N;'Program Submissions'!X2:X})

and this:

={filter('Program Submissions'!N2:N, len('Program Submissions'!N2:N)); filter('Program Submissions'!X2:X, len('Program Submissions'!X2:X)); filter('Program Submissions'!AH2:AH, len('Program Submissions'!AH2:AH))}

Neither of which seem as good as the solution above, either throwing errors in cells or having other cell-shifting issues.

Chzzy
  • 1
  • 1

1 Answers1

0

try:

={N2:W2; X2:AG2}

which could be extended:

={N2:W10; X2:AG10}

to ensure no blank rows:

=QUERY({N2:W; X2:AG}, "where Col1 is not null", )

more at: https://stackoverflow.com/questions/73767719/

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    You covered almost all I would like to say, just one more thing, you can even stack 2 query with "where Col1 is not null" on 2 unclosed column range (e.g. ```{QUERY(A:A,"WHERE Col1 IS NOT NULL");QUERY(B:B,"WHERE Col1 IS NOT NULL")}```). – Ping Nov 15 '22 at 16:39
  • In fact, you could do this in just one query =QUERY({N2:W;X2:AG;AF2:AO....},"Where Col1 is not null"), just put the six ranges of columns. You could eve add SORT previous to the QUERY – Martín Nov 15 '22 at 16:51