Macro/VBA to loop through list of values and output row- Help Need

[Steve Muir]

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 are:
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

 

[Mike H]

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.

 

[Don Guillett]

How about a helper column in col G and this formula ARRAY
entered(ctrl+shift+enter) copied down>filter
=IF(F2=MIN(IF($A$2:$A$6=A2,$F$2:$F$6)),1,2)

 

[Steve Muir]

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

 

[Steve Muir]

Thanks everyone for your help.

Don, I have used your idea and just used conditional formatting based on the
value in the helper column to just highlight the relevant closest agency,
rather than copy the rows to another sheet.

My boss is happy enough with this solution, so that'll do me!!!

Many thanks again for both of your unbelieveably quick responses

 

[Don Guillett]

Or you can use data>autofilter>filter on the helper column for 1

 

[Jef Gorbach]

create the helper column showing the closest agencies per the above -
then filter the helper column and copy/paste the visible rows to
another blank sheet.

obviously change the range and Field:=# to reflect your helper
column.

Sub FilterCopy()
Dim FilterRange As Range
Dim CopyRange As Range
Set FilterRange = Range("A1:A100") 'Header in row
Set CopyRange = Range("A2:A100")
FilterRange.AutoFilter Field:=1, Criteria1:=1
CopyRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Sheet2").Range("A2")
Application.CutCopyMode=False
End Sub