Solved

Sort by formula, keep values in adjacent columns

Posted on 2013-05-22
5
366 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 80

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now