Solved

Sort by formula, keep values in adjacent columns

Posted on 2013-05-22
5
400 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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;…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

717 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