Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Sort by formula, keep values in adjacent columns

Posted on 2013-05-22
Medium Priority
419 Views
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
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
• 2

LVL 35

Expert Comment

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

ID: 39189905

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

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

ID: 39205161
Thank you, works perfectly!
biker9
0

## Featured Post

Question has a verified solution.

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

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.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
###### Suggested Courses
Course of the Month9 days, 8 hours left to enroll