Sort by closest value to answer. Closest answer will appear at the top of the sorted list

Hi, I am trying to automatically sort a column in excel using VBA.

I want the closest value to my answer (my answer is found in sheet 2 but its hidden) to appear at the top of the sorted list. (The answer will be in a separate sheet, say sheet2, but my list will be in sheet1)

for example, my answer is 4,000 and the closest answer is 3,998. I want the row where the answer is 3,998 to be shifted to the top of the list and likewise sort for all the other rows. So the whole list will be sorted based on which ones are the closest to the answer.

This is the sorting code I found online, but idk how to include the part where I can sort according to my answer in another cell. Is that even possible?

Please Login or Register  to view this content.

Not really, this code would do regular sort (in this particular retup - vertically, in ascending order.

If you want to use such code you shall first compute the "distance" may be with the function Abs(value-your_answer).

But indeed, the code will do the automatic sort (unfortunately not the way you want) always when you change (manually or by macro, but not as a result of formula) any cell in column B.

If you do not feel confident about VBA it will be much easier to support you if you prepare a small sample file and attach it - see one of our "canned replies":
Will you please attach a SMALL sample Excel workbook(s) (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If trere are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, etc. - please show them all or at least indicate in text) The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).

3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!

To attach an Excel file you have to do the following: Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.

Originally Posted by AAS44

Lets say i do =ABS(B2-Sheet2!$A$1). Where can i place this in my current code?

Without any context this means little to us.


Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.