+ Reply to Thread
Results 1 to 5 of 5

Excel array function that assigns employee code to customer based on rank series assigned

  1. #1
    Registered User
    Join Date
    12-21-2019
    Location
    India
    MS-Off Ver
    2007
    Posts
    2

    Excel array function that assigns employee code to customer based on rank series assigned

    I'm trying to Assign employee code (EmpID) to my customer database in Excel

    I've the list of customers from each store which is ranked and it should be allocated to the employees of the respective store.

    I've customer database in the following format Column name is given in brackets

    (A). (B). (C)
    Store - CustomerID - Rank
    A. - A01. - 1
    A. - A02. - 2
    B. - B01. - 1
    B. - B02. - 2 and so on
    I've employee database in the following format

    (D). (E). (F). (G)
    Store. - EmpID. - Rankfrom - Rankto
    A. - E1. - 1 - 10
    A. -E2. -. 11. - 16
    B. - E3. -. 1. - 8 and so on

    I'm trying to add one more column to employee table where EmpId is assigned based on store and rank of customer in relation with rank range assigned in table 2

    I've come up with formula, but not working as I expected . Please help since I'm new with array functions

    {=Index(D:G,Match(1,((F:F>=C2)*(G:G>=C2)*(D:D=A2),0)2)}

    Desired output will be to add an employee code against each customer ( matching store and range of rank allotted to each employee )

    Please see the uploaded excel, the logic is working but not the way i had in mind.

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,892

    Re: Excel array function that assigns employee code to customer based on rank series assig

    A non-working formula is of limited use. It tells me where you want the result, but not much else. What are your expected resutls for a few rows? How would you derive them manually?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,892

    Re: Excel array function that assigns employee code to customer based on rank series assig

    No reply yet. So, a guess. In D2 an ordinary formula, copied down:

    =IFERROR(INDEX($G$2:$G$500,MATCH(1,INDEX((C2>=$K$2:$K$500)*(C2<=$L$2:$L$500)*($F$2:$F$500=B2),0),0)),"")

    DO NOT use whole column references with array formulae (or even with this one) unless you have 1,000,000 rows. It will get slowww.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    12-21-2019
    Location
    India
    MS-Off Ver
    2007
    Posts
    2

    Re: Excel array function that assigns employee code to customer based on rank series assig

    Thanks a lot brother

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,892

    Re: Excel array function that assigns employee code to customer based on rank series assig

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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. get sum of values based on assigned code
    By Excel God in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-04-2018, 08:57 AM
  2. [SOLVED] Formula (array?) to summarise and rank customer sales data
    By The_Snook in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-31-2016, 08:28 AM
  3. [SOLVED] A formula to find Customer based on rank
    By Aland2929 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-13-2016, 07:22 PM
  4. Replies: 19
    Last Post: 06-03-2016, 11:29 PM
  5. [SOLVED] Using rank function in an array
    By rhysspinner in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-22-2015, 09:15 AM
  6. [SOLVED] Rank - Maintain Series if duplicate rank found
    By ascool_asice in forum Excel General
    Replies: 2
    Last Post: 10-11-2014, 12:35 PM
  7. Need to Create Foreman Time Sheets based on Who Employee is assigned to
    By OlenOlen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-15-2013, 09:52 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