Reputation: 557
I have data in google sheet I just want to fetch that data in my other sheet but in transpose Here is ex:
Column A | Column B | Column C
=================================
site1.com | Name 1 | Name 2
site2.com | Name 3 | Name 4
site3.com | Name 5 | Name 6
Want to data like this
Column A | Column B | Column C
=================================
site1.com | site2.com | site3.com
Name 1 | Name 3 | Name 5
Name 2 | Name 4 | Name 6
I don't want to enter formula manually in every row so is arryformula can do this automatic.
I'm trying this but not able to what I want.
=ARRAYFORMULA(TRANSPOSE(Sheet1!$B2:B300 & Sheet1!$L2:L300))
Upvotes: 2
Views: 1229
Reputation: 1
or you can do just:
=TRANSPOSE(A1:C3)
or:
=TRANSPOSE(INDIRECT("A1:C"&COUNTA(A:A)))
=QUERY(TRANSPOSE(INDIRECT("Sheet1!A2:L"&COUNTA(Sheet1!A2:A)+1)), "offset 1")
Upvotes: 2
Reputation: 1
try:
=ARRAYFORMULA({TRANSPOSE(SORT(FILTER(A:A, A:A<>"")));
SUBSTITUTE(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(
IF(ISNUMBER(QUERY(QUERY({A:B; A:A, C:C},
"select count(Col1) where Col1 is not null group by Col1 pivot Col2"),
"offset 1", 0)), SUBSTITUTE(QUERY(QUERY({A:B; A:A, C:C},
"select count(Col1) where Col1 is not null group by Col1 pivot Col2"),
"limit 0"), " ", "♦"), )),,999^99)), " ")), "♦" , " ")})
Upvotes: 3