Sort by Absolute Value, Display True Value

[4thephil]

I am trying to get a range of cells sorted through a Macro by their absolute value, but still display their true value. The values of the cell are pulled via VLOOKUP from another tab. As I couldn't post an attachment, I have included an image of an example of what I'm looking for. I believe a third column must be created to figure out their absolute values, but I'm not sure.

Here is my macro I have which currently sorts it beutifully by true value:

Range("I22:J25").Select
    Range("J22").Activate
    ActiveWorkbook.Worksheets("sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("sheet1").Sort.SortFields.Add Key:=Range("J22" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("sheet1").Sort
        .SetRange Range("I22:J25")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[mirabeau]

OK. try this one. Modification from my earlier is indicated in red. If you're in the Carlos Slim/Bill Gates league $-wise you can up the 10^7 to some suitable higher figure.

Sub sortabs2()
Dim a As Range, i As Integer
Set a = Range("I22:J25")
For i = 1 To 4
    a(i, 2) = (10 ^ 7 + Abs(a(i, 2))) & Chr(30) & a(i, 2)
Next i
a.Sort a(1, 2), 1, Header:=xlNo
For i = 1 To 4
    a(i, 2) = Split(a(i, 2), Chr(30))(1)
Next i
End Sub

 

[4thephil]

This worked beautifully! Thank you!!

 

[4thephil]

Hello,

I'm running into a new issue. My values are pulled using VLOOKUP. Once I run the above formula, the values are sorted by absolute value beautifully. However, it removes the VLOOKUP formula completely, but leaves the cell value the same as it was. I use this template every week, and the values are always changing. Anyway to keep the VLOOKUP formula in play?

 

[mirabeau]

Hello,

I'm running into a new issue. My values are pulled using VLOOKUP. Once I run the above formula, the values are sorted by absolute value beautifully. However, it removes the VLOOKUP formula completely, but leaves the cell value the same as it was. I use this template every week, and the values are always changing. Anyway to keep the VLOOKUP formula in play?

OK. Let's try another approach. See if this one does it for you.

Sub nextversion()
Dim a As Range, b As Range, i As Integer
Set a = Range("I22:J25")
Set b = Cells(1, Columns.Count - 3)
a.Copy b
For i = 1 To a.Rows.Count
    Cells(i, b.Offset(, 2).Column) = Abs(a(i, 2).Value)
Next i
With b.Resize(a.Rows.Count, 3)
    .Sort b.Offset(, 2), 1
    .Resize(, 2).Copy a(1)
    .ClearContents
End With
End Sub

 

[4thephil]

That worked, however, while it did sort the first column, it did not sort the values.

Attached is a slimmed down image of a version of what I'm working with exactly. As you can see, I'm trying to "rank" each store by closest to 0.

 

[mirabeau]

If you confirm your data to be sorted are in Range("I22:J25"), that data in ColJ are the ones to be sorted by absolute value, and make the modification in red to my above code (that was an oversight by me) then it should work just as you requested.

Sub nextversion()
Dim a As Range, b As Range, i As Integer
Set a = Range("I22:J25")
Set b = Cells(1, Columns.Count - 3)
a.Copy b
For i = 1 To a.Rows.Count
    Cells(i, b.Offset(, 2).Column) = Abs(a(i, 2).Value)
Next i
With b.Resize(a.Rows.Count, 3)
    .Sort b.Offset(, 2), 1, Header:=xlNo
    .Resize(, 2).Copy a(1)
    .ClearContents
End With
End Sub

If you want sorted in the reverse order then put 2 in the line with red instead of 1, just before the red comma.

 

[4thephil]

I made the mofication to the code. I hate to be a bother, I'm so excited for this to work, but unfortunately, it isn't still. It is sorting correctly, and is sorting the Store #'s to what it should be, but leaving the Value #'s (column J, confirmed) alone. In otherwords:

--I----J--
1234 16
2345 -3
3456 8
4567 -31

After the above code is run, turns into:

--I----J--
2345 16
3456 -3
1234 8
4567 -31

So, as you can see, it is sorting, but leaving column J alone, only sorting column I. And with how it is now, the Value #'s (column J) don't match up correctly to their corresponding Store # (Column I).

 

[mirabeau]

I had tested that code several times before posting.

It worked fine as posted and I wasn't able to reproduce the results you report.

However, I tried it only with constants and with non-lookup (i.e. non location-finding) types of formulae. In both cases - no problem.

Could you post back whether your running of the code gives wrong results for all types of data (in J22:J25), or whether it is only lookup-type formulae that give the problem?

Seems to me the problem may occur with either wrongly-located dated, or perhaps with the relative/absolute references in your formulae being incompatible with that code as written.

You can also try it with a helper column located immediately adjacent to your data (as suggested in earlier posts) and do it manually and see if the problem still occurs, and if so with what types of data.

 

[4thephil]

J22:J25 is the only data being pulled, no other V-lookup or constants are being pulled. It is being pulled with the same store #'s with v-lookup (different column #) in other cells, but those are all sorted fine by my original code posted, and sorts accurately in both columns.

Seems to me the problem may occur with either wrongly-located dated, or perhaps with the relative/absolute references in your formulae being incompatible with that code as written.

I'm sorry, I'm not sure what you mean here, my existing code? the v-lookups?

An example of the v-lookup code used in J22:J25:

=VLOOKUP(E24,sheet1!$E$8:$BJ$590,57,FALSE)

 

[mirabeau]

(a) Does the code work with non-formula data in that range?

(b) In your
=VLOOKUP(E24,sheet1!$E$8:$BJ$590,57,FALSE)
delete all of your $ signs (these indicate absolute references) and see if it changes anything.