distribute data based on city name

kshitij_dch

Active Member
Joined
Apr 1, 2012
Messages
335
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.
 

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
303
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."?
 

kshitij_dch

Active Member
Joined
Apr 1, 2012
Messages
335
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>
 

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
303
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?
 

kshitij_dch

Active Member
Joined
Apr 1, 2012
Messages
335
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.
 

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
303
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:
 

kshitij_dch

Active Member
Joined
Apr 1, 2012
Messages
335
Thanks a lot Col for all your help :)
 

mse330

Active Member
Joined
Oct 18, 2007
Messages
449
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
 

kshitij_dch

Active Member
Joined
Apr 1, 2012
Messages
335
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
 

Forum statistics

Threads
1,078,521
Messages
5,340,942
Members
399,399
Latest member
SravanaSandhya

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top