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

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


0
Utf
2/26/2010 3:38:10 PM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
1351 Views

Similar Articles

[PageSpeed] 56

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
> 
> 
0
Utf
2/26/2010 3:54:01 PM
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@gmail.com
"Steve Muir" <SteveMuir@discussions.microsoft.com> wrote in message 
news:AE58A45F-DF10-4CA8-BF9E-967573E9D4AE@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
>
> 

0
Don
2/26/2010 4:03:22 PM
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
> > 
> > 
0
Utf
2/26/2010 4:29:03 PM
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@gmail.com
> "Steve Muir" <SteveMuir@discussions.microsoft.com> wrote in message 
> news:AE58A45F-DF10-4CA8-BF9E-967573E9D4AE@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
> >
> > 
> 
> .
> 
0
Utf
2/26/2010 4:56:06 PM
Or you can use data>autofilter>filter on the helper column for 1
-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Steve Muir" <SteveMuir@discussions.microsoft.com> wrote in message 
news:E62ADEE5-D7F2-4C61-9FD9-DCCB526127F6@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@gmail.com
>> "Steve Muir" <SteveMuir@discussions.microsoft.com> wrote in message
>> news:AE58A45F-DF10-4CA8-BF9E-967573E9D4AE@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
>> >
>> >
>>
>> .
>> 

0
Don
2/26/2010 10:28:04 PM
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

0
Jef
2/27/2010 6:19:47 PM
Reply:

Similar Artilces:

Need better way to provision AD accounts that are to have mailboxes
The exchange admins in my company have come up with a way for the sys admins to assign the user's mailbox store location. It's based off of last name. For example, if your last name starts with letter A, you go on Storage Group 1, Mailbox Store A, if your last name starts with letter B, you go on Storage Group 1, Mailbox Store B, if your last name starts with letter E, you go on Storage Group 2, Mailbox Store A, etc.... This is, I believe, and administrative nightmare. Is there a better way to provision AD accounts that are to have mailboxes? If it works for you and them, then al...

List Control
I want to add list control header like "First Column", "Second Column", I would like it is showed as 2 lines header by "First \nColumn", "Second \nColumn", But it does not work. How to do that? ...

Macro warning with no macros
I have a workbook that when I try to open it I get the warning tellin me it contains macros and asking whether I want to Enable or Disabl them. I put no macros, no VB scripts, nothing at all in the workboo other than standard Excel formulas and some charts. I developed it i Excel 2002, SP-2, but have moved it back and forth between tw computers, the other which has Excel 97. Regardless of whether Enable or Disable or change the security settings so as not to get th warning, once the workbook is open even if I go t Tools-Macro-Macros... it lists no macros. How can I find out what's cau...

deleting rows #4
In microsoft encell 2000 when i delete a row (say row 5) the row disapears but so does the row number then all other rows are moved up is there a way to delete the row and keep the number 5? The row number shouldn't disappear unless you are hiding the row, not deleting it -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Doctorpc631" <doctorpc631@aol.com> wrote in message news:e4FaNYV4EHA.1260@TK2MSFTNGP12.phx.gbl... > In microsoft encell 2000 when i delete a row (say row 5) the row > disapears > but s...

Another address list ?
Hi - I am running Exchange 2003 on Win2003 SBS edition. Could someone tell me whether it is possible to have another address list which exists separate to the GAL ? So for example if you compose a new message and click on the To button, I would like the users to see the GAL and also another entry underneath saying "External Emails" which will have the external user email addresses in it" How do you go about doing this ? Thanks in advance You would create this in the Address Lists container in Exchange System Manager. "Edward" <dont-mail@me.com> wrote...

Macro
Can anybody tell me how to kick off a macro automatically as file is opened? Regards ...

Macro
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: intel Hi, I'm thinking of buying Mac: Office 2008, but before I do I want to check that I will be able to download Scritpsmart collection of templates without any glitches: <http://www.bbc.co.uk/writersroom/scriptsmart/faq.shtml> Does anyone know if I can do this? Regards, SP. In article <ee98ff5.-1@webcrossing.caR9absDaxw>, StregaItalia@officeformac.com wrote: > Version: 2008 > Operating System: Mac OS X 10.4 (Tiger) > Processor: intel > > Hi, > > I'm thinking of buying Mac: Of...

Database is not listed in ODBC to default
Hi! I am trying to create a ODBC and default the Database in order for this ODBC to use in Crysral Reports, but I could see all Databases except the one I am looking for. I am using Windows Authentication and Namedpipes in the settings and necessary access has been given in SQL for this Windows user. I've tried even creating a new ODBC, it doesn't show the database that I'm looking for. Does any one experienced this before? Regards, Venkata N M Punugu If you are using Windows authentication, make sure the Windows user is added to the database. -- Charles Allen, MVP ...

Need to set extra hard return
Hi, This must be a very basic thing, but I have been asked to submit a paper with extra hard returns in Word. How do I do this? Can anyone help me out please? Thanks. Viju vijuhegde wrote: > This must be a very basic thing, but I have been asked to submit a > paper with extra hard returns in Word. How do I do this? Can anyone > help me out please? Ask whomever assigned this task to explain this task. Essentially a "hard return" signifies the end of a paragraph. It's simply pressing ENTER. The "soft return" is just a way to start a new line - h...

Run macro automatically.
How do I make a macro run automatically when a worksheet it is attached to is loaded? right click on the sheet tab>view code>left window worksheet>right window activate -- Don Guillett SalesAid Software donaldb@281.com "Excel macro" <Excel macro@discussions.microsoft.com> wrote in message news:DD7AF1E3-9263-4523-AC49-A43ABA1AB9D0@microsoft.com... > How do I make a macro run automatically when a worksheet it is attached to is > loaded? I am unable to find "activate " when I right click on worksheet. ( i assume that you are referring to the windo...

Help with macro/and or formula?
Perhaps this is a silly question, but i just can't figure out how t format this worksheet. The problem is that a lot of the cells hav numbers in them with negative signs in the back instead of in the fron (ie. 23-) and i was wondering if anyone knew of a macro/formula i coul use to quickly fix this problem for all the cells. I would b extremely grateful. Thanks -- brefed1 ----------------------------------------------------------------------- brefed15's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3557 View this thread: http://www.excelforum.com/showthre...

VBA code to say Yes or No if any VBA code is present in ActiveWork
Using XL 2003 & 97 Would like hit Ctrl+Shift+V and have an answer either yes or no as to the presence of Any VBA code in the Active Workbook. I do know that about the VBA editor/Explorer or to press the Run Macro button and get a list from various sources . I would like to stay at the keyboard and process a series of macros including one to let me know if I even need to evoke the VBA explorer. TIA Dennis There is no bootstrap macro to tell you whether you have macros or not. If there were there would be no limit to virus writers. --- HTH, David McRitchie, Microsoft MVP - Excel ...

Changing Rows to Columns
Is it possible to easily rotate a spreadsheet or part of a spreadshet, to change the data in rows to columns? Do to a sorting problem in the charts I create, it seems I have to sort the data in the spreadsheet first. Any help would be appreciated. Hi, Sure is a way. select & copy your data. then select the cell where you want to re-paste; right-click and select paste special; in the dialog box check "transpose". Ok. jeff >-----Original Message----- >Is it possible to easily rotate a spreadsheet or part of a >spreadshet, to change the data in rows to columns? D...

Attached Toolbars Macros
Excel 2003 I have a custom tool bar, this tool bar has several items, each item is pointing to a VBA code. This tool bar is attached to the worksheet. I've copied this spreadsheet to several computers. After I copied the spreadsheet I decided to rename the spreadsheet, after I renamed the spreadsheet the Toolbar Item's macros were still pointing to the old name, I reasigned each tool bar item to the current spreadsheet, saved the spreadsheet and everything was working on my computer, but when I re-copied the new spreadsheet to other computers and I clicked on the Toolbar Item...

Protected sheets and running macros
i have a sheet with a macro ran by a normal button at the top. Howeve when i protect the worksheet where ever i click on the sheet the macr executes. Can any one offer any advice on how to stop this? Thanks Robert -- Message posted from http://www.ExcelForum.com hi, i am not able to reproduce the problem, hence i may not be able t provide a panacea for that issue. here is what i suggest. i have a sheet that has a macro that is run when a control toolbo button (that is, not a form button) is clicked. this sheet is protected based on the user logged in. what i have don is to protect t...

VBA Command to Look at cell in an If statement
I need to create a macro in VBA that will look at a specific cell (which contains the result of a lookup formula) in an If statement. Basically it would look at a certian cell, and If that cell had the word "YES", then it would execute a seprate macro. If not, just continue on it merry way. I have tried the following and several variations: Sheets("Print Area").Select Range("BO8:BO8").Select If BO8 = YES Then Call PrintBatch What the program has to do is look at the last entry made in a specific column (hence the use of the Lookup formula in the cell) and...

Lists
Okay I am new to excell bar drawing little spreadsheets to do my own financing. I am currently setting up a sheet and need the users to be able to select from a list of options only and not input thier own text - does that make sense? also i ahve 12 names on the list and one master with all on - would like the data entered on to a names list to be automatically entered onto the master or vice versa which ever is easiest. sorry if this sounds jumbled but i am totally lost and this has to look really hot for work! regards Kelly - England Hi Kerry, don't know a great deal on forms ...

RE: last post about listing e-mail address rather than name.......
That is in Outlook Express 6.0. BTW, is there a separate newsgroup for Outlook Express...I couldn't find one?!!!!! ...

Macro Freezes Reading Pane
I have a macro that Sends/Receives Tasks from Project Server into Outlook Tasks. Everything is working fine, except one weird thing that happens. I have the Timer set on the macro and it starts as soon as Outlook starts. The macro gets the tasks from ProjectServer every hour. As soon as the macro starts running the Reading pane freezes in the Inbox. If i stop the macro then Reading pane is back to normal. Can anyone please tell me if there is something I can do in VBA to fix this annoying issue. Thanks a lot ...

Macro to remove rows?
Hi, I receive thousands of rows of data from an extract that comes from a database. The data extract automatically generates page headers that need to be removed, and I would love to automate this process. The headers do have a pattern to them, but I'm not clever enough to figure out a good way to use that to write a macro. The first row of the header starts with hyphens ------------------------------- Then there's a variable number of rows of header data to delete followed by another row of hyphens ------------------------------- Then 2 additional rows that must be deleted. The nu...

Calling on a macro within a macro!
Hey All!! Quick Question - How do you call up a macro within a current macro? I have one big macro and I need it to jump to several other macros when it runs! Does it matter where the other macros are stored or is it better to have them all in one module? Help appreciated Thnx! Hi Mellowe, '=============>> Public Sub Main() 'Your code Call One 'your code End Sub '<<============= '=============>> Public Sub One() MsgBox "Hi from One" End Sub '<<============= > Does it matter where the other macros are stored or ...

Please Help Me Secure My Form Results!!!
I have a FP 2003 form on a SSL page that contains confidential information. I would like to send it to a password protected page that my other employees can access with a unique username/password and then print the form results. I currently send it to the _private folder in formatted text– but I am the only one who can access it-- through FP. I tried to setup a subsite – but the Browse button in FrontPage Form Results does not see the subsite. I have tried other forums and paid for advice from “experts” on other sites – but no one seems to be able to tell me exactly how this ...

Trend Line constants as Excel Cell Values
I want to put the constants of a 2 degree polynomial trend line into Excel Cells. Are there formula's to do this? Equation is Y = Ax(2) + Bx + C. I want the value of A,B and C in seperate cells. Can anyone suggest how this can be done? Thanks -- Ken Hi Ken, Supposing your x-range is in A2:A10, and y-range in B2:B10, select a 3-column x 1-row area and enter the following formula and confirm with CTRL-SHIFT-ENTER. =LINEST(B2:B10,A2:A10^{0,1,2},0,) Regards, B. R. Ramachandran "Ken" wrote: > I want to put the constants of a 2 degree polynomial trend line into Excel &g...

Turn off auto adjust of row heights
I have a complex spreadsheet for my catalogue of products. I have set row heights, fonts and sizes of texts for different rows. Every now and then my row heights are automatically changed by EXCEL. I have turned off word wrap I have locked/protected my sheet. How can I stop EXCEL automatically readjusting row heights at its discretion. Thanks ...

shortcut keys to macros
Hi, Is it at all possible to assign shortcut keys to macros in Outlook? Thx, Hans ...