Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #108111 > unrolled thread

Rearrange numbers in a range

Back to article view | Back to microsoft.public.excel.programming

Contents

  Rearrange numbers in a range derick.gasimperial@gmail.com - 2015-10-22 21:18 -0700
    Re: Rearrange numbers in a range Claus Busch <claus_busch@t-online.de> - 2015-10-23 08:20 +0200
      Re: Rearrange numbers in a range Claus Busch <claus_busch@t-online.de> - 2015-10-23 08:40 +0200
        Re: Rearrange numbers in a range derick.gasimperial@gmail.com - 2015-10-23 21:10 -0700
          Re: Rearrange numbers in a range Claus Busch <claus_busch@t-online.de> - 2015-10-24 08:20 +0200
            Re: Rearrange numbers in a range derick.gasimperial@gmail.com - 2015-10-25 19:07 -0700
    Re: Rearrange numbers in a range GS <gs@v.invalid> - 2015-10-24 22:29 -0400
    Re: Rearrange numbers in a range Bruno Campanini <brunocam@libero.it> - 2015-10-26 01:32 +0100
      Re: Rearrange numbers in a range derick.gasimperial@gmail.com - 2015-10-25 19:10 -0700

#108111 — Rearrange numbers in a range

I have the numbers 1,2,3,4,5,6 in the source range("b4:g4") and would like to produce

1,2,3,4,5,6
2,3,4,5,6,1
3,4,5,6,1,2
4,5,6,1,2,3
5,6,1,2,3,4
6,1,2,3,4,5

Basically the code would take each number in the source range and place it in the 6th column if it is between 1 and 10 inclusive.
First it would have to the count how many numbers in the source range "b4:g4" are equal to and less than 10. 
In this case the 6 numbers are less than 10 so it would produce 6 results. if it was 5 numbers that are less than 10 then it would produce 5 results. If all the numbers are more than 10 then no action is required. 

The numbers in the source range can be in any order not necessarily in sequence.

Any help would be greatly appreciated.
Thank you

[toc] | [next] | [standalone]

#108112

Hi Derick,

Am Thu, 22 Oct 2015 21:18:05 -0700 (PDT) schrieb
derick.gasimperial@gmail.com:

> 1,2,3,4,5,6
> 2,3,4,5,6,1
> 3,4,5,6,1,2
> 4,5,6,1,2,3
> 5,6,1,2,3,4
> 6,1,2,3,4,5

try:

Sub Rearrange()
Dim myCnt As Long, i As Long, n As Long
Dim varData() As Variant

myCnt = Application.CountIf(Range("B4:G4"), "<11")
Application.ScreenUpdating = False
If myCnt = 6 Then
    For i = 5 To 5 - 2 + myCnt
        Range(Cells(i, 2), Cells(i, 6)).Value _
            = Range(Cells(i - 1, 3), Cells(i - 1, 7)).Value
        Cells(i, 7) = Cells(i - 1, 2)
    Next
Else
    For i = 2 To 7
        ReDim Preserve varData(myCnt - 1)
        If Cells(4, i) < 11 Then
            varData(n) = Cells(4, i)
            n = n + 1
        End If
    Next
    Range("B5").Resize(, UBound(varData) + 1) = varData
    For i = 6 To 6 - 1 + UBound(varData)
        Range(Cells(i, 2), Cells(i, 2 + UBound(varData))).Value _
            = Range(Cells(i - 1, 3), Cells(i - 1, 2 +
UBound(varData))).Value
        Cells(i, 2 + UBound(varData)) = Cells(i - 1, 2)
    Next
End If
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

#108113

Hi Derick,

Am Fri, 23 Oct 2015 08:20:48 +0200 schrieb Claus Busch:

> try:
> 
> Sub Rearrange()

sorry, forgot the case if all numbers greater 10

Try:

Sub Rearrange()
Dim myCnt As Long, i As Long, n As Long
Dim varData() As Variant

myCnt = Application.CountIf(Range("B4:G4"), "<11")
If myCnt = 0 Then Exit Sub

Application.ScreenUpdating = False

If myCnt = 6 Then
    For i = 5 To 5 - 2 + myCnt
        Range(Cells(i, 2), Cells(i, 6)).Value _
            = Range(Cells(i - 1, 3), Cells(i - 1, 7)).Value
        Cells(i, 7) = Cells(i - 1, 2)
    Next
Else
    For i = 2 To 7
        ReDim Preserve varData(myCnt - 1)
        If Cells(4, i) < 11 Then
            varData(n) = Cells(4, i)
            n = n + 1
        End If
    Next
    Range("B5").Resize(, UBound(varData) + 1) = varData
    For i = 6 To 6 - 1 + UBound(varData)
        Range(Cells(i, 2), Cells(i, 2 + UBound(varData))).Value _
            = Range(Cells(i - 1, 3), Cells(i - 1, 2 +
UBound(varData))).Value
        Cells(i, 2 + UBound(varData)) = Cells(i - 1, 2)
    Next
End If
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

#108114

On Friday, October 23, 2015 at 1:40:37 AM UTC-5, Claus Busch wrote:
> Hi Derick,
> 
> Am Fri, 23 Oct 2015 08:20:48 +0200 schrieb Claus Busch:
> 
> > try:
> > 
> > Sub Rearrange()
> 
> sorry, forgot the case if all numbers greater 10
> 
> Try:
> 
> Sub Rearrange()
> Dim myCnt As Long, i As Long, n As Long
> Dim varData() As Variant
> 
> myCnt = Application.CountIf(Range("B4:G4"), "<11")
> If myCnt = 0 Then Exit Sub
> 
> Application.ScreenUpdating = False
> 
> If myCnt = 6 Then
>     For i = 5 To 5 - 2 + myCnt
>         Range(Cells(i, 2), Cells(i, 6)).Value _
>             = Range(Cells(i - 1, 3), Cells(i - 1, 7)).Value
>         Cells(i, 7) = Cells(i - 1, 2)
>     Next
> Else
>     For i = 2 To 7
>         ReDim Preserve varData(myCnt - 1)
>         If Cells(4, i) < 11 Then
>             varData(n) = Cells(4, i)
>             n = n + 1
>         End If
>     Next
>     Range("B5").Resize(, UBound(varData) + 1) = varData
>     For i = 6 To 6 - 1 + UBound(varData)
>         Range(Cells(i, 2), Cells(i, 2 + UBound(varData))).Value _
>             = Range(Cells(i - 1, 3), Cells(i - 1, 2 +
> UBound(varData))).Value
>         Cells(i, 2 + UBound(varData)) = Cells(i - 1, 2)
>     Next
> End If
> Application.ScreenUpdating = True
> End Sub
> 
> 
> Regards
> Claus B.
> -- 
> Vista Ultimate / Windows7
> Office 2007 Ultimate / 2010 Professional

Thank you. Its not quite doing what is expected.
For example:
If the numbers in the source range are;
1,2,3,4,12,13

Then the result should be:
2,3,4,12,13,1
3,4,12,13,1,2
4,12,13,1,2,3
12,13,1,2,3,4

Thank you

[toc] | [prev] | [next] | [standalone]

#108115

Hi Derick,

Am Fri, 23 Oct 2015 21:10:59 -0700 (PDT) schrieb
derick.gasimperial@gmail.com:

> Thank you. Its not quite doing what is expected.
> For example:
> If the numbers in the source range are;
> 1,2,3,4,12,13
> 
> Then the result should be:
> 2,3,4,12,13,1
> 3,4,12,13,1,2
> 4,12,13,1,2,3
> 12,13,1,2,3,4

I thought you want only rearrange numbers <=10

Try this:

Sub Rearrange()
Dim myCnt As Long, i As Long, n As Long
Dim varData() As Variant

myCnt = Application.CountIf(Range("B4:G4"), "<11")
If myCnt = 0 Then Exit Sub

Application.ScreenUpdating = False

For i = 5 To 9
    Range(Cells(i, 2), Cells(i, 6)).Value _
        = Range(Cells(i - 1, 3), Cells(i - 1, 7)).Value
    Cells(i, 7) = Cells(i - 1, 2)
Next

Application.ScreenUpdating = True
End Sub


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

#108119

On Saturday, October 24, 2015 at 1:20:31 AM UTC-5, Claus Busch wrote:
> Hi Derick,
> 
>> 
> > Thank you. Its not quite doing what is expected.
> > For example:
> > If the numbers in the source range are;
> > 1,2,3,4,12,13
> > 
> > Then the result should be:
> > 2,3,4,12,13,1
> > 3,4,12,13,1,2
> > 4,12,13,1,2,3
> > 12,13,1,2,3,4
> 
> I thought you want only rearrange numbers <=10
> 
> Try this:
> 
> Sub Rearrange()
> Dim myCnt As Long, i As Long, n As Long
> Dim varData() As Variant
> 
> myCnt = Application.CountIf(Range("B4:G4"), "<11")
> If myCnt = 0 Then Exit Sub
> 
> Application.ScreenUpdating = False
> 
> For i = 5 To 9
>     Range(Cells(i, 2), Cells(i, 6)).Value _
>         = Range(Cells(i - 1, 3), Cells(i - 1, 7)).Value
>     Cells(i, 7) = Cells(i - 1, 2)
> Next
> 
> Application.ScreenUpdating = True
> End Sub
> 
> 
> Regards
> Claus B.
> -- 
> Vista Ultimate / Windows7
> Office 2007 Ultimate / 2010 Professional

Very close
If the variable myCnt is equal to 4 then you should get 4 results, equals 5 then 5 results, myCnt dictates how many results. Any result should not have a value of more than 10 in the rightmost column.
I made the following adjustment, but am not sure if its the best approach:

Sub Rearrange()
> Dim myCnt As Long, i As Long, n As Long
> Dim varData() As Variant
Dim c as integer

c = 0
> 
> myCnt = Application.CountIf(Range("B4:G4"), "<11")
> If myCnt = 0 Then Exit Sub
> 
> Application.ScreenUpdating = False
> 
> For i = 5 To 9
>     Range(Cells(i, 2), Cells(i, 6)).Value _
>         = Range(Cells(i - 1, 3), Cells(i - 1, 7)).Value
>     Cells(i, 7) = Cells(i - 1, 2)
      c = c+1
       if c = myCnt then Exit For

> Next
> 
> Application.ScreenUpdating = True
> End Sub

[toc] | [prev] | [next] | [standalone]

#108116

This smacks of lotto wheeling. Why not just use one of the many  
wheeling algorithms available online?

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108118

derick.gasimperial@gmail.com explained on 23-10-15 :
> I have the numbers 1,2,3,4,5,6 in the source range("b4:g4") and would like to 
> produce
>
> 1,2,3,4,5,6
> 2,3,4,5,6,1
> 3,4,5,6,1,2
> 4,5,6,1,2,3
> 5,6,1,2,3,4
> 6,1,2,3,4,5
>
> Basically the code would take each number in the source range and place it in 
> the 6th column if it is between 1 and 10 inclusive. First it would have to 
> the count how many numbers in the source range "b4:g4" are equal to and less 
> than 10.  In this case the 6 numbers are less than 10 so it would produce 6 
> results. if it was 5 numbers that are less than 10 then it would produce 5 
> results. If all the numbers are more than 10 then no action is required. 
>
> The numbers in the source range can be in any order not necessarily in 
> sequence.
>
> Any help would be greatly appreciated.
> Thank you

Try this:
================================
Public Sub ShiftLeft()
Dim A, B, C, R, i, j
Dim Separator As String

R = [F2!B4:G4]
Separator = ","

For Each i In R
    A = A & i & Separator
Next
A = Left(A, Len(A) - 1)

MsgBox A
For j = 1 To 5
    B = Split(A, Separator, -1, 1)
    For i = 1 To 5
        C = C & B(i) & Separator
    Next
    C = C & B(0)
    MsgBox C
    A = C
    C = NullString
Next

End Sub
==============================

Bruno

[toc] | [prev] | [next] | [standalone]

#108120

On Sunday, October 25, 2015 at 7:32:49 PM UTC-5, Bruno Campanini wrote:

> > I have the numbers 1,2,3,4,5,6 in the source range("b4:g4") and would like to 
> > produce
> >
> > 1,2,3,4,5,6
> > 2,3,4,5,6,1
> > 3,4,5,6,1,2
> > 4,5,6,1,2,3
> > 5,6,1,2,3,4
> > 6,1,2,3,4,5
> >
> > Basically the code would take each number in the source range and place it in 
> > the 6th column if it is between 1 and 10 inclusive. First it would have to 
> > the count how many numbers in the source range "b4:g4" are equal to and less 
> > than 10.  In this case the 6 numbers are less than 10 so it would produce 6 
> > results. if it was 5 numbers that are less than 10 then it would produce 5 
> > results. If all the numbers are more than 10 then no action is required. 
> >
> > The numbers in the source range can be in any order not necessarily in 
> > sequence.
> >
> > Any help would be greatly appreciated.
> > Thank you
> 
> Try this:
> ================================
> Public Sub ShiftLeft()
> Dim A, B, C, R, i, j
> Dim Separator As String
> 
> R = [F2!B4:G4]
> Separator = ","
> 
> For Each i In R
>     A = A & i & Separator
> Next
> A = Left(A, Len(A) - 1)
> 
> MsgBox A
> For j = 1 To 5
>     B = Split(A, Separator, -1, 1)
>     For i = 1 To 5
>         C = C & B(i) & Separator
>     Next
>     C = C & B(0)
>     MsgBox C
>     A = C
>     C = NullString
> Next
> 
> End Sub
> ==============================
> 
> Bruno

Very close. No result should have a value of more than  10 1n the rightmost column. Thank you

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web