Solved

Sort by formula, keep values in adjacent columns

Posted on 2013-05-22
5
392 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
5 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 500 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

840 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