+ Reply to Thread
Results 1 to 9 of 9

Dynamic repeating order that needs to change based on specific criteria

  1. #1
    Registered User
    Join Date
    07-07-2013
    Location
    Norristown, PA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Dynamic repeating order that needs to change based on specific criteria

    I'm trying to figure out if there is a way to have a repeating list/order in a particular column that changes once specific criteria are met. For purposes of illustration, I am attaching a sample spreadsheet. The idea is that the letters in column A stand for individual salespeople. Column B says whether they were successful in making a sale in a given attempt. I would like column A to repeat their letters in that order but once each salesperson makes 3 sales, that particular salesperson should be removed from the order in column A going forward while the rest of the salespeople remain until they each have completed 3 sales. Please let me know if that isn't clear. I started a solution using dummy columns and COUNTIFS functions, but I'm not sure how to bring it home with what I figure should be a formula for each cell in column A. Any help would be much appreciated.Test Worksheet.xls

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Dynamic repeating order that needs to change based on specific criteria

    Do you have a list of those sales people?
    If you need the name to be repeated by formula then you must have a source.
    You can not create a column from nowhere.
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  3. #3
    Registered User
    Join Date
    07-07-2013
    Location
    Norristown, PA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Dynamic repeating order that needs to change based on specific criteria

    I'm not sure I follow what you are asking. In my sample worksheet I had attached, the letters in column A represent the sales people's names. If you are asking whether the sample worksheet has a dummy list of names in another column somewhere, I don't think it did, but it would be easy enough to add. I think my goal is to replace the letters in column A with a formula that would insert the next name in the order that doesn't already have 3 completed sales, but I'm having trouble coming up with that answer on my own. Thanks.

  4. #4
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Dynamic repeating order that needs to change based on specific criteria

    Hmm, so if you do not have the names of sales people(or you may consider to add)
    how Excel should return the name?
    Yuo can not have the names and the formula in one column.

  5. #5
    Registered User
    Join Date
    07-07-2013
    Location
    Norristown, PA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Dynamic repeating order that needs to change based on specific criteria

    I don't think I'm explaining this correctly then. Does this help? I've attached a revised version of the test worksheet. I created a new column to list the employee names in order. The goal is to have that order repeat in column A but over time to weed out each employee as they reach their 3 sale goal. Does that make more sense now? Thanks.
    Test Worksheet_v2.xls

  6. #6
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Dynamic repeating order that needs to change based on specific criteria

    Ok.I know what you after but this is far to complicated.
    A self sorting column...
    Look here for some resurces:
    http://dailydoseofexcel.com/archives...-sorting-list/

  7. #7
    Registered User
    Join Date
    07-07-2013
    Location
    Norristown, PA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Dynamic repeating order that needs to change based on specific criteria

    A self-sorting column isn't exactly what I was going for, but I actually managed to come up with the solution on my own. It's not very elegant, but it works. Thanks anyway!

  8. #8
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Dynamic repeating order that needs to change based on specific criteria

    Glad it works.
    Could you share your work?

  9. #9
    Registered User
    Join Date
    07-07-2013
    Location
    Norristown, PA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Dynamic repeating order that needs to change based on specific criteria

    Given that I'm somewhat of an Excel novice, there are probably more efficient/clean/elegant ways to solve this than I created. But attached is a solution I came up with that appears to be working for me. It just took me a while to envision the mechanics of it.

    In the spreadsheet example, you will see that Employee "D" reaches his/her goal of 3 sales first (the third occurring in in Row 24). Thereafter, Employee "D" is removed from the repeating employee list which continues to otherwise be repeated in order. I left all columns unhidden for purposes of showing the work, but on my actual spreadsheet I've hidden most of the them.Test Worksheet_Solution.xls

+ 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. [SOLVED] Macro/VBA to Change Order of Rows/Add Rows based on specific text content presence/absence
    By cmaunder in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-07-2013, 04:50 PM
  2. Calculate subtotals based on specific criteria + put in specific order
    By msls09 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-28-2013, 12:47 AM
  3. Urgent: Create a Dynamic Dropdown list based on a specific criteria
    By vijaye in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-11-2013, 08:07 AM
  4. HELP! Macro to Copy specific cells from one sheet to another based on specific criteria
    By atriscritti in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-11-2012, 11:05 AM
  5. create a dynamic drop down list based on specific criteria
    By stevegrobertson in forum Excel General
    Replies: 4
    Last Post: 02-21-2012, 08:07 PM

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