distribute data based on city name

K

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.
 
Sort by date Sort by votes

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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!:grin:


[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​
[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
[/TD]
[TD]
Data​
[/TD]
[TD]
Data​
[/TD]
[TD]
Data​
[/TD]
[TD]
Formula​
[/TD]
[TD]
Formula​
[/TD]
[TD]
Formula​
[/TD]
[TD]
Formula​
[/TD]
[TD]
Formula​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
[/TD]
[TD]Daily Update Table (tblDBase)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
[/TD]
[TD]City[/TD]
[TD]Name[/TD]
[TD]Email Id's[/TD]
[TD]
City_Name Index​
[/TD]
[TD]
PSF Allocation​
[/TD]
[TD]
PSF Allocation From​
[/TD]
[TD]
PSF Allocation To​
[/TD]
[TD]
City_Email Key​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
[/TD]
[TD]CityA[/TD]
[TD]Name1[/TD]
[TD]ksh.1@abc.com[/TD]
[TD]
1​
[/TD]
[TD]
4​
[/TD]
[TD]
-​
[/TD]
[TD]
4​
[/TD]
[TD]
CityA1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
[/TD]
[TD]CityA[/TD]
[TD]Name2[/TD]
[TD]hit.2@bcd.com[/TD]
[TD]
2​
[/TD]
[TD]
4​
[/TD]
[TD]
5​
[/TD]
[TD]
8​
[/TD]
[TD]
CityA2​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
[/TD]
[TD]CityA[/TD]
[TD]Name3[/TD]
[TD]lou@def.com[/TD]
[TD]
3​
[/TD]
[TD]
3​
[/TD]
[TD]
9​
[/TD]
[TD]
11​
[/TD]
[TD]
CityA3​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
[/TD]
[TD]CityB[/TD]
[TD]Name4[/TD]
[TD]mau1@fgh.com[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
-​
[/TD]
[TD]
2​
[/TD]
[TD]
CityB1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
[/TD]
[TD]CityB[/TD]
[TD]Name5[/TD]
[TD]tau2@hgf.com[/TD]
[TD]
2​
[/TD]
[TD]
1​
[/TD]
[TD]
3​
[/TD]
[TD]
3​
[/TD]
[TD]
CityB2​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
[/TD]
[TD]CityD[/TD]
[TD]Name5[/TD]
[TD]tau2@hgf.com[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
-​
[/TD]
[TD]
2​
[/TD]
[TD]
CityD1​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet2[/TD]
[/TR]
</tbody>[/TABLE]

Formulas:
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​
[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
[/TD]
[TD]
Data​
[/TD]
[TD]
Data​
[/TD]
[TD]
Data​
[/TD]
[TD]
Formula​
[/TD]
[TD]
Formula​
[/TD]
[TD]
Formula​
[/TD]
[TD]
Formula​
[/TD]
[TD]
Formula​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
[/TD]
[TD]Daily Update Table (tblDBase)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
[/TD]
[TD]City[/TD]
[TD]Name[/TD]
[TD]Email Id's[/TD]
[TD]
City_Name Index​
[/TD]
[TD]
PSF Allocation​
[/TD]
[TD]
PSF Allocation From​
[/TD]
[TD]
PSF Allocation To​
[/TD]
[TD]
City_Email Key​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
[/TD]
[TD]CityA[/TD]
[TD]Name1[/TD]
[TD]ksh.1@abc.com[/TD]
[TD]
= COUNTIFS( tblDBase[[#Headers],[City]]:$A4, tblDBase[[#This Row],[City]])​
[/TD]
[TD]
= 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 )​
[/TD]
[TD]
= IF( tblDBase[[#This Row],[City_Name Index]] = 1, 0, OFFSET(tblDBase[[#This Row],[PSF Allocation To]], -1, 0 ) + 1 )​
[/TD]
[TD]
=SUMIFS( tblDBase[[#Headers],[PSF Allocation]]:$E4, tblDBase[[#Headers],[City]]:$A4, tblDBase[[#This Row],[City]] )​
[/TD]
[TD]
= tblDBase[[#This Row],[City]] & tblDBase[[#This Row],[City_Name Index]]​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet2[/TD]
[/TR]
</tbody>[/TABLE]

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

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


[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
[/TD]
[TD]
Data​
[/TD]
[TD]
Data​
[/TD]
[TD]
Formula​
[/TD]
[TD]
Formula​
[/TD]
[TD]
Formula​
[/TD]
[TD]
Formula​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
[/TD]
[TD]Database (tblPSF)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
[/TD]
[TD]
16 ​
[/TD]
[TD]
16 ​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
16 ​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
[/TD]
[TD]PSF Name[/TD]
[TD]City[/TD]
[TD]
City_PSF Index​
[/TD]
[TD="bgcolor: #4F81BD"]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]PSF Allocation[/COLOR]​
[/TD]
[TD]
City_Name Index​
[/TD]
[TD]
PSF Email ID's​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
[/TD]
[TD]Name1[/TD]
[TD]CityA[/TD]
[TD]
1​
[/TD]
[TD]
4​
[/TD]
[TD]
CityA1​
[/TD]
[TD]ksh.1@abc.com[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
[/TD]
[TD]Name3[/TD]
[TD]CityA[/TD]
[TD]
2​
[/TD]
[TD]
4​
[/TD]
[TD]
CityA1​
[/TD]
[TD]ksh.1@abc.com[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
[/TD]
[TD]Name6[/TD]
[TD]CityA[/TD]
[TD]
3​
[/TD]
[TD]
4​
[/TD]
[TD]
CityA1​
[/TD]
[TD]ksh.1@abc.com[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
[/TD]
[TD]Name8[/TD]
[TD]CityA[/TD]
[TD]
4​
[/TD]
[TD]
4​
[/TD]
[TD]
CityA1​
[/TD]
[TD]ksh.1@abc.com[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
[/TD]
[TD]Name9[/TD]
[TD]CityA[/TD]
[TD]
5​
[/TD]
[TD]
8​
[/TD]
[TD]
CityA2​
[/TD]
[TD]hit.2@bcd.com[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​
[/TD]
[TD]Name11[/TD]
[TD]CityA[/TD]
[TD]
6​
[/TD]
[TD]
8​
[/TD]
[TD]
CityA2​
[/TD]
[TD]hit.2@bcd.com[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​
[/TD]
[TD]Name12[/TD]
[TD]CityA[/TD]
[TD]
7​
[/TD]
[TD]
8​
[/TD]
[TD]
CityA2​
[/TD]
[TD]hit.2@bcd.com[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​
[/TD]
[TD]Name13[/TD]
[TD]CityA[/TD]
[TD]
8​
[/TD]
[TD]
8​
[/TD]
[TD]
CityA2​
[/TD]
[TD]hit.2@bcd.com[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​
[/TD]
[TD]Name14[/TD]
[TD]CityA[/TD]
[TD]
9​
[/TD]
[TD]
11​
[/TD]
[TD]
CityA3​
[/TD]
[TD]lou@def.com[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​
[/TD]
[TD]Name15[/TD]
[TD]CityA[/TD]
[TD]
10​
[/TD]
[TD]
11​
[/TD]
[TD]
CityA3​
[/TD]
[TD]lou@def.com[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​
[/TD]
[TD]Name16[/TD]
[TD]CityA[/TD]
[TD]
11​
[/TD]
[TD]
11​
[/TD]
[TD]
CityA3​
[/TD]
[TD]lou@def.com[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​
[/TD]
[TD]Name2[/TD]
[TD]CityB[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
CityB1​
[/TD]
[TD]mau1@fgh.com[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​
[/TD]
[TD]Name7[/TD]
[TD]CityB[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
CityB1​
[/TD]
[TD]mau1@fgh.com[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR]​
[/TD]
[TD]Name10[/TD]
[TD]CityB[/TD]
[TD]
3​
[/TD]
[TD]
3​
[/TD]
[TD]
CityB2​
[/TD]
[TD]tau2@hgf.com[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]20[/COLOR]​
[/TD]
[TD]Name4[/TD]
[TD]CityD[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
CityB1​
[/TD]
[TD]mau1@fgh.com[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]21[/COLOR]​
[/TD]
[TD]Name5[/TD]
[TD]CityD[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
CityB1​
[/TD]
[TD]mau1@fgh.com[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

Formulas:
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
[/TD]
[TD]
Data​
[/TD]
[TD]
Data​
[/TD]
[TD]
Formula​
[/TD]
[TD]
Formula​
[/TD]
[TD]
Formula​
[/TD]
[TD]
Formula​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
[/TD]
[TD]Database (tblPSF)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
[/TD]
[TD]
= SUBTOTAL( 3, tblPSF[PSF Name] )​
[/TD]
[TD]
= SUBTOTAL( 3, tblPSF[City] )​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
= SUBTOTAL( 3, tblPSF[PSF Email ID''s] )​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
[/TD]
[TD]PSF Name[/TD]
[TD]City[/TD]
[TD]
City_PSF Index​
[/TD]
[TD="bgcolor: #4F81BD"]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]PSF Allocation[/COLOR]​
[/TD]
[TD]
City_Name Index​
[/TD]
[TD]
PSF Email ID's​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
[/TD]
[TD]Name1[/TD]
[TD]CityA[/TD]
[TD]
=COUNTIFS( tblPSF[[#Headers],[City]]:$B6, tblPSF[[#This Row],[City]] )​
[/TD]
[TD]
= 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] ) )​
[/TD]
[TD]
= INDEX( tblDBase[City_Email Key], MATCH( tblPSF[[#This Row],[PSF Allocation]], tblDBase[PSF Allocation To], 0 ))​
[/TD]
[TD]= INDEX( tblDBase[Email Id''s], MATCH( [City_Name Index], tblDBase[City_Email Key], 0 ) )[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
maybe something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]PSF Name[/td][td=bgcolor:#70AD47]City[/td][td=bgcolor:#70AD47]Email Id's[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name1[/td][td=bgcolor:#E2EFDA]CityA[/td][td=bgcolor:#E2EFDA]ksh.1@abc.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name1[/td][td]CityA[/td][td]hit.2@bcd.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name1[/td][td=bgcolor:#E2EFDA]CityA[/td][td=bgcolor:#E2EFDA]lou@def.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name1[/td][td]CityA[/td][td]mau1@fgh.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name1[/td][td=bgcolor:#E2EFDA]CityA[/td][td=bgcolor:#E2EFDA]tau2@hgf.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name2[/td][td]CityB[/td][td]ksh.1@abc.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name2[/td][td=bgcolor:#E2EFDA]CityB[/td][td=bgcolor:#E2EFDA]hit.2@bcd.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name2[/td][td]CityB[/td][td]lou@def.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name2[/td][td=bgcolor:#E2EFDA]CityB[/td][td=bgcolor:#E2EFDA]mau1@fgh.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name2[/td][td]CityB[/td][td]tau2@hgf.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name3[/td][td=bgcolor:#E2EFDA]CityA[/td][td=bgcolor:#E2EFDA]ksh.1@abc.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name3[/td][td]CityA[/td][td]hit.2@bcd.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name3[/td][td=bgcolor:#E2EFDA]CityA[/td][td=bgcolor:#E2EFDA]lou@def.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name3[/td][td]CityA[/td][td]mau1@fgh.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name3[/td][td=bgcolor:#E2EFDA]CityA[/td][td=bgcolor:#E2EFDA]tau2@hgf.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name4[/td][td]CityD[/td][td]ksh.1@abc.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name4[/td][td=bgcolor:#E2EFDA]CityD[/td][td=bgcolor:#E2EFDA]hit.2@bcd.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name4[/td][td]CityD[/td][td]lou@def.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name4[/td][td=bgcolor:#E2EFDA]CityD[/td][td=bgcolor:#E2EFDA]mau1@fgh.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name4[/td][td]CityD[/td][td]tau2@hgf.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name5[/td][td=bgcolor:#E2EFDA]CityD[/td][td=bgcolor:#E2EFDA]ksh.1@abc.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name5[/td][td]CityD[/td][td]hit.2@bcd.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name5[/td][td=bgcolor:#E2EFDA]CityD[/td][td=bgcolor:#E2EFDA]lou@def.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name5[/td][td]CityD[/td][td]mau1@fgh.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name5[/td][td=bgcolor:#E2EFDA]CityD[/td][td=bgcolor:#E2EFDA]tau2@hgf.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name6[/td][td]CityA[/td][td]ksh.1@abc.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name6[/td][td=bgcolor:#E2EFDA]CityA[/td][td=bgcolor:#E2EFDA]hit.2@bcd.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name6[/td][td]CityA[/td][td]lou@def.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name6[/td][td=bgcolor:#E2EFDA]CityA[/td][td=bgcolor:#E2EFDA]mau1@fgh.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name6[/td][td]CityA[/td][td]tau2@hgf.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name7[/td][td=bgcolor:#E2EFDA]CityB[/td][td=bgcolor:#E2EFDA]ksh.1@abc.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name7[/td][td]CityB[/td][td]hit.2@bcd.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name7[/td][td=bgcolor:#E2EFDA]CityB[/td][td=bgcolor:#E2EFDA]lou@def.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name7[/td][td]CityB[/td][td]mau1@fgh.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name7[/td][td=bgcolor:#E2EFDA]CityB[/td][td=bgcolor:#E2EFDA]tau2@hgf.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name8[/td][td]CityA[/td][td]ksh.1@abc.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name8[/td][td=bgcolor:#E2EFDA]CityA[/td][td=bgcolor:#E2EFDA]hit.2@bcd.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name8[/td][td]CityA[/td][td]lou@def.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name8[/td][td=bgcolor:#E2EFDA]CityA[/td][td=bgcolor:#E2EFDA]mau1@fgh.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name8[/td][td]CityA[/td][td]tau2@hgf.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name9[/td][td=bgcolor:#E2EFDA]CityA[/td][td=bgcolor:#E2EFDA]ksh.1@abc.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name9[/td][td]CityA[/td][td]hit.2@bcd.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name9[/td][td=bgcolor:#E2EFDA]CityA[/td][td=bgcolor:#E2EFDA]lou@def.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name9[/td][td]CityA[/td][td]mau1@fgh.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name9[/td][td=bgcolor:#E2EFDA]CityA[/td][td=bgcolor:#E2EFDA]tau2@hgf.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name10[/td][td]CityA[/td][td]ksh.1@abc.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name10[/td][td=bgcolor:#E2EFDA]CityA[/td][td=bgcolor:#E2EFDA]hit.2@bcd.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name10[/td][td]CityA[/td][td]lou@def.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Name10[/td][td=bgcolor:#E2EFDA]CityA[/td][td=bgcolor:#E2EFDA]mau1@fgh.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name10[/td][td]CityA[/td][td]tau2@hgf.com[/td][/tr]
[/table]
 
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
You must log in or register to reply here.

Similar threads

E
  • Question Question
Selecting multiple columns (not adjacent to each other) in a table using column names
Replies
2
Views
141
excelguru123
E
Deepakchoudhary
  • Solved
RANK based on selling multiple items in different criteria (Excel 2016)
Replies
3
Views
163
hagia_sofia
hagia_sofia
T
  • Question Question
Moving address list to columns from rows
Replies
3
Views
97
Sufiyan97
Sufiyan97
B
  • Question Question
Need help
Replies
0
Views
273
blackberry
B
P
  • Question Question
Text split state and zip into separate columns
Replies
2
Views
271
pto160
P

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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