0

I am using =transpose(filter('Top HP''s List'!A:A, (search("-P/TT", 'Top HP''s List'!A:A)))) to search a column for text, filter it, and then transpose it to a row.

Is there any way to transpose to every other column in the row? eg, use Col A, leave Col B blank, use C, leave D blank, use E, etc etc.

Thanks Jon

Using =transpose(filter('Top HP''s List'!A:A, (search("-P/TT", 'Top HP''s List'!A:A)))) Getting a value in every column of the transposed row.

Would like a value in every other column of transposed row

J Dallimore
  • 3
  • 2

1 Answers1

0

Is there any way to transpose to every other column in the row? eg, use Col A, leave Col B blank, use C, leave D blank, use E, etc etc.

You can use:

=LET(data,transpose(filter('Top HP''s List'!A:A, (search("-P/TT", 'Top HP''s List'!A:A)))),
IFNA(REDUCE(TOROW(,1),SEQUENCE(COLUMNS(data)),LAMBDA(a,i,HSTACK(a,INDEX(data,,i),)))))
z''
  • 4,527
  • 2
  • 3
  • 12
  • Perfect, thanks muchly - one edit, I will search for a value like "-p/TT" held in a cell instead - I need 6 sheets with different search values so makes life easier – J Dallimore Jul 23 '23 at 11:43