Multiple corresponding values (looping)

[investor9987]

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?

 

[jindon]

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]

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]

It is still not clear to me....
Can you show us a sample with before/after ?

 

[zzjasonzz]

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:

       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

    A                    B                 C-wahtever
1  Zip Code         Agent          Housing Details
2
3   37011
4   37013
5   37011
6   37013
etc

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]

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:

       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

    A                    B                 C-wahtever
1  Zip Code         Agent          Housing Details
2
3   37011
4   37013
5   37011
6   37013
etc

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]

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]

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]

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]

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.

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