MAcro

[sylvaing]

Hello all.
Here is my issue i am not quiet a full user of calc.
The numbers of my spreadsheet are set this way:
11111555552222244444555558888877777
11111555552222244444555558888877777
11111555552222244444555558888877777
11111555552222244444555558888877777
11111555552222244444555558888877777
11111555552222244444555558888877777
99999000006666633333111117777766666
99999000006666633333111117777766666
99999000006666633333111117777766666
99999000006666633333111117777766666
99999000006666633333111117777766666
99999000006666633333111117777766666

You can see that there are blocks containing the same numbers.
each block is composed of 5 rows of 6 numbers.
My aim is to have a spreadsheet composed with only one numbers of each block.
THis would make it like that.
1524587
9063176

AM i Clear enough ?
Any idea to help me ?
Many thanks.....
Sylvaing

[Villeroy]

Please change the subject of this thread to "Concentrate Data" or something. "Macro" implies that writing a macro would solve the problem. In that case I would suggest that you start writing the macro and come back to subforum "API and Macros" when you encounter concrete problems. However, you should not expect anybody to write that macro for you.

To answer this question in spreadsheet context it would be important to know if
11111555552222244444555558888877777
is a single text value in one cell (it can not be a number due to lenght) or if each digit or each block occupies one cell.
In any case you may start with a filter eliminating the row duplicates:
If there is no empty sheet, add one (let's say "Sheet2").
Select the entire list (Ctrl+Pos1, Ctrl+Shift+End)
Menu:Data>Filter>Standard...
[More Options]
[X]Copy Output, [X]No Duplicates, Copy To:Sheet2.A1

[TerryE]

If you are asking to pick every 5th column and sixth row from a separate spreadsheet:

• A1, F1, K1, P1, ...
  A7, F7, K7, P7, ...
  A13, F13, K13, P13, ...
  ...

because the other 29 cells in each 5 x 6 box are repeats then you can use a propagating formula in each cell, so

• A1 -> =INDEX(Sheet2.$A$1:$IV$65536;6*ROW()-1;5*COLUMN()-1)

and just drag/copy it to the size you need.

BTW you talk of "five rows of six numbers" but the example shows 6 row of 5 numbers. Anyway, you can just adjust the multipliers and offsets as required.

[sylvaing]

Thank You villeroy
In fact each cell contains one number.
I tried to use this filter you told me about but i can't see the "copy output" to check it.
how is the filter usable ? do you apply it to a selection ?
because once the destination sheet is selected, nothing happens.
I also have to select a field ( here i can choose a column number from A to IQ) why ?

Second thing since i posted this message, every text from OOo forum are replaced by ?????? not only caracters, all textes.
I wanted to change the subject of my post but now I don't know which button to press.
It is even hard to answer you guys.
If someone have an idea, I allready renew all my System/font folder.

Thank You also terry
nevertheless I am confused with your proposition.
inside your formula =INDEX(Sheet2.$A$1:$IV$65536;6*ROW()-1;5*COLUMN()-1)
is sheet2 the active sheet.
I tried to replace it by my sheet1_2 which contains all the datas but unccessfully.
The resultt keep returning 0.
THX
sylvain

[Villeroy]

Filters apply to the currently selected cells. In case you copy a filter result to some other place, you specify the top-left cell of the target range to be written.
Anyway, Terries solution is smarter:
I generated your example on Sheet1.A1:AI12
Put the formula into Sheet2.A1 (replacing "Sheet2" with "Sheet1" since I refer to Sheet1). THe result is 1.
Then I copied this A1 cell (with the previously entered formula) some columns to the right and down (drag the tiny square at the bottom-right of a selection or copy one cell and paste into a range selection).