distribute data based on city name

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
645
Office Version
365
Platform
Windows
Try to change “A1” with “A2” in the whole code
 

kshitij_dch

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

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
645
Office Version
365
Platform
Windows
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:

Col Delane

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

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,318
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
 

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
645
Office Version
365
Platform
Windows
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
 

kshitij_dch

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

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
645
Office Version
365
Platform
Windows
Glad to help & thanks for reporting back :)
 

Watch MrExcel Video

Forum statistics

Threads
1,099,020
Messages
5,466,091
Members
406,464
Latest member
buks1232000

This Week's Hot Topics

Top