Multiple corresponding values (looping)

investor9987

New Member
Joined
Jul 26, 2007
Messages
9
I'm using Excel 2003:

I have agents that want to "own" records depending on the zip code. I want to dynamically assign agent to a record based on the zip code. If there was only one agent per zip code, this could easily be done with Lookup. However, in many instances there will be multiple agents for a zip code. In these cases, I was hoping to assign the agents to each record in a round robin fashion.

My agent/zip table would may look something like this:

37011 Smith
37013 Jones
37023 Herrman
37025 Martin
37025 Slater
37025 ORiley
37028 Phelps
37028 Trenton
37029 Reagan

I would then have another sheet made up of home addresses that each have a zip-code. If the record contains zip 37013, I can easily assign that one to Jones. However, if the zip in the record is 37025, there are three agents sharing that zip. So for the first record with 37025, I would assign Martin. The next occurence of 37025 would be Slater and the third would be ORiley. The fourth occurence would go back to Martin and so on.

I have found some versions of what I want to do on this site and others but can't find the looping capability. Is it possible?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi

Number of each zip code in both sheet always match?

I mean, if 37023 are found 3 in one sheet, same 3 always appear in another sheet?
 

investor9987

New Member
Joined
Jul 26, 2007
Messages
9
There is only one zip-code table. I guess you could consider it a master table. All of my other sheets that contain homeowners records would then go this one zip-code table to dynamically assign an agent to each homeowner record based on the zip-code.

Does this clarify?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
It is still not clear to me....
Can you show us a sample with before/after ?
 

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
I'm sure kindon will come in an school me with a better way but try this:

this assumes 2 sheets called "Zip Table" and "Data"

the data on zip table looks like:
Code:
       A      B
1   37011	Smith
2   37013	Jones
3   37023	Herrman
4   37025	Martin
5   37025	Slater
6   37025	ORiley
7   37028	Phelps
8   37028	Trenton
9   37029	Reagan
the data on the Data sheet looks like
Code:
    A                    B                 C-wahtever
1  Zip Code         Agent          Housing Details
2
3   37011
4   37013
5   37011
6   37013
etc

Code:
Sub assignagents()

Dim i, j
Dim tablesheet As Worksheet
Dim datasheet As Worksheet
Dim startrow, endrow
Dim numagt
    

Set tablesheet = Sheets("Zip Table")
Set datasheet = Sheets("Data")

currow = 1
startrow = 1

For i = 1 To tablesheet.Range("A1").End(xlDown).Row ' where A1 is the first agents assigned zip code and B1 is name
        
        If tablesheet.Range("A" & i).Value <> tablesheet.Range("A" & i + 1).Value Then
            endrow = i
            numagt = endrow - startrow
            ctr = numagt
            
            For j = 1 To datasheet.Range("A3").End(xlDown).Row ' where A3 is first record in datasheet and column A is all the zipcodes
                If datasheet.Range("A" & j).Value = tablesheet.Range("A" & i).Value Then
                    datasheet.Range("B" & j) = tablesheet.Range("B" & (startrow + ctr)).Value ' where column B in datasheet is where agents name will go
                    If ctr = 0 Then
                        ctr = numagt
                    Else
                        ctr = ctr - 1
                    End If
                End If
            Next j
            startrow = i + 1
        End If
Next i



End Sub
 

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
I'm sure kindon will come in an school me with a better way but try this:

this assumes 2 sheets called "Zip Table" and "Data"

the data on zip table looks like:
Code:
       A      B
1   37011	Smith
2   37013	Jones
3   37023	Herrman
4   37025	Martin
5   37025	Slater
6   37025	ORiley
7   37028	Phelps
8   37028	Trenton
9   37029	Reagan
the data on the Data sheet looks like
Code:
    A                    B                 C-wahtever
1  Zip Code         Agent          Housing Details
2
3   37011
4   37013
5   37011
6   37013
etc

Code:
Sub assignagents()

Dim i, j
Dim tablesheet As Worksheet
Dim datasheet As Worksheet
Dim startrow, endrow
Dim numagt
    

Set tablesheet = Sheets("Zip Table")
Set datasheet = Sheets("Data")

currow = 1
startrow = 1

For i = 1 To tablesheet.Range("A1").End(xlDown).Row ' where A1 is the first agents assigned zip code and B1 is name
        
        If tablesheet.Range("A" & i).Value <> tablesheet.Range("A" & i + 1).Value Then
            endrow = i
            numagt = endrow - startrow
            ctr = numagt
            
            For j = 1 To datasheet.Range("A3").End(xlDown).Row ' where A3 is first record in datasheet and column A is all the zipcodes
                If datasheet.Range("A" & j).Value = tablesheet.Range("A" & i).Value Then
                    datasheet.Range("B" & j) = tablesheet.Range("B" & (startrow + ctr)).Value ' where column B in datasheet is where agents name will go
                    If ctr = 0 Then
                        ctr = numagt
                    Else
                        ctr = ctr - 1
                    End If
                End If
            Next j
            startrow = i + 1
        End If
Next i



End Sub
 

investor9987

New Member
Joined
Jul 26, 2007
Messages
9
Further Clarification for Jindon

OK - here is a recap. The zip code/agent table is in one sheet of my excel file. It would look something like this. Note that there are three agents sharing Zip 37025 and two agents sharing 37028.

A B
1 37011 Smith
2 37013 Jones
3 37023 Herrman
4 37025 Martin
5 37025 Slater
6 37025 ORiley
7 37028 Phelps
8 37028 Trenton
9 37029 Reagan

In another sheet, I have records and I am trying to come up with a formula for the Agent column that would assign the correct agent based on the zipcode/agent table above. If only one agent were allowed per zip code, I could do that. However, since agents are allowed to share the zip codes, I have to figure out a way to assign an agent to particular record in a round robin fashion. Before I assing the formula to the agent column, it would look something like this:

Address Zip Agent
212 Green Street 37011
140 5th Street 37025
219 Navaho Lane 37028
312 Orange Rd 37025
250 Blakely Ct 37028
470 Birdie Lane 37011
310 Sommerset Pl 37025
520 Navaho Lane 37023
490 Cherokee Ct 37025
320 Kentons Way 37028
325 Kentons Way 37028

If I can find the correct array formula to fill in the Agent column, the results would look like this:

Address Zip Agent
212 Green Street 37011 Smith
140 5th Street 37025 Martin
219 Navaho Lane 37028 Phelps
312 Orange Rd 37025 Slater
250 Blakely Ct 37028 Trenton
470 Birdie Lane 37011 Smith
310 Sommerset Pl 37025 Oriley
520 Navaho Lane 37023 Herrman
490 Cherokee Ct 37025 Martin (this is the 4th 37025 so we loop back and assign Martin and then on down)
320 Kentons Way 37028 Phelps (3rd occurrence of 37028 so we loop back to Phelps)
325 Kentons Way 37028 Trenton

I just did a preview and my spacing stinks. Hope you can still follow.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi

Number of each zip code in both sheet always match?

I mean, if 37023 are found 3 in one sheet, same 3 always appear in another sheet?

Same question,

Does number of each zip code appearance always match with the one in other sheet?
 

investor9987

New Member
Joined
Jul 26, 2007
Messages
9
there could be a record with a zipcode that does not match any of the zipcodes in the zip/agent table. On those, that could be an error.
 

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
ok this should work then, right click on the sheet tab and go to view code, then on the left right click on microsoft excel objects and go Insert > module, then paste this.

Code:
Sub assignagents() 

Dim i, j 
Dim tablesheet As Worksheet 
Dim datasheet As Worksheet 
Dim startrow, endrow 
Dim numagt 
    

Set tablesheet = Sheets("Zip Table") ' change these to ur sheet names
Set datasheet = Sheets("Data") 

currow = 1 
startrow = 1 

For i = 1 To tablesheet.Range("A1").End(xlDown).Row ' where A1 is the first agents assigned zip code and B1 is name 
        
        If tablesheet.Range("A" & i).Value <> tablesheet.Range("A" & i + 1).Value Then 
            endrow = i 
            numagt = endrow - startrow 
            ctr = numagt 
            
            For j = 1 To datasheet.Range("A1").End(xlDown).Row
                If datasheet.Range("A" & j).Value = tablesheet.Range("B" & i).Value Then 
                    datasheet.Range("C & j) = tablesheet.Range("B" & (startrow + ctr)).Value ' where column B in datasheet is where agents name will go 
                    If ctr = 0 Then 
                        ctr = numagt 
                    Else 
                        ctr = ctr - 1 
                    End If 
                End If 
            Next j 
            startrow = i + 1 
        End If 
Next i 



End Sub
 

Forum statistics

Threads
1,181,719
Messages
5,931,656
Members
436,797
Latest member
fullmetalpogi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top