Link to home
Create AccountLog in
Avatar of biker9
biker9Flag for Canada

asked on

Sort by formula, keep values in adjacent columns

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
Avatar of [ fanpages ]
[ fanpages ]

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.
Avatar of biker9

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of biker9

ASKER

Thank you, works perfectly!
biker9