Rearrange rows in matrix (Excel)

Pax2Garo

New Member
Joined
Jun 25, 2015
Messages
12
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!
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Assuming it makes no differdence which is on top. Let's say the cut-off value is 10. The code below assumes no headers
Code:
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

New Member
Joined
Jun 25, 2015
Messages
12
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
the parameters are controlled by the part in red font.
Code:
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

New Member
Joined
Jun 25, 2015
Messages
12

ADVERTISEMENT

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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.


Code:
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

New Member
Joined
Jun 25, 2015
Messages
12

ADVERTISEMENT

Hi JLGWhiz,

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


Original Matrix

1234
12131415
-11329
-9-8-7-6
5687
-20-139

<tbody>
</tbody>

Rearranged Rows and Matrices

Matrix witth values between -10 and +10

1234
-9-8-7-6
5687

<tbody>
</tbody>

Matrix with values < -10 or > 10

12131415
-11329
-20-139

<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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Try this
Code:
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

New Member
Joined
Jun 25, 2015
Messages
12
It seems to work for part of the matrix.

My original matrix is this one:

1234
12131415
-11329
-9-8-7-6
5687
-20-139

<tbody>
</tbody>

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

1234
-9-8-7-6
5687
12131415

<!--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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,577
Messages
5,596,996
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top