Rearrange rows in matrix (Excel)

[Pax2Garo]

Hi everyone,

I'm actually struggling with excel in order to perform a certain command.

I have a very basic matrix consisting of rows and columns. What I'd like to do is to rearrange (changing the location of) the rows in the matrix based on a certain criteria.
What I'd like to accomplish is: If one value (cell) in an entire row of the matrix is higher than a certain value (a cut-off value) then the entire row should be copy/paste to another location in excel in order to gather all the rows with values higher than the specified value on one side and the rows with values lower than the specified value on the other side. It shouldn't affect the columns, only the rows should be switched.

Can someone help me with this? Maybe with a function or in VBA?

Thanks in advance!

 

[JLGWhiz]

Assuming it makes no differdence which is on top. Let's say the cut-off value is 10. The code below assumes no headers

Sub CutOffValue()
Dim sh As Worksheet, lr As Long, i As Long
Set sh = Sheets(1)
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lr
        If Application.Max(sh.Rows(i)) <= 10 Then
            If sh.Cells(lr + 2, 1) = "" Then
                sh.Rows(i).Copy sh.Cells(Rows.Count, 1).End(xlUp)(3)
            Else
                sh.Rows(i).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
            End If
        End If
    Next
    For j = 1 To lr
        If Application.Max(sh.Rows(j)) > 10 Then
            sh.Rows(j).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
        End If
    Next
End Sub

This would put the new matrix beneath the current matrix.

 

[Pax2Garo]

Hi, JLGWhiz

I'm very thankful for your work, the code works perfectly.

I have a last question. Assume that the cut-off value is not a single number anymore but an interval. For example if a value in a row is higher than 10 or lower than -10 the entire row should be copy/paste to an other location and gathered together with the other rows fulfilling that criteria. So, it is exactly the same question as before but with a cut-off value of 10 and -10. How should I recode that into VBA? Just by changing numbers into the code?

Cheers!

 

[JLGWhiz]

the parameters are controlled by the part in red font.

Sub CutOffValue()
Dim sh As Worksheet, lr As Long, i As Long
Set sh = Sheets(1)
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lr
        If Application.Max(sh.Rows(i)) [COLOR="#FF0000"]< -10 [/COLOR]Then
            If sh.Cells(lr + 2, 1) = "" Then
                sh.Rows(i).Copy sh.Cells(Rows.Count, 1).End(xlUp)(3)
            Else
                sh.Rows(i).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
            End If
        End If
    Next
    For j = 1 To lr
        If Application.Max(sh.Rows(j)) [COLOR="#FF0000"]> 10 [/COLOR]Then
            sh.Rows(j).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
        End If
    Next
End Sub

This code would use the interval +10 to -10

 

[Pax2Garo]

Hi JLGWhiz,

The last lines of code you provided doesn't work. Actually it should do the same than the FIRST code lines you provided but it doesn't. When I run the macro based on the FIRST code you provided, the matrix is rearranged perfectly based on the cut-off value. With the new one, some rows are copied but not the whole matrix and also not based on the good criteria. So the FIRST VBA code you provided was perfect, the only thing that should be changed is that if a value in a row is higher than 10 or lower than -10 the entire row should be copy/paste to an other location and gathered together with the other rows fulfilling that criteria and rows with a value (or values) between 10 and -10 should also be copy/paste to another location and gathered together.
Hopefully I'm clear enough with that.

Thanks for your help!

 

[JLGWhiz]

Yes, for the negative value it should have been a Min function instead of Max. However, after correcting that, I realized that because there are varying values in each row, it would be possible for the same row to meet the Max and Min criteria, but in different columns. Don't know if that is what you want or not. I also, noted that the wording in your last post seems to indicate that you want only the values between +10 and -10, which is a change from the original request. So please post an examplae of what you really want.

Sub CutOffValue()
Dim sh As Worksheet, lr As Long, i As Long
Set sh = Sheets(1)
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lr
        If Application.Min(sh.Rows(i)) < -10 Then
            If sh.Cells(lr + 2, 1) = "" Then
                sh.Rows(i).Copy sh.Cells(Rows.Count, 1).End(xlUp)(3)
            Else
                sh.Rows(i).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
            End If
        End If
    Next
    For j = 1 To lr
        If Application.Max(sh.Rows(j)) > 10 Then
            sh.Rows(j).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
        End If
    Next
End Sub

 

[Pax2Garo]

Hi JLGWhiz,

Here's an example of what I'd like to achieve:


Original Matrix

1	2	3	4
12	13	14	15
-11	3	2	9
-9	-8	-7	-6
5	6	8	7
-20	-1	3	9

<tbody>
</tbody>

Rearranged Rows and Matrices

Matrix witth values between -10 and +10

1	2	3	4
-9	-8	-7	-6
5	6	8	7

<tbody>
</tbody>

Matrix with values < -10 or > 10

12	13	14	15
-11	3	2	9
-20	-1	3	9

<tbody>
</tbody>

As you can see, once a row has a value (a cell) with a number <-10 or > 10 it is redirected to the "Matrix with values <-10 or > 10".
In the case that a row doesn't have any value (cell) < -10 or > 10 then it will be redirected to the "Matrix with values between -10 and +10".
For this purpose I'd like to have a Macro, because I've to work with a very large number of data. So a macro will safe me a lot of time!
Thx in advance!

 

[JLGWhiz]

Try this

Sub CutOffValue()
Dim sh As Worksheet, lr As Long, i As Long
Set sh = Sheets(1)
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lr
        If Application.Min(sh.Rows(i)) > -10 And Application.Max(sh.Rows(i)) < 10 Then
            If sh.Cells(lr + 2, 1) = "" Then
                sh.Rows(i).Copy sh.Cells(Rows.Count, 1).End(xlUp)(3)
            Else
                sh.Rows(i).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
            End If
    Next
    For j = 1 To lr
        If Application.Min(sh.Rows(i)) <= -10 Or Application.Max(sh.Rows(j)) >= 10 Then
            sh.Rows(j).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
        End If
    Next
End Sub

 

[Pax2Garo]

It seems to work for part of the matrix.

My original matrix is this one:

1	2	3	4
12	13	14	15
-11	3	2	9
-9	-8	-7	-6
5	6	8	7
-20	-1	3	9

<tbody>
</tbody>

When I run the macro, the result is the following one:

1	2	3	4
-9	-8	-7	-6
5	6	8	7
12	13	14	15

<!--StartFragment--> <colgroup><col width="65" span="4" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

So two rows are missing :s

Thanks for your help!

 

[JLGWhiz]

There are no rows missing. It simply did not copy those two rows because neither fits the criteria. In both the rows you have a value which exceed 10/-10 and value between 10/-10. The code could not include those rows in the criteria because they are exclusive of both the conditions you established. If you want to play with this further, I suggest you start a new thread.