Excel spreadsheet help

[retepsnikrep]

I have imported a lot of csv values between 0-255 into column A in an excel spreadsheet.
Every tenth number is always the same value 1

What i want to do is sort/filter/import it so that whenever 1 appears it moves that block of ten into a row using columns 1-10

so data in my column A is below (not actual values)

1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10
etc

I want it to look like below in ten columns

1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10
etc

How to do this?

[AbCaRed00]

This may be a little murky and it's been years and I'm sure there is a better way but here's a couple tactics for data manipulation that hopefully may help...

In one approach I would import similar strings of data from the Column 'A' into a reasonably powerful word processor program, dumping all the data in one long column many rows long.
The data likely may paste-in with 'line breaks' or hidden 'paragraph characters' after each number.
After pasting into the WP, edit by Find and Replace, all whole word occurances of
'1' <line break> ..or <^p> as it may be
to
<line break>'1' <line break> """

which would result in a row gap in front of each '1'; spacing the sets from the next.

Then find and temporarily replace all occurrences of
<line break><line break>
[occurring after the number in the 10th position in front of the next '1'], to
'zzzz' or some placeholder
[.. as long as it isn't in your data set;]

Next change all occurrences of
<line breaks> or <^p> to
either tabs or commas [depending on what works best importing back into the spreadsheet. ]
The data will all jam together in a one big paragraph mass with the 'zzzz' where each line break is now desired.

Change the temporary 'zzzz' back to a single <line break> and it should form up like a table in the word processor with eleven columns of numbers in text including the long column 'A'.

Finally dump the grid of numbers into the spreadsheet in numeric format.

[In a nutshell, the method changes 'return keys' to 'tabs' after introducing an extra row, then replacing and and 'preserving it' while wiping out the rest with a text marker where you want the line breaks later. The 'zzzz' text marker is meant to change back to a 'return key'/line break to denote the end of a row and form up a table.]

**************
Another way to do it in the spreadsheet would be to paste the column of numbers in 'A'
then set cells sideways up something like
b1 =A1, c1 =A2, d1 =A3, e1 =A4, f1 =A5, g1 =A6, h1 =A7, i1 =A8, j1 =A9, k1=A10,

Copy block from B1:k10.

Move to cell at start of next group of data points i.e. to B11 and paste to k20 etc.
If there are thousands of rows it won't take long recopy and pasting repeatedly and doubling up to make for shorter keyboarding of the largest files.

What should form up is a row of numbers every ten rows forming up on the number '1' as seen in column 'A'

Select column 'A' and do a Sort
then all the number '1's should fall together with the data grouped into a table to the right.

Finally, Select, copy, paste, and format into the spreadsheet for analysis.

I hope there is an easier way or a macro that could be useable by all.

[richardh]

Hi Peter,

I presume this is not a one off and you will be doing this over and over?
Can you describe the process of how you aquire the data in the first place?

EDIT: As in is this already in a file before you get it to excel?

Thanks

Richard

[retepsnikrep]

Thanks for the ideas.

The data is in a comma delimited text file which I import into excel.

Keep the ideas comming it's all in the interest of the group.

[samwichse]

Oh man, this would be so easy in SAS.

In Excel, you can do it with a macro.

Just hit "record macro" set your key shortcut, then stop again to get an empty one. Hit "view macros->edit" and replace what's there with the following (as per your example of 1-10):

Sub transpose()
    Dim sourceRow, targetRow, targetColumn As Integer

    ' find first empty row in target sheet
    targetRow = 1
    While (Sheets(2).Cells(targetRow, 1) <> "")
        targetRow = targetRow + 1
    Wend

    sourceRow = 1
    While (Sheets(1).Cells(sourceRow, 1) <> "")
        For targetColumn = 1 To 10
            ' copy
            Sheets(2).Cells(targetRow, targetColumn) = Sheets(1).Cells(sourceRow, 1)
            ' delete original row (uncomment if required)
            ' Sheets(1).Cells(sourceRow, 1) = ""
            sourceRow = sourceRow + 1
        Next targetColumn
        targetRow = targetRow + 1
    Wend
End Sub

That will take each block of 10 data values in a column, transpose them into 10 columns in the next worksheet, then go to the next line and do the same till you run out of data.

[retepsnikrep]

I can't that to do anything Sam I'm a total excel idiot

[samwichse]

Did you look in the next sheet over? Which excel version are you using? If your data is in worksheet 1, the output will be in worksheet 2.
EDIT: Check your gmail.
Also: go to "view macros," hit "options," and double check your keyboard shortcut is still there, mine disappeared after the edit. Set it to something like "ctrl+m" hit it once, and your data will be transposed into the next sheet.

Sam

[retepsnikrep]

yep nothing can you send me a worksheet/book with it in and enabled/working then i can copy my data into your sheet 1 and press go.

Excels with macros might be blocked you may need to zip it up, nothing has come through yet.

[retepsnikrep]

Got the macro going now thanks.

[AbCaRed00]

Originally Posted by retepsnikrep

Got the macro going now thanks.

Good stuff. Played a bit with the old tables too. Curious how many rows are required for the data?

This below [done via spreadsheet to WP to spreadsheet again] can be pasted special as a formula into the B1 cell and should render 10 rows x10 columns if helps as an example.

=a1,=a2,=a3,=a4,=a5,=a6,=a7,=a8,=a9,=a10,
=a11,=a12,=a13,=a14,=a15,=a16,=a17,=a18,=a19,=a20,
=a21,=a22,=a23,=a24,=a25,=a26,=a27,=a28,=a29,=a30,
=a31,=a32,=a33,=a34,=a35,=a36,=a37,=a38,=a39,=a40,
=a41,=a42,=a43,=a44,=a45,=a46,=a47,=a48,=a49,=a50,
=a51,=a52,=a53,=a54,=a55,=a56,=a57,=a58,=a59,=a60,
=a61,=a62,=a63,=a64,=a65,=a66,=a67,=a68,=a69,=a70,
=a71,=a72,=a73,=a74,=a75,=a76,=a77,=a78,=a79,=a80,
=a81,=a82,=a83,=a84,=a85,=a86,=a87,=a88,=a89,=a90,
=a91,=a92,=a93,=a94,=a95,=a96,=a97,=a98,=a99,=a100 ,