[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Sort by formula, keep values in adjacent columns

Posted on 2013-05-22
5
Medium Priority
?
435 Views
Last Modified: 2013-05-29
Hello Experts!

I would like to sort values in a range consisting of two columns, (see attached sheet), the sort is based on values in column E, and values in F relative to E are kept in same relevant order after the sort. Easy with VBA,

However I'd prefer sorting and keeping the adjacent relevant values using a formula.

Is this possible?

Thanks,
biker9
0
Comment
Question by:biker9
  • 2
4 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39189459
Hi,

(see attached sheet)

Sorry, but nothing is attached.

However, your requirement may simply be achieved by having a third column, say [G], that contains a formula (on row 2, in this example):

=[E2] & "/" & [F2]

Then copy this from cell [G2] down column [G] as far as the extent of the data within columns [E] & [F].

Sort on column [G].


Sight of the workbook may negate this suggestion though.

BFN,

fp.
0
 

Author Comment

by:biker9
ID: 39189905
Thanks for your suggestion,

check the attached (hopefully) sheet, and see the "Formula" tab. I think I was able to accomplish the sort in 4 steps;

First, I use your suggestion to convert the values to a string.

I then sorted the string using an array formula, and then converted the text to values.

Question:

can the steps be consolidated somehow, ie: a formula that accomplishes step 1 & 2 in one column?

or is there perhaps a more efficient way to accomplish this formula based sort?
Tx,
biker9
Sort-formula.xlsx
0
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 39203574
I put the following array-entered formula in cell G20, then copied it across and down for your sort:
=INDEX(A$20:A$59,MATCH(SMALL($A$20:$A$59+ROW($A$20:$A$59)/1000000,ROWS(G$20:G20)),$A$20:$A$59+ROW($A$20:$A$59)/1000000,0))
The bit with ROW divided by a million is to act as a tie-breaker without mis-sorting two values that are close together.

The bit with ROWS is to give the series 1, 2, 3, etc. as you copy the formula down. SMALL then returns the smallest number in column A, second smallest, third smallest, etc.

To array-enter a formula:
1. Paste the formula in the formula bar (or click in the formula bar)
2. Hold the Control and Shift keys down
3. Hit Enter, then release all three keys
Excel should respond by adding curly braces { } surrounding the formula. If not (or if you see #NUM! error value), repeat the three steps.
Sort-formulaQ28136406.xlsx
0
 

Author Closing Comment

by:biker9
ID: 39205161
Thank you, works perfectly!
biker9
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question