Rearrange numbers from a column in a different order in a second c
"macropod" skrev i en meddelelse
...
Hi Gaetan58,
For Column A data starting on row 1, try using this formula in Column B:
=INT((A1-1)/5)*10+MOD((A1-1),5)+1+INT((A1-1)/40)*5-INT((A1-1)/40)*80+INT((A1
-1)/80)*70
Cheers
--
macropod
[MVP - Microsoft Word]
Hi macropod
The OP's posting indicates, that data is various percentages, positive
and/or negative and in no particular order.
Quote:
The data are % which could be negative. The '' '' was just to say that the
series are continuing following the same pattern. These % are not in order.
Each group of five are data associated with one compound. They have to be
rearranged because they are automatically plotted on a graph with very
complex algorythms.
End quote
Your clever formula doesn't reorganize the *contents* of the cells but
"only" the numbering. In other words, if the OP's data is in A1:A4800,
column B1:B4800 is a helper column containing the numbers from
1 through 4800. Your formula is entered in C1:C4800, and the result is,
that C1:C4800 contains the reorganization of B1:B4800. In order
to get the *contents* of A1:A4800 reorganized, the formula in C1
must be:
=INDEX($A$1:$A$4800,INT((B1-1)/5)*10+MOD((B1-1),5)+1+INT((B1-1)/40)*5-INT((B1-1)/40)*80+INT((B1
-1)/80)*70)
On the other hand, I may have misunderstood the problem completely.
My VBA solution reorganizes the contents.
--
Best regards
Leo Heuser
|