get 10 Closest Larger / Closest Smaller Values from a List

**Auni** · 02-05-2013, 05:27 AM

hi,i hope i can get some helps for this problem;

Listed in cells B2:B100 are address and C2:C100 are postcode for the address.
my task is when i key in the postcode,either the postcode is in the list or there is no exact match, i have to return the exact address for the postcode, the 10 closest larger and the 10 closest smaller postcode with the address that is in the list.

thank you for any help.

**Brumbot** · 02-05-2013, 05:34 AM

Hi Auni,

Could you post an example workbook?

**Auni** · 02-07-2013, 02:50 AM

hi Brumbot,
thank you for the response.
i try to post the workbook but i couldn't find the symbol to attach the file
can you help me,sorry i am still new with this forum.

**Brumbot** · 02-11-2013, 05:10 AM

Sure:

Make sure you are replying in 'advanced mode' -> go to the bottom of the thread and click advanced.

Underneath the main text box for replying to a post, there is an 'additional options' box. Go to attachments and click the manage attachments box. A pop up will appear and you need to click on the 'add files' button in the top right. Find your file and upload it.

**Auni** · 02-13-2013, 09:44 PM

hi Brumbot,

Sorry for the late reply, i have a holiday break last week because my country is celebrating Chinese New Year.

here i give you the actual workbook for this problem. i need to do it by using vba but in this workbook i had tried to solve it by using excel formula.
i manage to make the formula work but i have two problems with it. First it is loading for too long,so i think the same thing will happen when i using vba. is there any formula/coding that can help me to solve this problem. And the second problem is, as you can see in my workbook, in the closest postcode cells,when i search the postcode that has the exact match in the list,it will show the exact postcode for all the cells because some address have the same postcode. what i need is 5 closest postcode.Hope you can understand my problem.thank you for any help and sorry for my poor English.

**kvsrinivasamurthy** · 02-14-2013, 06:41 AM

Pl see the attached file with formulas and WorkSheet_change event.

**Auni** · 02-18-2013, 10:03 PM

hi kvsrinivasamurthy,
thank you so much,you solve my problem!
here i want to ask you something regarding the file.
what is the function of the coding that you create in Worksheet_change?
is it to solve the loading problem?
what about in the module 1? can you give me some explanation.
anyway,thanks again for your help!

**kvsrinivasamurthy** · 02-19-2013, 04:07 AM

Worksheet_change event is used to create unique list of Post codes in K column from B column, whenever I3 value is changed.
Formulas pick appropriate Postcodes from this list.
Module 1 not required. You can delete it.
If you are satisied mark the thread SOLVED.