Import data from nth columns to another sheet

0
votes

I'm trying to import all data from nth columns, starting from the 3rd row, from one worksheet ('Dataimport') to another ('Cleaned Data') in the same spreadsheet, but so far I have only managed to get a specific cell from every nth column:

=INDEX(Dataimport!$C$3:$HI$3;(ROWS($A$1:A1)*6)+1)

enter image description here

Right now I'm doing it manually, using =TRANSPOSE(IMPORTRANGE("1yyb1k0uAdN1XcLWBhNq4jA0eInnePRoUt9IbuXDmfEU";"Dataimport!I3:I300")) in every cell, so that the column data is imported horizontally (this is how it's supposed to look like):

enter image description here

The sheet with data looks like this. Here I want to retrieve data from every 7th column starting from column C:

enter image description here

Any idea how I do this?

Public link: https://docs.google.com/spreadsheets/d/1hXFiSoduVjcZ6fbOcyp-BfI7m4So-01umLS0kBm-lVI/edit?usp=sharing

2
excelgoogle-sheetsgoogle-sheets-formulamultiple-columns
In Excel, you can use arrays for the row_num and column_num arguments to the INDEX function. Perhaps there is something similar in Sheets. – Ron Rosenfeld
Can you please update your question with a public link of your spreadsheet, an example of the input you are using and the expected output? – Alessandro
@Alessandro I've inserted a public link now :) – Betina Svendsen
Betina, please make your sample sheet editable by all, not just viewable. See here for a guide: webapps.stackexchange.com/questions/138382/… Since it involves imports from other sheets, we lose that access if we make our own copy of your sheet. Alternatively, you could paste the content of the other sheet to a tab in your sample sheet, so we can develop the correct formula locally. – kirkg13

2 Answers

1
votes

Proposed solution

Given your attempt =INDEX(Dataimport!$C$3:$HI$3;(ROWS($A$1:A1)*6)+1), I would suggest using the Google Sheets Formula OFFSET that allows specifying indexes as mathematical operations.

You will be able to specify the column offset with a simple mathematical operation in order to get the next needed indexes for the rows below.

Here is an example:

=TRANSPOSE(OFFSET(Dataimport!C$2:C$199;1;(ROW()-1)*6;199;1))

With this formula you can obtain the right column form the Dataimport Worksheet and then transpose it in order to fit it in a row.

Putting this formula in the Clean Data "D2" Cell will compute a column offset of 6 starting from the 3rd column ("C") considered in the OFFSET formula. Dragging down the formula will adjust the column offset index to the needed 7 columns range.

Reference

OFFSET

1
votes

I think I have a solution. I've build a spreadsheet with dummy data and then I made a formula that takes every nth column from the table.

Here is my solution: https://docs.google.com/spreadsheets/d/1J6x4H_cNczRRo40Ri6Nwa-YnZOISpxZmG4-JZ7tA1kA/copy

First I make an ID column to both tables to let vlookup work I use vlookup and arrayformula formula for this: For grabbing multiple columns I use sequence formula within vlookup. This makes an array of numbers with defined step. So if you can have every 6th column you define step as 6. Here step is defined in cell c23 =ArrayFormula(vlookup(A24:A43,A2:AB21,sequence(1,5,4,C23),false))

Of course it can work in multiple sheets or files. You just need to use importranges instead of standard references to a range.

enter image description here