Prev: Data Connection: Access Query to Excel How to get rid of heade
Next: #VALUE ERROR
From: Steve Muir on 26 Feb 2010 11:29
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 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
> >
> >
From: Steve Muir on 26 Feb 2010 11:56
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" wrote:

> 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)
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett(a)gmail.com
> "Steve Muir" <SteveMuir(a)discussions.microsoft.com> wrote in message
> news:AE58A45F-DF10-4CA8-BF9E-967573E9D4AE(a)microsoft.com...
> >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
> >
> >
>
> .
>
From: Don Guillett on 26 Feb 2010 17:28

Or you can use data>autofilter>filter on the helper column for 1
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Steve Muir" <SteveMuir(a)discussions.microsoft.com> wrote in message
news:E62ADEE5-D7F2-4C61-9FD9-DCCB526127F6(a)microsoft.com...
> 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" wrote:
>
>> 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)
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett(a)gmail.com
>> "Steve Muir" <SteveMuir(a)discussions.microsoft.com> wrote in message
>> news:AE58A45F-DF10-4CA8-BF9E-967573E9D4AE(a)microsoft.com...
>> >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
>> >
>> >
>>
>> .
>>

From: Jef Gorbach on 27 Feb 2010 13:19
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

 | 
Pages: 1
Prev: Data Connection: Access Query to Excel How to get rid of heade
Next: #VALUE ERROR