2

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))
player0
  • 124,011
  • 12
  • 67
  • 124
hardy
  • 537
  • 1
  • 5
  • 19

2 Answers2

3

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)), " ")), "♦" , " ")})

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thanks, some help need in this, I do this to transpose A:A in 2nd sheet =transpose(filter(Sheet1!A2:A, len(Sheet1!A2:A))) L column is last column of my data and its total 378 rows please help me in this – hardy Nov 21 '19 at 11:33
  • can you share a copy of your sheet? – player0 Nov 21 '19 at 11:35
  • here sheet link data in sheet1 and need in sheet 2 https://docs.google.com/spreadsheets/d/1zAWnfRGBSntjSLSvkMskRntNkdMjojclfCz8AAbeRXI/edit?usp=sharing – hardy Nov 21 '19 at 11:47
  • see: https://docs.google.com/spreadsheets/d/1j8iFs94rDlNnFuooZCP2QIWplFj6GPvekY8zu1IDs_g/edit#gid=737178459 – player0 Nov 21 '19 at 12:01
  • you mean the difference in A1 and A3 in Sheet4? you want to force the order of 1st row? – player0 Nov 21 '19 at 12:09
  • Please lookinto sheet 4 now I want data like now . hope you got my point Column a and Column B i update – hardy Nov 21 '19 at 12:11
  • 1
    paste in Sheet4!A3: `=TRANSPOSE(INDIRECT("Sheet1!A2:L"&COUNTA(Sheet1!A2:A)+1))` – player0 Nov 21 '19 at 12:15
  • (Y) Thank you so much , you are best .. have a nice day – hardy Nov 21 '19 at 12:17
2

or you can do just:

=TRANSPOSE(A1:C3)

or:

=TRANSPOSE(INDIRECT("A1:C"&COUNTA(A:A)))

0

UPDATE:

=QUERY(TRANSPOSE(INDIRECT("Sheet1!A2:L"&COUNTA(Sheet1!A2:A)+1)), "offset 1")
Community
  • 1
  • 1
player0
  • 124,011
  • 12
  • 67
  • 124
  • here sheet link data in sheet1 and need in sheet 2 https://docs.google.com/spreadsheets/d/1zAWnfRGBSntjSLSvkMskRntNkdMjojclfCz8AAbeRXI/edit?usp=sharing – hardy Nov 21 '19 at 11:47
  • see: https://docs.google.com/spreadsheets/d/1j8iFs94rDlNnFuooZCP2QIWplFj6GPvekY8zu1IDs_g/edit#gid=1301974344 – player0 Nov 21 '19 at 12:05