distribute data based on city name

kshitij_dch

Active Member
Joined
Apr 1, 2012
Messages
362
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
Hello Folks ,

I have a workbook with 2 sheets (Sheet1 and Sheet2)

Sheet1 has 3 columns (Column A- Name , Column B - City , Column C - PSF ID's) , Name and City is mapped and PSF ID's column is blank

Sheet2 has 3 columns (City Column A, Name Column B, PSF ID's Column C) all the 3 columns are mapped. One City name has many psf names and ID's (could be 5 , 6 or more than 6)

I need to manually check Sheet1 City name (column B) , Suppose i have selected one city that has 50 rows of data , then i have to look in sheet2 PSF ID's (Column C) against city name selected in Sheet1 , that city has 5 Names and ID's , i need to manually distribute ID's equally to 50 rows of data in Sheet1 in Column C against that city name and same process for all cities.

I am looking for a macro that can automatically look for ID's against city names and equally distribute data against city name in sheet1.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Questions:

  1. What's a PSF?
  2. Can you provide some sample data for Sheet1 and Sheet2?
  3. What do you mean by "...equally distribute data against city name in sheet1."?
 
Upvote 0
1. PSF are the Email ID's
2. I don't have access to attach files however will try to give you an image of sheet 1 and Sheet 2
3.I mean that suppose one city name "A" has 5 psf Email ID's in sheet 2 and in sheet1 City name "A" has 50 rows of data , 5 email equally pasted in sheet1 column 3 50/5 that is each email id will be pasted 10 times in column C in sheet 1.

Sheet1

PSF NameCityPSF Email ID's
Name1CityA
Name2CityB
Name3CityA
Name4CityD
Name5CityD
Name6CityA
Name7CityB
Name8CityA
Name9CityA
Name10CityA

<colgroup><col><col><col></colgroup><tbody>
</tbody>


Sheet2

CitynameEmail Id's
CityAName1ksh.1@abc.com
CityAName2hit.2@bcd.com
CityAName3lou@def.com
CityBName4mau1@fgh.com
CityBName5tau2@hgf.com
CityDName5tau2@hgf.com

<colgroup><col span="3"></colgroup><tbody>
</tbody>



Output in sheet1

PSF NameCityPSF Email ID's
Name1CityAksh.1@abc.com
Name2CityBmau1@fgh.com
Name3CityAhit.2@bcd.com
Name4CityDtau2@hgf.com
Name5CityDtau2@hgf.com
Name6CityAlou@def.com
Name7CityBtau2@hgf.com
Name8CityAksh.1@abc.com
Name9CityAhit.2@bcd.com
Name10CityAlou@def.com

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Mmm, this is an unusual kind of Excel problem.:eek:


1. Is this a once-off task, or something to be done regularly?
2. If regularly, what changes - the data in Sheet 1, Sheet 2, or both?

3. Obviously, not all ratios of City data rows to Email IDs will be a whole number (e.g. 50/5 = 10), so what's your business rule to deal with that (i.e. where is there is fraction remaining like with 20/6)? Do you expect the allocation to complete the last full block of IDs and then just allocate the next block until the last data row has an ID. For example, say 20 data rows and 6 IDs = 3.33 allocation blocks. So would you allocate 3 blocks of 6 (=18) and then just allocate the next 2 IDs in the block to the last 2 data rows?

4. Can the data in each sheet be sorted as part of the solution?

5. What's the difference or correlation between the PSF Names in Sheet1 and the Names in Sheet2?
 
Upvote 0
Hello Col,

Yes its a office task and done regularly every day , let me tell you the procedure what i do everyday.

I have a workbook , that has 2 sheets (1 and 2) , sheet 2 is a database of cities in A Column and email id's of persons who represent a particular city in C Column.

Sheet1 is something my team update daily , They update daily leads generated by each of them.They update PSF Name and City Name manually for each lead.At the end of the day team manager need to send city wise leads to city wise PSF email Id's to get them actioned .Each PSF email ID should get equal leads to action.(Suppose 20 leads for a particular city and 3 PSF persons representing that city , should get equal leads between 3 of them. 20 leads means 2 persons get 7 leads each and one will get 6 leads.

This distribution of leads done manually and take lot of time because after distribution mail is also marked to each one of them with the leads pasted in the body of the mail.

If the above work is automated , i will be able to save 2-3 hours of mine.
 
Upvote 0
I started to work on a VBA solution for this issue, but as I was building helper columns to make the coding easier, I realized that I could build the whole solution without VBA by using Excel Tables and formulas!

I'm almost done - just having trouble with a couple of formulas - & will post as soon as it's working.:pray:
 
Upvote 0
While Col Delane is working on a non VBA solution, this is a VBA option if you're interested ...

Code:
Sub Allocate_Leads()
Dim Dic As Object, k As Variant, Ar1 As Variant, Ar2 As Variant, Ar3 As Variant, Cnt As Long, ArIndex As Long
Set Dic = CreateObject("Scripting.Dictionary")
Ar1 = Sheet2.Range("A1").CurrentRegion.Value
' Get count of agents per city
For x = 2 To UBound(Ar1)
    If Not Dic.exists(Ar1(x, 1)) Then
        Dic.Add Ar1(x, 1), 1
    Else
        Dic(Ar1(x, 1)) = Dic(Ar1(x, 1)) + 1
    End If
Next x
ReDim Ar1(1 To Dic.Count, 1 To 5)
For Each k In Dic.keys
    Cnt = Cnt + 1
    Ar1(Cnt, 1) = k: Ar1(Cnt, 2) = Dic(k): Ar1(Cnt, 3) = 0: Ar1(Cnt, 4) = 0: Ar1(Cnt, 5) = 0
Next
' Get count of leads per city
Ar2 = Sheet1.Range("A1").CurrentRegion.Value
For x = 2 To UBound(Ar2)
    If Not Dic.exists(Ar2(x, 2)) Then
        Dic.Add Ar2(x, 2), 1
    Else
        Dic(Ar2(x, 2)) = Dic(Ar2(x, 2)) + 1
    End If
Next x
For x = LBound(Ar1) To UBound(Ar1)
    Ar1(x, 3) = Dic(Ar1(x, 1))
    Ar1(x, 4) = Int(Ar1(x, 3) / Ar1(x, 2))
Next
Dic.RemoveAll
Ar2 = Sheet2.Range("A1").CurrentRegion.Value
For x = 2 To UBound(Ar2)
    If Not Dic.exists(Ar2(x, 1)) Then
        Dic.Add Ar2(x, 1), Ar2(x, 3)
    Else
        Dic(Ar2(x, 1)) = Dic(Ar2(x, 1)) & "," & Ar2(x, 3)
    End If
Next x
Ar3 = Sheet1.Range("A1").CurrentRegion.Value
For x = 2 To UBound(Ar3)
    For y = LBound(Ar1) To UBound(Ar1)
        If Ar3(x, 2) = Ar1(y, 1) Then
            ArIndex = y
            Exit For
        End If
    Next y
    
    If Ar1(ArIndex, 2) = 1 Or Ar1(ArIndex, 5) > Ar1(ArIndex, 4) - 1 Then
        Ar1(ArIndex, 5) = 1
    Else
        Ar1(ArIndex, 5) = Ar1(ArIndex, 5) + 1
    End If
    Ar3(x, 3) = Split(Dic(Ar3(x, 2)), ",")(Ar1(ArIndex, 5) - 1)
Next x
Sheet1.Range("A1").Resize(UBound(Ar3), UBound(Ar3, 2)).Value = Ar3
End Sub
 
Upvote 0
Hello mse300 ,

Thank you for the response , i tried the above code , Showing Error "Overflow"

Get count of leads per city
Ar2 = Sheet1.Range("A1").CurrentRegion.Value
 
Upvote 0
Does your data start in sheet1 start in cell A1 ?
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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
Back
Top