0

I have a set of data (26250) on one column, but I need them in rows of 175 values, on other words I need transpose them in 150 rows, and I have to do this for at least 10 files. I wondering if there is a Perl script that can do this automatically. I have check the transpose questions, but they just transpose all the column in one row. Is there a command that I can use to delimit the number of values per row?

Thanks is advance

Yuma

Zaid
  • 36,680
  • 16
  • 86
  • 155
user1213013
  • 3
  • 1
  • Does the solution have to be Perl? The language shouldn't make a difference for any full script to transpose an Excel file. However, if you want to incorporate this into a larger Perl program, absolutely a Perl solution is a reasonable goal. – Adam Mihalcin Feb 16 '12 at 05:01
  • What have you tried? There is nothing built-in to Perl (or probably any other language). Please read the [faq] and [ask] to understand why your question is likely to get downvotes and closed. – Jim Garrison Feb 16 '12 at 05:06
  • 1
    Have you looked at pivot tables? Maybe this underused Excel-feature (underused even by many long-term Excel users) can solve your problem. Please provide some sample data. – knb Feb 16 '12 at 16:23

2 Answers2

1

Here is a possible solution in Excel:

  1. Put your data into column A

  2. Put this formula into C1:

    =OFFSET($A$1,(COLUMN(C1)-3) + (ROW(C1)-1)*175, 0)

  3. Expand C1 to replicate the formula over a 175 x 150 range. The range should be C1:FU149.

Edit: I changed $A1 to $A$1

Edit 2: Hopefully this does what you need. I am not quite clear on what you mean by "transposed", but if you want the data in column-major order instead of row-major, use this formula:

=OFFSET($A$1,(COLUMN(C1)-3)*150 + (ROW(C1)-1), 0)
Igor ostrovsky
  • 7,282
  • 2
  • 29
  • 28
-1

I think what you want or perhaps what you need is this:

{=TRANSPOSE(A1:A3)}

something very important is to write the formula and press Ctrl+Shift+Enter in order to (I dont know how to say it) "fix" it into the cell otherwise it will not work.

Makyen
  • 31,849
  • 12
  • 86
  • 121
Ero08
  • 1
  • 1
  • 1
    By posting on the Stack Exchange (SE) network, you've granted a non-revocable right, under the [CC BY-SA 3.0 license](//creativecommons.org/licenses/by-sa/3.0/), for SE to distribute that content (i.e. regardless of your future choices). By SE policy, the non-vandalized version of the post is the one which is distributed. Thus, any vandalism will be reverted. Please don't make more work for people by vandalizing your posts. If you would like to disassociate this post from your account, see [What is the proper route for a disassociation request?](//meta.stackoverflow.com/q/323395) – Makyen Oct 23 '17 at 06:17
  • 1
    @Ero08 if you want to delete your own answer you can click the delete button under the answer. No need to vandalize it. – Ajay Brahmakshatriya Oct 23 '17 at 06:17