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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Try to change “A1” with “A2” in the whole code
 
Upvote 0
Nope , Not working , still showing same error on same line of code :(

I think there some issue with no of rows , at the moment I have 5000 rows of data , when i run the code it shows "Overflow Error" and when i run 2000 rows of data it is working fine.....
 
Last edited:
Upvote 0
This is awkward :confused: .. So without changing anything in the file except the number of rows (leads) in sheet1 the code works but if increased back again to ~5k rows it fails & it fails in the below line ?

' Get count of leads per city
Ar2 = Sheet1.Range("A1").CurrentRegion.Value '<--- This line ?!

What if you increase the data to 3k rows ? Does it still work ? Also, how many columns does sheet1 has ?
 
Last edited:
Upvote 0
Here's my non-VBA Excel Table based solution. It was definitely a challenging problem, but I think I've got there in the end!:biggrin:


[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
Data
Data
Data
Formula
Formula
Formula
Formula
Formula
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
Daily Update Table (tblDBase)
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
CityNameEmail Id's
City_Name Index
PSF Allocation​
PSF Allocation From​
PSF Allocation To​
City_Email Key​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
CityAName1ksh.1@abc.com
1​
4​
-​
4​
CityA1​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
CityAName2hit.2@bcd.com
2​
4​
5​
8​
CityA2​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
CityAName3lou@def.com
3​
3​
9​
11​
CityA3​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
CityBName4mau1@fgh.com
1​
2​
-​
2​
CityB1​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
CityBName5tau2@hgf.com
2​
1​
3​
3​
CityB2​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
CityDName5tau2@hgf.com
1​
2​
-​
2​
CityD1​

<tbody>
</tbody>
Sheet: Sheet2

<tbody>
</tbody>

Formulas:
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
Data
Data
Data
Formula
Formula
Formula
Formula
Formula
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
Daily Update Table (tblDBase)
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
CityNameEmail Id's
City_Name Index
PSF Allocation​
PSF Allocation From​
PSF Allocation To​
City_Email Key​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
CityAName1ksh.1@abc.com
= COUNTIFS( tblDBase[[#Headers],[City]]:$A4, tblDBase[[#This Row],[City]])​
= INDEX( tblCityCounts[Min PSFs PP], MATCH( tblDBase[[#This Row],[City]], tblCityCounts[City], 0 )) + IF( tblDBase[[#This Row],[City_Name Index]] <= INDEX( tblCityCounts[Leftover PSFs], MATCH( tblDBase[[#This Row],[City]], tblCityCounts[City], 0 )), 1, 0 )​
= IF( tblDBase[[#This Row],[City_Name Index]] = 1, 0, OFFSET(tblDBase[[#This Row],[PSF Allocation To]], -1, 0 ) + 1 )​
=SUMIFS( tblDBase[[#Headers],[PSF Allocation]]:$E4, tblDBase[[#Headers],[City]]:$A4, tblDBase[[#This Row],[City]] )​
= tblDBase[[#This Row],[City]] & tblDBase[[#This Row],[City_Name Index]]​

<tbody>
</tbody>
Sheet: Sheet2

<tbody>
</tbody>

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​
Data
Formula
Formula
Formula
Formula
Formula
Formula
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​
-​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​
Counts by City (tblCityCounts)
16
3
16
-
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​
City
Count
PSFs​
Min PSFs PP​
Leftover PSFs​
Allocated PSFs​
Allocation Check​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​
CityA
3​
11​
3​
2​
11​
-​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​
CityB
2​
3​
1​
1​
3​
-​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​
CityD
1​
2​
2​
-​
2​
-​

<tbody>
</tbody>
Sheet: Sheet2

<tbody>
</tbody>

Formulas:
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​
Data
Formula
Formula
Formula
Formula
Formula
Formula
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​
= $C$14 - COUNTA( tblPSF[PSF Name] )​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​
Counts by City (tblCityCounts)
= SUBTOTAL( 9, tblCityCounts[PSFs] )
= SUBTOTAL( 9, tblCityCounts[Leftover PSFs] )
= SUBTOTAL( 9, tblCityCounts[Allocated PSFs] )
= SUBTOTAL( 9, tblCityCounts[Allocation Check] )
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​
City
Count
PSFs​
Min PSFs PP​
Leftover PSFs​
Allocated PSFs​
Allocation Check​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​
CityA
= COUNTIFS( tblDBase[City], tblCityCounts[[#This Row],[City]] )​
= COUNTIFS( tblPSF[City], tblCityCounts[[#This Row],[City]] )​
= ROUNDDOWN( tblCityCounts[[#This Row],[PSFs]] / tblCityCounts[[#This Row],[Count]], 0 )​
= tblCityCounts[[#This Row],[PSFs]] - ( tblCityCounts[[#This Row],[Min PSFs PP]] * tblCityCounts[[#This Row],[Count]] )​
= SUMIFS( tblDBase[PSF Allocation], tblDBase[City], tblCityCounts[[#This Row],[City]] )​
= tblCityCounts[[#This Row],[PSFs]] - tblCityCounts[[#This Row],[Allocated PSFs]]​

<tbody>
</tbody>
Sheet: Sheet2

<tbody>
</tbody>


[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
Data
Data
Formula
Formula
Formula
Formula
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
Database (tblPSF)
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
16
16
16
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
PSF NameCity
City_PSF Index​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]PSF Allocation[/COLOR]
City_Name Index​
PSF Email ID's
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
Name1CityA
1​
4​
CityA1​
ksh.1@abc.com
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
Name3CityA
2​
4​
CityA1​
ksh.1@abc.com
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
Name6CityA
3​
4​
CityA1​
ksh.1@abc.com
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
Name8CityA
4​
4​
CityA1​
ksh.1@abc.com
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
Name9CityA
5​
8​
CityA2​
hit.2@bcd.com
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​
Name11CityA
6​
8​
CityA2​
hit.2@bcd.com
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​
Name12CityA
7​
8​
CityA2​
hit.2@bcd.com
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​
Name13CityA
8​
8​
CityA2​
hit.2@bcd.com
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​
Name14CityA
9​
11​
CityA3​
lou@def.com
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​
Name15CityA
10​
11​
CityA3​
lou@def.com
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​
Name16CityA
11​
11​
CityA3​
lou@def.com
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​
Name2CityB
1​
2​
CityB1​
mau1@fgh.com
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​
Name7CityB
2​
2​
CityB1​
mau1@fgh.com
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR]​
Name10CityB
3​
3​
CityB2​
tau2@hgf.com
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]20[/COLOR]​
Name4CityD
1​
2​
CityB1​
mau1@fgh.com
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]21[/COLOR]​
Name5CityD
2​
2​
CityB1​
mau1@fgh.com

<tbody>
</tbody>
Sheet: Sheet1

<tbody>
</tbody>

Formulas:
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
Data
Data
Formula
Formula
Formula
Formula
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
Database (tblPSF)
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
= SUBTOTAL( 3, tblPSF[PSF Name] )
= SUBTOTAL( 3, tblPSF[City] )
= SUBTOTAL( 3, tblPSF[PSF Email ID''s] )
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
PSF NameCity
City_PSF Index​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]PSF Allocation[/COLOR]
City_Name Index​
PSF Email ID's
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
Name1CityA
=COUNTIFS( tblPSF[[#Headers],[City]]:$B6, tblPSF[[#This Row],[City]] )​
= SUMPRODUCT( ( tblPSF[[#This Row],[City]] = tblDBase[City] ) * ( tblPSF[[#This Row],[City_PSF Index]] >= tblDBase[PSF Allocation From] ) * (tblPSF[[#This Row],[City_PSF Index]] <= tblDBase[PSF Allocation To] ) * ( tblDBase[PSF Allocation To] ) )​
= INDEX( tblDBase[City_Email Key], MATCH( tblPSF[[#This Row],[PSF Allocation]], tblDBase[PSF Allocation To], 0 ))​
= INDEX( tblDBase[Email Id''s], MATCH( [City_Name Index], tblDBase[City_Email Key], 0 ) )

<tbody>
</tbody>
Sheet: Sheet1

<tbody>
</tbody>
 
Upvote 0
maybe something like this?

PSF NameCityEmail Id's
Name1CityAksh.1@abc.com
Name1CityAhit.2@bcd.com
Name1CityAlou@def.com
Name1CityAmau1@fgh.com
Name1CityAtau2@hgf.com
Name2CityBksh.1@abc.com
Name2CityBhit.2@bcd.com
Name2CityBlou@def.com
Name2CityBmau1@fgh.com
Name2CityBtau2@hgf.com
Name3CityAksh.1@abc.com
Name3CityAhit.2@bcd.com
Name3CityAlou@def.com
Name3CityAmau1@fgh.com
Name3CityAtau2@hgf.com
Name4CityDksh.1@abc.com
Name4CityDhit.2@bcd.com
Name4CityDlou@def.com
Name4CityDmau1@fgh.com
Name4CityDtau2@hgf.com
Name5CityDksh.1@abc.com
Name5CityDhit.2@bcd.com
Name5CityDlou@def.com
Name5CityDmau1@fgh.com
Name5CityDtau2@hgf.com
Name6CityAksh.1@abc.com
Name6CityAhit.2@bcd.com
Name6CityAlou@def.com
Name6CityAmau1@fgh.com
Name6CityAtau2@hgf.com
Name7CityBksh.1@abc.com
Name7CityBhit.2@bcd.com
Name7CityBlou@def.com
Name7CityBmau1@fgh.com
Name7CityBtau2@hgf.com
Name8CityAksh.1@abc.com
Name8CityAhit.2@bcd.com
Name8CityAlou@def.com
Name8CityAmau1@fgh.com
Name8CityAtau2@hgf.com
Name9CityAksh.1@abc.com
Name9CityAhit.2@bcd.com
Name9CityAlou@def.com
Name9CityAmau1@fgh.com
Name9CityAtau2@hgf.com
Name10CityAksh.1@abc.com
Name10CityAhit.2@bcd.com
Name10CityAlou@def.com
Name10CityAmau1@fgh.com
Name10CityAtau2@hgf.com
 
Upvote 0
I think I figured out the issue with my previous code. Can you re-try the updated code below let me know how it goes

Code:
Sub AllocateLeads2()
Dim Dic As Object, k As Variant, Ar1 As Variant, Ar2 As Variant, Cnt As Long, ArIndex As Long
Set Dic = CreateObject("Scripting.Dictionary")
Ar1 = Sheet2.Range("A1").CurrentRegion.Value
For x = 2 To UBound(Ar1)
    If Not Dic.exists(Ar1(x, 1)) Then
        Dic.Add Ar1(x, 1), Ar1(x, 3)
    Else
        Dic(Ar1(x, 1)) = Dic(Ar1(x, 1)) & "," & Ar1(x, 3)
    End If
Next x
ReDim Ar1(1 To Dic.Count, 1 To 4)
For Each k In Dic.keys
    Cnt = Cnt + 1
    Ar1(Cnt, 1) = k: Ar1(Cnt, 2) = Len(Dic(k)) - Len(Replace(Dic(k), ",", "")) + 1: Ar1(Cnt, 3) = 0: Ar1(Cnt, 4) = Dic(k)
Next
Ar2 = Sheet1.Range("A1").CurrentRegion.Value
For x = 2 To UBound(Ar2)
    For y = LBound(Ar1) To UBound(Ar1)
        If Ar2(x, 2) = Ar1(y, 1) Then ArIndex = y: Exit For
    Next y
    
    If Ar1(ArIndex, 2) = 1 Or Ar1(ArIndex, 3) >= Ar1(ArIndex, 2) Then
        Ar1(ArIndex, 3) = 1
    Else
        Ar1(ArIndex, 3) = Ar1(ArIndex, 3) + 1
    End If
    Ar2(x, 3) = Split(Ar1(ArIndex, 4), ",")(Ar1(ArIndex, 3) - 1)
Next x
Sheet1.Range("A1").Resize(UBound(Ar2), UBound(Ar2, 2)).Value = Ar2
End Sub
 
Upvote 0
Hi Mse300

Code is working fine at the moment , will let you know if any problem arises , Thanks for your Help .


Hi Col,

Can you share workbook for my reference ??
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

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