Want to sort individual cells by magnitude & display as described

PAvisitor

New Member
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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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?

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.``````

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

Thank you very much, Phil. I really appreciate this - it's a tremendous help. Thanks again.

Replies
3
Views
168
Replies
3
Views
770
Replies
5
Views
345
Replies
2
Views
2K
Replies
7
Views
442

1,196,391
Messages
6,015,006
Members
441,865
Latest member
Enragedpanda

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?

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

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