Mike,

Many thanks for the prompt response but this isn't quite all that I need.
This does return the smallest distance from a range, but what I need to do is
output (i.e. Copy and move the row with the lowest distance value, to another
sheet)
Basically, I need a new sheet (created from this one) which only shows one
instance of each Agency (the closest one)

I presume I would need to somehow incorporate your array formula into some
VBA code which would loop through the table and copy the row with the lowest
distance value to another worksheet? Also, I cannot specify the range
because each Agency Supplier has a different number of branches. (See Below)

A B C D
E F
AName1 ABranch ATown OurPostCode TheirPCode
Distance
AName1 ABranch ATown OurPostCode TheirPCode
Distance
AName2 ABranch ATown OurPostCode TheirPCode
Distance
AName2 ABranch ATown OurPostCode TheirPCode
Distance
AName2 ABranch ATown OurPostCode TheirPCode
Distance
AName3 ABranch ATown OurPostCode TheirPCode
Distance
AName3 ABranch ATown OurPostCode TheirPCode
Distance
AName3 ABranch ATown OurPostCode TheirPCode
Distance
AName3 ABranch ATown OurPostCode TheirPCode
Distance
AName4 ABranch ATown OurPostCode TheirPCode
Distance

All this needs to run automatically, preferably from a command button.

Thanks again,

Stephen
"Mike H" wrote:

Steve,

You don't need a macro it can be done with an ARRAY formula. See below on
how to enter it. So with out data on sheet 1 this goes on any other sheet and
the lookup value (Supplier name) is in a1 of the same sheet the formula is in

=MIN(IF(Sheet1!A1:A10=A1,Sheet1!F1:F10))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Steve Muir" wrote:

I am new to VBA/Macros and really don't know how to get this to work.

I have a spreadsheet which calculates distances between two postcodes for a
list of suppliers. Each supplier can have one or more branches at different
locations (postcodes). I need to return only the closest branch of each
supplier (i.e. copy the row of the closest supplier to a new sheet.

The sheet has 6 columns. Only 2 of these columns are needed to work out
this problem.

Column A holds the Supplier Name and Column F holds the distance from us.

I have worked out the steps needed to complete this, they a
1. Loop through Column A
2. If only 1 instance of the Supplier Name then copy this row to new sheet.
3. If more than 1 instance, then loop through column F (Distance field) and
find the row which has the lowest distance value and copy this row to the new
sheet.

If anyone can at least point me in the right direction I will try my best to
write this myself. I managed to write (well, adapt really!) the code which
works out the distance but this is a little beyond my current skills.
Many thanks

Stephen