distribute data based on city name

[kshitij_dch]

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]

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]

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

[TABLE="width: 423"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]PSF Name[/TD]
[TD]City[/TD]
[TD]PSF Email ID's[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]CityA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]CityB[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]CityA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Name4[/TD]
[TD]CityD[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Name5[/TD]
[TD]CityD[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Name6[/TD]
[TD]CityA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Name7[/TD]
[TD]CityB[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Name8[/TD]
[TD]CityA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Name9[/TD]
[TD]CityA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Name10[/TD]
[TD]CityA[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2

[TABLE="width: 396"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]City[/TD]
[TD]name[/TD]
[TD]Email Id's[/TD]
[/TR]
[TR]
[TD]CityA[/TD]
[TD]Name1[/TD]
[TD]ksh.1@abc.com[/TD]
[/TR]
[TR]
[TD]CityA[/TD]
[TD]Name2[/TD]
[TD]hit.2@bcd.com[/TD]
[/TR]
[TR]
[TD]CityA[/TD]
[TD]Name3[/TD]
[TD]lou@def.com[/TD]
[/TR]
[TR]
[TD]CityB[/TD]
[TD]Name4[/TD]
[TD]mau1@fgh.com[/TD]
[/TR]
[TR]
[TD]CityB[/TD]
[TD]Name5[/TD]
[TD]tau2@hgf.com[/TD]
[/TR]
[TR]
[TD]CityD[/TD]
[TD]Name5[/TD]
[TD]tau2@hgf.com[/TD]
[/TR]
</tbody>[/TABLE]



Output in sheet1

[TABLE="width: 291"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]PSF Name[/TD]
[TD]City[/TD]
[TD]PSF Email ID's[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]CityA[/TD]
[TD]ksh.1@abc.com[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]CityB[/TD]
[TD]mau1@fgh.com[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]CityA[/TD]
[TD]hit.2@bcd.com[/TD]
[/TR]
[TR]
[TD]Name4[/TD]
[TD]CityD[/TD]
[TD]tau2@hgf.com[/TD]
[/TR]
[TR]
[TD]Name5[/TD]
[TD]CityD[/TD]
[TD]tau2@hgf.com[/TD]
[/TR]
[TR]
[TD]Name6[/TD]
[TD]CityA[/TD]
[TD]lou@def.com[/TD]
[/TR]
[TR]
[TD]Name7[/TD]
[TD]CityB[/TD]
[TD]tau2@hgf.com[/TD]
[/TR]
[TR]
[TD]Name8[/TD]
[TD]CityA[/TD]
[TD]ksh.1@abc.com[/TD]
[/TR]
[TR]
[TD]Name9[/TD]
[TD]CityA[/TD]
[TD]hit.2@bcd.com[/TD]
[/TR]
[TR]
[TD]Name10[/TD]
[TD]CityA[/TD]
[TD]lou@def.com[/TD]
[/TR]
</tbody>[/TABLE]

 

[Col Delane]

Mmm, this is an unusual kind of Excel problem.


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]

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]

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.ray:

 

[kshitij_dch]

Thanks a lot Col for all your help

 

[mse330]

While Col Delane is working on a non VBA solution, this is a VBA option if you're interested ...

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]

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

 

[mse330]

Does your data start in sheet1 start in cell A1 ?