Want to sort individual cells by magnitude & display as described

PAvisitor

New Member
Joined
Mar 15, 2009
Messages
3
Am looking for any help or suggestions. For a table made up of cells holding numbers that are the product of mathematical computation of entries on an X and a Y axis, am looking for a way to sort individual cells according to the magnitude of their numbers, and also display those sorted cells with tags from corresponding X and Y axis tags. Thanks very much for any help.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Welcome to the board.

How big is the table? What worksheet is it on? How is it laid out (where are the X & Y tags in relation to the data? Does its size change? If the size changes, is it isolated on the worksheet (will it always be over at least one cell and is there a gap between all data in the table and a Range("A1").CurrentRegion call will only select its data?
 
Upvote 0
Rich (BB code):
Thanks much for your interest. The table could be anywhere from 100 - 5000 computed cells. I'm using Excel 2004 for Mac, 11.3.7. The size of the table can change, and it could be isolated on the worksheet. I can set the table up in any format useful, provided I can use the top row and the leftmost column for category names. 
		C1	C2
		3	4
R1	2	6	8
R1	5	15	20
		
I'd like to sort the numbers in red in descending order, and present them as shown for this example:
"Column 2 Row 2       20
 Column 1 Row 2       15
 Column 2 Row 1        8
 Column 1 Row 1        6"
Thanks again for any help, much appreciated.
 
Upvote 0
I believe this will do what you asked for.
ALL DATA ON SHEET2 WILL BE ERASED WHEN THIS CODE IS RUN

Code:
Sub SortGridDataAndRefs()
    'This code should be placed in a standard module in the workbook containing the data
    '
    'Sheet1 holds input table with the following characteristics:
    '  B1 as the first column header
    '  A2 as the first row header
    '  Data starting in B2 and going right and down,
    '  At least 1 blank row directly after the last row of data
    '  At least 1 blank column directly after the last column of data
    '  No fully blank rows within the data
    '  No fully blank columns within the data

    'Sheet2 will be erased and the results will be placed there starting in cell A1.
    
    ThisWorkbook.Activate
    
    Dim rngCell As Range
    Dim lngX As Long
    With Sheets("Sheet2")
        .Cells.Clear
        lngX = 1
        For Each rngCell In Intersect(Sheets("Sheet1").Range("B2").CurrentRegion.Offset(1, 1), Sheets("Sheet1").Range("B2").CurrentRegion)
            .Cells(lngX, 1) = "Column " & rngCell.Column - 1
            .Cells(lngX, 2) = "Row " & rngCell.Row - 1
            .Cells(lngX, 3) = rngCell.Value
            lngX = lngX + 1
        Next
    End With
    
    Sheets("Sheet2").Select
    Range("A1").CurrentRegion.Select
    Selection.Sort Key1:=Range("C1"), Order1:=xlDescending, Header:=xlNo
    Range("A1").Select
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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
Back
Top