Gaetan58 said:

Hi, I'm using Excel 2003 on Windows. I have a list of 4800 numbers in a

column divided by groups of 5 within a larger group of 80. I need to

rearrange them in a second column with a different order but with a

pattern.

Each group of five consecutive numbers are separated by a jump of 6

numbers

up to 75, starting at number ''1''. By using exactly the same pattern,

start

this time with number ''6'' up to 80. The same pattern is repeated from 81

to

160, ... up to 4800.

I'm not an expert. Is there an efficient way to rearrange these numbers?

Any

help would be great!

In other words, in column A, you have numbers from 1 to 4800. In column B,

I

need the numbers to be in the following order:

A B

1 1 1st group of 80

2 2

3 3

4 4

5 5

6 11

7 12

8 13

9 14

10 15

11 21

12 22

13 23

14 24

15 25

16 31

17 32

18 33

19 34

20 35

'' ''

'' ''

36 71

37 72

38 73

39 74

40 75

41 6

42 7

43 8

44 9

45 10

46 16

47 17

48 18

49 19

50 20

51 26

52 27

53 28

54 29

55 30

56 36

57 37

58 38

59 39

60 40

61 46

62 47

63 48

64 49

65 50

66 56

67 57

68 58

69 59

70 60

71 66

72 67

73 68

74 69

75 70

76 76

77 77

78 78

79 79

80 80 2nd group of 80 with identical pattern to group 1

81 81

82 82

83 83

84 84

85 85

86 91

87 92

88 93

89 94

90 95

'' ''

151 146

152 147

153 148

154 149

155 150

156 156

157 157

158 158

159 159

160 160

Thanks a lot!

Hi

Here's a VBA solution, which reorganizes the contents of

the datacells according to the given rules and puts the result

into the cells to the right of the datarange.

The values for Outer, Inner and the number of cells in

the datarange may be altered. It's left as an exercise

to the reader to spot the principles behind

Put the routine in a general module (<Alt><F11>, Insert > Module)

Sub Reorganize()

'Leo Heuser, 19 Nov. 2006

Dim Counter As Long

Dim Counter1 As Long

Dim Counter2 As Long

Dim DataRange As Range

Dim DataRangeValue As Variant

Dim Dummy As Long

Dim Inner As Long

Dim Outer As Long

Dim Reorganized() As Variant

Outer = 80

Inner = 5

Set DataRange = Sheets("Sheet1").Range("A2:A4801")

DataRangeValue = DataRange.Value

ReDim Reorganized(1 To UBound(DataRangeValue), 1 To 1)

For Counter = LBound(DataRangeValue) To _

UBound(DataRangeValue) Step Outer

For Counter1 = 1 To Outer Step Inner

For Counter2 = 1 To Inner

Dummy = Counter + Counter1 + Counter2 - 2

If Dummy > Counter + Outer / 2 - 1 Then

Reorganized(Dummy, 1) = _

DataRangeValue(Dummy - Outer + _

Inner * (Int(Counter1 / Inner) + 1), 1)

Else

Reorganized(Dummy, 1) = _

DataRangeValue(Dummy + Counter1 - 1, 1)

End If

Next Counter2

Next Counter1

Next Counter

DataRange.Offset(0, 1).Value = Reorganized

End Sub