+ Reply to Thread
Results 1 to 22 of 22

Formula that returns data based on certain criteria & distibution

  1. #1
    Registered User
    Join Date
    12-04-2014
    Location
    Fort Wayne, Indiana
    MS-Off Ver
    10
    Posts
    39

    Formula that returns data based on certain criteria & distibution

    Hello!

    I am really stuck on a formula and am trying to create a formula (in my Leads tab, column L) that looks at another tab (Territory tab) and returns data that matches certain criteria (in my Leads tab, column L) and also I need the formula to be able to cycle thru data based on territory/location (columns I thru R).

    Basically, I am trying to create a formula (for my Leads tab, column L) that will provide me with the name of the individual listed in a certain location based on county &/or zip code information (Territory tab). In some locations, there are multiple people (columns I thru R in the Territory tab) and I will also need this information to cycle to another individual, if applicable. For example, if I receive 2 leads, I need leads distributed evenly amongst those in the territory/location. After that, I would need to see if I can also set the formula up so that the next person in line will receive leads. For example, if someone received leads today, then tomorrow everyone else in line would receive leads, depending on the quantity received.

    This is very difficult to explain, but I have attached the workbook for you to see.

    Any help you can offer would definitely be appreciated.

    Thank you,
    Tina
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula that returns data based on certain criteria & distibution

    I am having a hard time trying to figure out exactly what you want here.
    Which column/s contain the data you want returned?
    where is teh criteria
    where do you want the forumla?
    Can you show some sample answers?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    12-04-2014
    Location
    Fort Wayne, Indiana
    MS-Off Ver
    10
    Posts
    39

    Re: Formula that returns data based on certain criteria & distibution

    Hello and thank you for your reply!

    The column(s) that contain the data I need returned is in the Territory tab, columns I thru R.

    I am not sure if I understand your question about criteria, but on the Leads tab, I need for the agent name to populate in column L for those individuals that are located in the county &/or zip codes from the Territory tab, columns I thru R.

    I need the formula in column L of the Leads tab. Right now, it is manually entered and I am hoping that a formula can help do this.

    As for sample answers, the name of the agent in column L of the Leads tab is showing you the agent in the county/location from the Territory tab.

    I hope this makes sense. Let me know.

    Thank you,
    Tina

  4. #4
    Registered User
    Join Date
    12-04-2014
    Location
    Fort Wayne, Indiana
    MS-Off Ver
    10
    Posts
    39

    Re: Formula that returns data based on certain criteria & distibution

    Good Morning!

    After thinking about your questions, I will explain what I need.

    My Leads tab is information based off of responses we get from a mailing which in turn is a lead. My objective is to tie an agent to the lead based on the territory that they've selected (Territory tab contains the agents selected territory which is county &/or zip code(s); columns I thru R are the names of the agents for the corresponding territory) and the formula would need to go into the Leads tab, column L. I am trying to figure out a way to have a formula do this and also distribute the leads evenly among the agents within the county/zip code(s) because in some areas I have multiple agents and we also receive multiple leads in the mail.

    Does this help and are you able to help me?

    Thank you,
    Tina

  5. #5
    Registered User
    Join Date
    12-04-2014
    Location
    Fort Wayne, Indiana
    MS-Off Ver
    10
    Posts
    39

    Re: Formula that returns data based on certain criteria & distibution

    If someone could please help me with this, I would really appreciate it. I am in desperate need of someone to help guide me on this.

  6. #6
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Formula that returns data based on certain criteria & distibution

    I'm not sure exactly what you're looking for when you say "evenly distributed". Do you mean everyone should have the same number of total leads across the entire Leads sheet? Or should the leads for each territory be evenly distributed to the agents for that territory? I went with the latter interpretation in my effort below, because your data tables appeared (to me) to be set up for that sort of arrangement.

    I created a helper column in your territory sheet, putting the following in S2:

    =COUNTA($I2:$R2)

    ...and filling down to get a count of how many agents are assigned to each ZIP. I then used the following formula for L2 of 'Leads' and filled down:

    =IFERROR(INDEX(OFFSET(Territory!$I$2:$I$891,0,MOD(COUNTIF($K$1:$K22,$K23),IFERROR(INDEX(Territory!$S$2:$S$891,MATCH(Leads!$K23,Territory!$E$2:$E$891,0)),INDEX(Territory!$S$2:$S$891,MATCH(Leads!$D23,Territory!$B$2:$B$891,0))))),MATCH(Leads!$K23,Territory!$E$2:$E$891,0)),INDEX(OFFSET(Territory!$I$2:$I$891,0,MOD(COUNTIF($K$1:$K22,$K23),IFERROR(INDEX(Territory!$S$2:$S$891,MATCH(Leads!$K23,Territory!$E$2:$E$891,0)),INDEX(Territory!$S$2:$S$891,MATCH(Leads!$D23,Territory!$B$2:$B$891,0))))),MATCH(Leads!$D23,Territory!$B$2:$B$891,0)))

    NOTE: I actually put the formula in N2 of my attached workbook so that I/you could compare my results to your existing L2 column. The formula should look up the ZIP in 'Territory' and return the first agent. If the ZIP appears a second time, it returns the second agent, etc., eventually cycling back around to the first agent if the ZIP appears often enough. If the ZIP doesn't find a match in 'Territory', it'll search by county. My results appear to work as designed, but they don't quite line up with what you had in L2, which makes me concerned that my understanding was off. If I swung and missed, please clarify exactly what you're after.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-04-2014
    Location
    Fort Wayne, Indiana
    MS-Off Ver
    10
    Posts
    39

    Re: Formula that returns data based on certain criteria & distibution

    Good Morning!

    Oh my gosh..............this looks amazing……………THANK YOU!

    When I say evenly distribute, I am saying that if I receive multiple leads in from a county &/or zip code(s) and there are multiple agents within that area, I need the formula to provide a lead to each person in that area, depending on the number of leads that have come in. The order of the agents listed does not matter. For example, in Riverside County, there are a total of 5 agents, 4 of the 5 take any leads that come in from Riverside, 1 agent only takes certain zip codes from Riverside, but the other 4 agents can also take leads from this zip code too. I’ve noticed that one agent is getting the majority of the leads. Is there a way to correct this so that if we receive a lead from zip code 91752, it would go to any of the agents because all 5 agents can receive leads from this area?

    The formula looks great, but I am thinking that I need to modify the spreadsheet because areas that I have an agent that will take any leads from anywhere in the county and also listing them in the zip codes area may cause an issue. Here’s a question, if an agent takes leads anywhere from the county, should I only have the agent listed in the county and not the zip code area or vice versa or does it matter?

    The not lining up is not a big deal. The names that I have listed for the lead is something that was done manually based off of which agent was in the area.

    Let me know if you need anything else.

    I really appreciate your help with this!

    Thank you,
    Tina

  8. #8
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Formula that returns data based on certain criteria & distibution

    Certain agents get the majority of the leads because my current formula gives primacy to Agent 1. It only moves to Agent 2 on the second lead from that particular ZIP code, so Agent 1 always gets the first shot at a lead. Balancing agent/lead distribution across every ZIP will be quite a bit more complicated. Unfortunately, I didn't have time to work on it today. I might be able to toy with it a bit on Monday, but another Forum User is welcome to jump in sooner. Are you open to a VBA solution? It might be cleaner than trying to manipulate a better overall lead distribution with formulas alone.

    As to listing only by ZIP or only by county... it's up to you and what you need from your database. As I mentioned, my formula looks for a match by ZIP first, then looks by county if the initial match attempt fails. As such, the formula length could be cut in half if every ZIP were included, but otherwise we can work with what you've got.

  9. #9
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Formula that returns data based on certain criteria & distibution

    Here's another effort that requires a few helper columns but provides a more even distribution. In this version, columns P:Y are helper columns with the formula below in P2, filled down then right:

    =IF(INDEX(Territory!$I$2:$R$891,IFERROR(MATCH($K2,Territory!$E$2:$E$891,0),MATCH($D2,Territory!$B$2:$B$891,0)),COLUMN(A:A))=0,"",COUNTIF($O$1:$O1,INDEX(Territory!$I$2:$R$891,IFERROR(MATCH($K2,Territory!$E$2:$E$891,0),MATCH($D2,Territory!$B$2:$B$891,0)),COLUMN(A:A))))

    This formula will provide the number of assigned leads thus far in the list for each of the eligible agents in that ZIP/area. Column O then offers up the name of the eligible agent with the fewest assigned leads thus far using the formula:

    =INDEX(Territory!$I$2:$R$891,IFERROR(MATCH($K2,Territory!$E$2:$E$891,0),MATCH($D2,Territory!$B$2:$B$891,0)),MATCH(MIN($P2:$Y2),$P2:$Y2,0))

    I left my previous effort in column N for comparison's sake. The new effort will still tilt a bit toward your agents with more assigned ZIPs, naturally, but you do get a much broader distribution. Unfortunately, you'll have to leave the helper columns. You can hide them for aesthetic purposes.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    12-04-2014
    Location
    Fort Wayne, Indiana
    MS-Off Ver
    10
    Posts
    39

    Re: Formula that returns data based on certain criteria & distibution

    Hello Cantosh!

    I'm sorry that I haven't gotten back with you. Things have been crazy in the office and I haven't had a chance to review what you provided to me. I hoping to get to it this week and once I do, I will touch base with you.

    Thank you very much and talk to you soon!

    Thanks,
    Tina

  11. #11
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Formula that returns data based on certain criteria & distibution

    Haha, don't sweat it. There's certainly no rush on my end.

    Craig

  12. #12
    Registered User
    Join Date
    12-04-2014
    Location
    Fort Wayne, Indiana
    MS-Off Ver
    10
    Posts
    39

    Re: Formula that returns data based on certain criteria & distibution

    Good Afternoon Craig,

    All I can say is WOW..................completely amazed!!!!!!!!!!!!!

    While reviewing your work and testing things, I found something with zip codes that cross county lines. For example, zip code 90024 resides in Los Angeles County. I added the same zip code for this purpose to Riverside County, to the Territory tab. I also created another line entry for a lead for Riverside with the zip code 90024. The purpose here is to see if the proper agent(s) will populate and it didn't based off of county. Can you take a look at this to see what's going on?

    I really appreciate your help!!!

    Thank you,
    Tina 

  13. #13
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Formula that returns data based on certain criteria & distibution

    My previous effort only looks at the county as a backup. The primary lookup is a MATCH of the ZIP code, and the MATCH function will always return the first match it finds. If you have two 90024 entries on the 'territory' sheet, the lookup on 'leads' will always return a result based on the first match it finds. That's likely what gave you the false return on the split ZIP. In my latest effort (attached) there is a three step lookup. I added a helper column on 'territory' (column I) that concatenates the ZIP and county. The new formula in Column N of 'leads' looks at that column first, to see if there is a territory entry that matches both the county and ZIP (this should solve the split ZIP issue). Should it fail to find a match there, it'll look by ZIP alone. Failing that, it'll look by county alone. Columns O:X on 'leads' have also been tweaked to accommodate the three step lookup. I played with a few examples, and it appeared to hold up, but test it out a bit and see if it fits what you need.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    12-04-2014
    Location
    Fort Wayne, Indiana
    MS-Off Ver
    10
    Posts
    39

    Re: Formula that returns data based on certain criteria & distibution

    Good Afternoon Craig!

    Everything is functioning the way we need it to. Now I am having an issue with trying to put this in my real spreadsheets. I am going to keep going, but need you to look over my final stuff to make sure that I haven't messed anything up, if you would mind?

    Thank you very much for all of your help with this and I am sure I will be in touch!

    Tina

  15. #15
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Formula that returns data based on certain criteria & distibution

    I'm glad it's working, please let me know if you have any additional issues! FYI, though, I'll be largely offline from 9/1 to 9/12, so if you have any pressing issues in that timespan, you might be wise to invite the assistance of the forum at large. Your workbook has quite a bit going on, but the helper columns keep things reasonably clear, so there should be plenty of experts on here who can quickly get up to speed and help.

    Happy Friday!
    Craig

  16. #16
    Registered User
    Join Date
    12-04-2014
    Location
    Fort Wayne, Indiana
    MS-Off Ver
    10
    Posts
    39

    Re: Formula that returns data based on certain criteria & distibution

    Good Afternoon Craig!

    Well, I have tried and tried to apply what you have given to me, in real form and I cannot get this to work. I don't know what I am doing wrong.

    Can you take a look?

    Attached is my territory spreadsheet as well as my leads database. You are also going to notice that I have some sort of an error going on.

    OH MY GOSH.................I can't find where to attach my stuff to have you review it???????????

    Thank you,
    Tina

  17. #17
    Registered User
    Join Date
    12-04-2014
    Location
    Fort Wayne, Indiana
    MS-Off Ver
    10
    Posts
    39

    Re: Formula that returns data based on certain criteria & distibution

    Hi Craig!

    Ok, I found where I can attach my files, but now they are too large to send.

    I am going to try something else to get them to you.

    I am so ready to pull my hair out!

    Thank you,
    Tina

  18. #18
    Registered User
    Join Date
    12-04-2014
    Location
    Fort Wayne, Indiana
    MS-Off Ver
    10
    Posts
    39

    Re: Formula that returns data based on certain criteria & distibution

    Good Afternoon Craig,

    Ok, I am hoping that you can figure this out.

    In order for me to get my stuff to you, I had to remove pretty much all of my stuff, including formulas because of the size limitations.

    My work starts and ends at rows 19939 thru 19962. For column U, my formula is as follows:

    =INDEX('T:\A Web Admin\Advantage Direct 365\Leads Program\Tina\[Leads Program -Tracking and Billing 6.11.2014 New Leads Program 2.xlsx]Territory NEW 8.29.2016'!$I$2:$R$51556,IFERROR(MATCH(CONCATENATE($AF19939,$U19939),'T:\A Web Admin\Advantage Direct 365\Leads Program\Tina\[Leads Program -Tracking and Billing 6.11.2014 New Leads Program 2.xlsx]Territory NEW 8.29.2016'!$H$2:$H$51556,0),IFERROR(MATCH($AF19939,'T:\A Web Admin\Advantage Direct 365\Leads Program\Tina\[Leads Program -Tracking and Billing 6.11.2014 New Leads Program 2.xlsx]Territory NEW 8.29.2016'!$E$2:$E$51556,0),MATCH($U19939,'T:\A Web Admin\Advantage Direct 365\Leads Program\Tina\[Leads Program -Tracking and Billing 6.11.2014 New Leads Program 2.xlsx]Territory NEW 8.29.2016'!$G$2:$G$51556,0))),MATCH(MIN($EB19939:$EK19939),$EB19939:$EK19939,0))

    I am getting a circumference error on the above.

    Now, for the helper columns in the leads section, columns EB thru EK, my formula is as follows:

    =IF(INDEX('T:\A Web Admin\Advantage Direct 365\Leads Program\Tina\[Leads Program -Tracking and Billing 6.11.2014 New Leads Program 2.xlsx]Territory NEW 8.29.2016'!$I$2:$R$51556,IFERROR(MATCH(CONCATENATE($AF19939,$U19939),'T:\A Web Admin\Advantage Direct 365\Leads Program\Tina\[Leads Program -Tracking and Billing 6.11.2014 New Leads Program 2.xlsx]Territory NEW 8.29.2016'!$H$2:$H$51556,0),IFERROR(MATCH($AF19939,'T:\A Web Admin\Advantage Direct 365\Leads Program\Tina\[Leads Program -Tracking and Billing 6.11.2014 New Leads Program 2.xlsx]Territory NEW 8.29.2016'!$E$2:$E$51556,0),MATCH($U19939,'T:\A Web Admin\Advantage Direct 365\Leads Program\Tina\[Leads Program -Tracking and Billing 6.11.2014 New Leads Program 2.xlsx]Territory NEW 8.29.2016'!$G$2:$G$51556,0))),COLUMN(A:A))=0,"",COUNTIF($EA1:$EA$19939,INDEX('T:\A Web Admin\Advantage Direct 365\Leads Program\Tina\[Leads Program -Tracking and Billing 6.11.2014 New Leads Program 2.xlsx]Territory NEW 8.29.2016'!$I$2:$R$51556,IFERROR(MATCH(CONCATENATE($AF19939,$U19939),'T:\A Web Admin\Advantage Direct 365\Leads Program\Tina\[Leads Program -Tracking and Billing 6.11.2014 New Leads Program 2.xlsx]Territory NEW 8.29.2016'!$H$2:$H$51556,0),IFERROR(MATCH($AF19939,'T:\A Web Admin\Advantage Direct 365\Leads Program\Tina\[Leads Program -Tracking and Billing 6.11.2014 New Leads Program 2.xlsx]Territory NEW 8.29.2016'!$E$2:$E$51556,0),MATCH($U19939,'T:\A Web Admin\Advantage Direct 365\Leads Program\Tina\[Leads Program -Tracking and Billing 6.11.2014 New Leads Program 2.xlsx]Territory NEW 8.29.2016'!$G$2:$G$51556,0))),COLUMN(A:A))))

    I am trying to provide you with a short version of what I am trying to do because of the size limitations.

    I am so sorry that I am a hot mess!

    Thank you,
    Tina

  19. #19
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Formula that returns data based on certain criteria & distibution

    I don't know if I caught everything, but two corrections stand out:

    Your concatenated helper column in territory needs to match the actual concatenation. The concatenation done in EB attaches ZIP before county with no space or dash in between, but your concatenation helper column in territory appears to list the county, then a dash, then the ZIP. That will return a "non-match" every time. Be sure that the helper column fits the combination that your IF(INDEX... formulas are looking for.

    Your COUNTIF in the EB:EK formula needs to cover EA1:EA19938 - we're counting previous occurrences, so the range should be to the row BEFORE the one the formula is in. Going with 19939 would likely lead to circular reference errors.

    Try correcting those issues and seeing if that does the trick. If not, let me know and we'll try digging a bit deeper.

  20. #20
    Registered User
    Join Date
    12-04-2014
    Location
    Fort Wayne, Indiana
    MS-Off Ver
    10
    Posts
    39

    Re: Formula that returns data based on certain criteria & distibution

    Good Afternoon Craig,

    Yes, after emailing you, I realized the concatenation was incorrect and I was working on that when you responded. I thought that I had done something wrong to the COUNTIF function, but now it makes sense.

    I have corrected everything and everything functions!

    Craig, thank you so much for all of your help on this! I really, really appreciate it!!!!!!!!!!!!!

    Have a wonderful day!

    Tina

  21. #21
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Formula that returns data based on certain criteria & distibution

    I'm glad we found it, I definitely was not looking forward to sifting through that formula again! Fingers crossed that it behaves from now on.

    Good luck!

  22. #22
    Registered User
    Join Date
    12-04-2014
    Location
    Fort Wayne, Indiana
    MS-Off Ver
    10
    Posts
    39

    Re: Formula that returns data based on certain criteria & distibution

    I know what you mean..............my eyes are crossed.........lol!

    Thank you again!!!!!!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Returns a single result based on multiple criteria
    By Humanoj in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-29-2016, 10:22 AM
  2. Replies: 5
    Last Post: 04-12-2016, 11:30 PM
  3. Distibution Curve
    By andycuk7 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-08-2015, 08:49 AM
  4. [SOLVED] one column that returns a text based on criteria in other cells
    By nicko54 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-16-2013, 12:24 AM
  5. Replies: 4
    Last Post: 03-27-2013, 10:05 PM
  6. [SOLVED] Formula that returns values based on criteria
    By dks345 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-10-2013, 08:48 AM
  7. Hypergeometic distibution
    By Ganivada in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-11-2009, 09:12 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1