How to extract values from a row into a column on a different sheet?

[Meropi Stratoulat]

Hi.

Can somebody help with a formula?

As per file attached, I need to extract the values from each row from sheet 1 into a column on sheet 2 but also to keep showing the group number for each value apart.

I was able to do that by selecting the values from one row, copy and paste them on sheet 2 with special paste "paste transposed" but, this operation is take to much time and the file I have has more than 100 columns and more the 200 rows.

I need to have on a different sheet (2) on column B, all the values per row, starting with column B until the end from sheet (1) and on column A, all group numbers from sheet (1) column A accordingly to the value.

Thank you

Meropi

[Ray Thomas]

This probably belongs in the Google Docs forums - https://productforums.google.com/forum/#!forum/docs - than here and your file was not attached, but to get you started you can copy and transpose an entire section of a sheet using:

1) To the same tab in a sheet put this in the top left cell you want the range copied to: =TRANSPOSE(range) eg =TRANSPOSE(A1:C4)

2)To a different tab in the same sheet put this in the top left cell you want the range copied to: =TRANSPOSE(sheet_name!range) eg =TRANSPOSE(Sheet1!A1:C4)

3) To a completely different sheet put this in the top left cell you want the range copied to: =transpose(importrange("sheet_URL/edit", "sheet_name!range")) eg =transpose(importrange("https://docs.google.com/spreadsheets/d/1XqI6BGA5uBioeZza1KxxsXeTT4VHJbLhGBz06h9Rv_U/edit", "sheet3!A1:D3"))

If you like you can copy the result again and just paste the values rather than the formula.

To make the question a bit more relevant to this forum, to transpose a datatable, the best solution I've found is one that appeared on Stack Overflow some years ago - https://stackoverflow.com/questions/16949993/inverting-rows-and-columns-on-google-area-chart 

[Ray Thomas]

Meropi, something turned up yesterday in the Google Docs forum that may be able to help you. Someone wanted to transpose several sheets but keep them grouped by date and an ID number - https://productforums.google.com/forum/#!topic/docs/PxWFYY3BKjg;context-place=forum/docs

The solution is a little complicated as it's pulling data from several pages, but what it's doing is using a single ARRAYFORMULA and VLOOKUP to rewrite the tables. You can see in https://docs.google.com/spreadsheets/d/1pZOyuC9NKkvcOc-kNAF7RJCqHz2Oddj8Kd_rlPjjWm0/edit#gid=919009111 (columns K to N) where they used TRANSPOSE to rearrange the original data, but Matt King's solution in https://docs.google.com/spreadsheets/d/1pZOyuC9NKkvcOc-kNAF7RJCqHz2Oddj8Kd_rlPjjWm0/edit#gid=521868215 uses a single formula to get everything done.

If you want to use a version of that to write to different Sheet without creating an intermediate table in the original then you still need to use IMPORTRANGE to access it - https://www.ablebits.com/office-addins-blog/2017/07/05/vlookup-google-sheets-example/ 

[Meropi Stratoulat]

Hi Ray,

I've tried with transpose but still are all per multiple column and not on in one column as I need it.

I will try also the ARRAYFORMULA which is a bit complicated :).

Thank you