Los of cell references with Excel sort 1

[philrock]

I need to sort an Excel spreadsheet with 800+ rows. There are many cell references from one row to another: for example, the formula in cell D121 refers to cell D203. When I run the Excel sort command, the row-to-row references are not preserved. As I read up on this, everyone says this is to be expected.

Is there an add-in that does what I need? Kutools claims elaborate abilities, but their web site does not specify whether their software does what I need.

 

[SkipVought]

Hi,

A SORT is something you do on (a) table field(s).

As such, fields in such tables ought all to have the same formula, thus making moot your problem. That is why Structured Tables, introduced in Excel 2007, propagates formulas as rows are added.

What is the purpose/function of Column D data? Heading?
Why, specifically, does D121 reference D203?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

I created a table with simple references between rows. Change from relative to absolute references in formulas seems to preserve rows data after sorting. No guarantee that this always works, but the problem can be linked to the way excel resolves relative reference - as offset to the caller.

A quicker way to switch between address types manually:

https://support.office.com/en-us/ar...ferences-dfec08cd-ae65-4f56-839e-5f0d8d0baca9

A code from Tom Urtis:

https://www.atlaspm.com/toms-tutori...la-cell-references-from-relative-to-absolute/

combo

 

[philrock]

Skip,

My data is organized with records in columns and fields in rows. This is needed because of the large number of fields. If organized in the conventional way - records in rows and fields in columns, the spreadsheet becomes extremely awkward to use. Of course, I'm now finding myself with a different kind of awkward.


Combo,

I think your method might work. I'll also try the macro on this page:

Link

- so I don't have to manually modify all 2300 cells in the spreadsheet one at a time.


Thanks for your help

 

[SkipVought]

Well, Excel is designed for tables with columns as fields. So you give up some built-in functionslity when you transpose the table.

So if that’s the case, you really need to sort left to right rather than top to bottom, or am I missing something?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Can you post or upload a small representative example with the formula?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[philrock]

Skip,

Basically, what I'm trying to do is re-arrange the fields (rows) so they are in a more logical order. The spreadsheet has been evolving for more than 15 years. If I had known at the start how large and complicated it would get, I would have been more careful and orderly from the beginning.

I certainly agree I have given up some functionality by working in the transposed manner.

The problem applies to hundreds of formulas in each column (record). I'll work on setting up an example to post.

Thanks, Phil

 

[kjv1611]

philrock,

Any updates?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[philrock]

I tried changing all references to absolute, as combo suggested - sorting still would not work. Also, sorting does not solve all my problems anyway. I'm just going to bite the bullet and grunt through it by hand.

Thanks very much for your help.

phil

 

[SkipVought]

No representative upload?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[philrock]

No upload Skip, sorry

 

[Andrzejek]

No upload because you cannot do that at work (prohibited)? Or because you have sensitive data? Other reasons?

People here try to help you, so if you would provide some simple, short, made-up data representing your problem, I am sure an elegant solution would be available.


---- Andy

There is a great need for a sarcasm font.

 

[philrock]

Attached is a very abbreviated example of my sort problem. I want to sort rows 11 through 32 based on column B.

One of the goals is to eventually move the values for parameters whose description starts with "Wheel" to column E. This would be accompanied by eliminating the words "Pinion" and "Wheel" from the descriptions.

Black text is user inputs (except for column G).
Blue text is calculated parameters.
Column G is a hard value of the parameter in column D.
Column H checks to see if the value in col D matches the value in column G after sorting.
Column I totals the number of mismatches found between columns D and G.
Column A is in case I want to restore the original order, but once the cell references are broken sorting by column A does not restore to the pre-sort status.

Before sort:

After sort:

 

[combo]

So excel can't cope with complicated references and sorting. You may consider to use vba. An example working with active sheet, bubble sorting case sensitive, empty cells first and with no validation (I entered 11, 32 and 2 in your example):

Sub BubbleSort()
Dim r_Start As Long, r_End As Long, c_Crit As Integer
Dim r_Count As Long
Dim CalcSetting As XlCalculation
r_Start = Application.InputBox("enter start row number", "sort range data", Type:=1)
r_End = Application.InputBox("enter end row number", "sort range data", Type:=1)
c_Crit = Application.InputBox("enter no of column sorted", "sort range data", Type:=1)
r_Count = r_End - r_Start + 1
CalcSetting = Application.Calculation
Application.Calculation = xlCalculationManual
With ActiveSheet
    For i = r_Count - 1 To 1 Step -1
        For j = 1 To i
            If .Cells(r_Start + j, c_Crit) < .Cells(r_Start + j - 1, c_Crit) Then
                .Rows(r_Start + j).Cut
                .Rows(r_Start + j - 1).Insert Shift:=xlDown
            End If
        Next j
    Next i
End With
Application.Calculation = CalcSetting
End Sub

combo

 

[philrock]

combo,

I had considered doing something with VBA, but thought it would be way too complicated. Your code is very elegant compared to what I would have done. I'll give it a try - thanks a million!

 

[combo]

Verify sorting (swapping rows) criteria for matching your needs and, if you have time, apply faster sorting algorithm, bubble sorting is simple but slow (n x n /2) for bigger sets of data.

combo

 

[SkipVought]

I used Named Ranges via Formulas > Defined Names > Create from selection.

I had selected B1:I32. Then use the above Create from selection using names in the TOP row and LEFT column.

Then CHANGED the formulas to use the Dual Names (TOP & LEFT) Column D now being Pinion_or_pair

For instance in column D
[tt]
Gear Ratio =Wheel_number_of_teeth Pinion_or_Pair/Pinion_number_of_teeth Pinion_or_Pair
Pinion base diameter, abs: =2*Pinion_base_radius__abs Pinion_or_Pair
[/tt]
Attached is your workbook with new Sheet1 Named Ranges

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[philrock]

Combo,

Your code worked like a charm - very impressive! Since I hope not to be doing this a lot, I probably won't look into a faster sort algorithm.

Phil