CODEWITHSUNDEEP
excelpivot
S. M
S. M

Reputation: 1

Rearrange Excel data to specific number of columns

I have a large table in excel, with some properties listed in columns. The original data look like:

    Date    Space    prop1  prop2  prop3  prop4
    12.9    A1        12    143     12     17
    12.9    B1        23     23     34     56
    12.9    C1        45     23    123     34
    25.9    A1        48     52    325     55
    25.9    B1       125     14    254     23
    25.9    C1        15     25     45     55

I want to re-arrange/pivot the table into some pre-defined form (as below) to be read into a software.

    Date    Space    Prop    value
    12.9    A1       prop1     12
    12.9    A1       prop2    143
    12.9    A1       prop3     12
    12.9    A1       prop4     17
    12.9    B1       prop1     23
    12.9    B1       prop2     23
    12.9    B1       prop3     34
    12.9    B1       prop4     56
    12.9    C1       prop1     45
    12.9    C1       prop2     23
    12.9    C1       prop3    123
    12.9    C1       prop4     34
    25.9    A1       prop1     48
    25.9    A1       prop2     52
    25.9    A1       prop3    325
    25.9    A1       prop4     23
    .
    .
    .

So, basically all data has to be rearranged into 4 columns, wProp columns stacked into rows, including their date and space tags.

I have tried index, but somehow I can not get things right. Is there any way/formula to do so?

Thanks in advance.

Upvotes: 0

Views: 33

Answers (1)

Plato77
Plato77

Reputation: 442

The easiest way to do this would be to create a pivot table.

When you're in Excel (I'm assuming 2010 version or later):

  • click anywhere within the first table
  • go to the "Insert" tab
  • select Pivot Table
  • when it has created the basic blank table, right-click within it
  • go to PivotTable Options>Display and click "Classic PivotTable layout"

This means that drag-and-drop is enabled which makes it much easier. It is then possible to drag the various elements from the first table into the format that you want.

Let me know if you get stuck and I can help further.

Upvotes: 1

Related Questions