0

I have data in an excel spreadsheet. It is arranged as follows: 6 rows with 15 columns. Then a 7th row with 8 columns. Then 6 rows with 15 columns and a 7th row with 8 columns. This repeats down the spreadsheet. There are 441 'blocks' of this pattern.

There are two options that I have for this data.

Option 1: I would like it to be in a single column, so the values from the first row are now the first 15 values in column 1, then the values from the second row are now the next 15 values in column 1, and so on.

Option 2: I would like the values to be formatted in a grid that is 98 columns ad 441 rows. The data from the first 'block' would be the first row. the data from the second 'block' would be the second row.

Is there any way that this can be done?

Horizon_Net
  • 5,959
  • 4
  • 31
  • 34
jcronin
  • 21
  • 3

1 Answers1

0

Option 1 is possible with unpivoting. Assuming your data starts in A1, insert a new row at the top and a new column at the left and apply the steps detailed here. From the Table delete the left-hand two columns and filter the remaining column to delete blank rows.

For Option 2 consider OFFSET.

Community
  • 1
  • 1
pnuts
  • 58,317
  • 11
  • 87
  • 139