+ Reply to Thread
Results 1 to 42 of 42

Help Required - Allocation based on exclusion of values

  1. #1
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104

    Help Required - Allocation based on exclusion of values

    Hi team,

    I have finally completed the attached macro however in the task code I am updating text instead of a number, however the code is showing error. Can anyone help me to fix the error.


    I believe the current coding looks out for numbers in the task code however I am trying to modify values to text.

    I have attached the error screen shot.

    Kindly help me to get this code fixed.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Santhi28595; 05-18-2019 at 12:03 AM.

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Cool

    Hi !

    Classic typo : replace 1 by L

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    8,667

    Re: Help required X1values

    With this line
    Please Login or Register  to view this content.
    You are trying to add 1 to a text value, which you cannot do. try removing the part in red

  4. #4
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104

    Re: Help required X1values

    Hi Marc,

    Thank you. Even though if replaced still the task codes are not excluded while assigning.

    Can u check and tell me.

    Thanks a lot for the quick assistance

  5. #5
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104
    Quote Originally Posted by Fluff13 View Post
    With this line
    Please Login or Register  to view this content.
    You are trying to add 1 to a text value, which you cannot do. try removing the part in red

    I removed that as well however still the values that I enter in task code is not excluding...

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    8,667

    Re: Help required X1values

    Can you please explain what you are trying to do & what is not working?

  7. #7
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104

    Re: Help required X1values

    Hi Fluff,

    The macro is designed in such a way that it Excludes the values given in task code and product code sheets And assigns the remaining data to the users in the assigned summary.

    So ideally it should look first on column E and check in task code sheet, if the same value is there it should return excluded Task code in column J.

    Hope u understood now

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    8,667

    Re: Help required X1values

    Hope u understood now
    Fraid not.
    Please remember that we have no knowledge of what you are trying to do, or what your data represents.

  9. #9
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104
    Quote Originally Posted by Fluff13 View Post
    Fraid not.
    Please remember that we have no knowledge of what you are trying to do, or what your data represents.
    Hi Fluff,

    This macro assigns data after excluding the values that is given in task code and product code.

    However currently it is excluding only the values that was given in product code.

    However it does not exclude the values that was given in task code, please let me know if there is any changes that to be done in my code to get this worked.

  10. #10
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    8,667

    Re: Help required X1values

    Until you can supply me with a full, clear & concise description of what you are trying to do, I cannot help

  11. #11
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104

    Re: Help required X1values

    Fluff,

    This is a assignment macro. I have modified in such a way that.

    1. It Excludes the values that is provided in the task code sheet.(based on column E)
    2. Then it Excludes the values that is provided in the product code sheet. (Based on column F)

    So after this exclusion, the reamining data which is in the data tab gets assigned to the users.

    Let me Know if u still have any questions.

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    8,667

    Re: Help required X1values

    All you are doing is repeating what you have already said, which I don't understand.

    Imagine you were standing in a queue at a ticket office & were trying to explain to the person next to you, what you were trying to do.
    That is the sort of description that I need.

  13. #13
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104

    Re: Help required X1values

    Hi Fluff

    Sorry the objective of the macro is to assign data to the users.

    This is macro which equally assigns data to the users.

    Can you please let me what u did not understood so I can provide explanation on that.

  14. #14
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104

    Re: Help required X1values

    While assigning I have 2 check points to be checked.

    1. Task code (column D). The macro looks for the data that is provided in the task code tab and excludes whatever the values that was there in the first colu.

    2. Once task code exclusion is over then, it looks for values in product code and compare with the values in the product code sheet. If it contains same values, it excludes those row in the data and returns a value in column J as "Excluded product code"

    The reamining data only after these 2 exclusion should be assigned to the users in the team.

  15. #15
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104

    Re: Help required X1values

    I want to know is there any modifications that need to be done so that exclusion of task code works.

  16. #16
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,430

    Re: Help required X1values

    Thread title much more descriptive. Thanks
    Last edited by Pepe Le Mokko; 05-18-2019 at 02:20 AM.

  17. #17
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104

    Re: Help required X1values

    Hi, thanks for the reminder. I will.modify that.

  18. #18
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104
    Quote Originally Posted by Pepe Le Mokko View Post
    Thread title much more descriptive. Thanks
    Thank you. Any help is much more appreciated !!

  19. #19
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,630

    Re: Help Required - Allocation based on exclusion of values

    I think I understand what you're looking for but I have 1 question.

    What happens if all persons have a case allocated but there are still rows to process. Do you start again at the top of the Name list.

    Can 1 person be assigned multiple cases ?
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  20. #20
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104

    Re: Help Required - Allocation based on exclusion of values

    In this case, whoever is in the next list in the assigned summary will be picked up so it gets evenly distributed.

    Say for example, if the last case allocated to "Alan' then when I again put the data it gets allocated to "Harita"

    My coding works like that however only the exclusion in task code doesn't work. Not sure if any format has to be changed.

  21. #21
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,630

    Re: Help Required - Allocation based on exclusion of values

    Try this one.
    Attached Files Attached Files

  22. #22
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104
    Quote Originally Posted by bakerman2 View Post
    Try this one.
    Hi bakerman,

    Thanks for the effort but I still don't see any changes that happened. The macro does not exclude the task code and it simply assigns everything.

    Product code is getting excluded however task code is not excluding.Can u please check

  23. #23
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,630

    Re: Help Required - Allocation based on exclusion of values

    Your sheet variables don't work.
    Attached Files Attached Files

  24. #24
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104
    Quote Originally Posted by bakerman2 View Post
    Your sheet variables don't work.
    Hi bakerman,

    This really works out. Thanks a lot.

    Have u changed the entire code? Also I have a small doubt if I do a vlookup with the task code number as that is only available in my raw data. Will the same macro work? Or should I always have the values in text format..


    I have attached the file. Please review and let me know ur feedback.
    Attached Files Attached Files

  25. #25
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,630

    Re: Help Required - Allocation based on exclusion of values

    Don't understand what you're trying to achieve.

    Explain clearly the steps you want to take.

  26. #26
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104

    Re: Help Required - Allocation based on exclusion of values

    Hi bakerman,

    Please refer to my attached file now.I will have only task code number, each task code number has a unique name. So whatever the file u gave me has the direct task code values in it.

    If I do a vlookup with task code number and return the task code values, does the macro works??

    I want to know if the macro still works with a vlookup function. You refer to my attached sheet where I have returned the task code names for the corresponding task code number.

  27. #27
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,630

    Re: Help Required - Allocation based on exclusion of values

    Is this what you have in mind ?
    Attached Files Attached Files

  28. #28
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104
    Quote Originally Posted by bakerman2 View Post
    Is this what you have in mind ?
    Hi I believe you have replaced it by task code number. What I exactly require is to compare the names provided in the task code sheet.

    My question is that each task code name carries a number.

    If I do a vlookup with task code number (column E) and return the values in column F.

    Would that same macro works. Now what I need is compare the vales of F in it the task code sheet and assign accordingly.

    Would that be possible?
    Attached Files Attached Files

  29. #29
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,630

    Re: Help Required - Allocation based on exclusion of values

    Just shift all column letters by 1.
    Please Login or Register  to view this content.

  30. #30
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104

    Re: Help Required - Allocation based on exclusion of values

    Hi bakerman,
    I am sorry I could not understand. Shift by 1 means u want me to move all the columns???

  31. #31
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,630

    Re: Help Required - Allocation based on exclusion of values

    The updated code is in my previous post.

  32. #32
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104
    Quote Originally Posted by bakerman2 View Post
    The updated code is in my previous post.
    I copied to my file, however when I try to add new value, in the task code exclusion list, the code didn't work. Can u check that for me please.

    Appreciate all the efforts taken to get my queries solved.

  33. #33
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,630

    Re: Help Required - Allocation based on exclusion of values

    Works just fine for me. Send file where the error occurs.

  34. #34
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104
    Quote Originally Posted by bakerman2 View Post
    Works just fine for me. Send file where the error occurs.
    Hi bakerman, please refer attached. No cases gets assigned even if I paste the updated macro.

    Also if there is #NA during a vlookup, it means that those are not excluded and it should get assigned.

    Also if the case gets duplicated again, can we able to assign to the same person to whom it was assigned before.

    Please review and share your feedback.
    Attached Files Attached Files
    Last edited by Santhi28595; 05-19-2019 at 07:00 AM.

  35. #35
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,630

    Re: Help Required - Allocation based on exclusion of values

    1. It's more then logical that nothing gets assigned because you have either an Excluded Task Code or Product Code in every row.

    2. How can you assign a task if information isn't complete ? Do you really want that to happen ?

    3. This is for a new thread.

    Also please stop quoting posts as it just clutters the thread.

  36. #36
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104

    Re: Help Required - Allocation based on exclusion of values

    Hi bakerman,

    If you refer to my data there is one row #NA, which means that it is not Excluded. It should get assigned right?.

    Also if your statement tells that it was all excluded, then remarks should be updated as per the category of exclusion.

    Can you please share your Excel where it works perfect so I can check and confirm.
    Last edited by Santhi28595; 05-19-2019 at 07:32 AM.

  37. #37
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,630

    Re: Help Required - Allocation based on exclusion of values

    I have downloaded your file from Post#34 again and noticed that Column I is filled all the way down.

    Then you have added the condition
    Please Login or Register  to view this content.
    but the point is that column I isn't empty on any row ??????? thus code aborts execution on every loop.

    This is going nowhere if you keep adding things without thinking them through.

  38. #38
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104

    Re: Help Required - Allocation based on exclusion of values

    I just copy pasted the code that u have given in your earlier post.

    I have not modified anything else. Can u share ur Excel which works fine so I can check again.

  39. #39
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104

    Re: Help Required - Allocation based on exclusion of values

    Hi bakerman,

    I just ran the macro with column "I" as empty, the macro works. Can u tell why does column "I" should be empty???

    Is the design is like that??

    Also one more doubt is that, if I add a new data and click assign the loop does not work from where it stopped
    Do we need to add any specific code to continue the loop.
    Last edited by Santhi28595; 05-19-2019 at 09:15 AM.

  40. #40
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,630

    Re: Help Required - Allocation based on exclusion of values

    Previously I just looked at the Columns but now my remark still stands.

    Please Login or Register  to view this content.
    Code will never work because Segment Column isn't empty on any row.

    It's not the design, it's something you added yourself.

  41. #41
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104

    Re: Help Required - Allocation based on exclusion of values

    Hi Bakerman,

    I agree to your point and I made the segment column as empty and the macro works.

    Now if I add new data to the sheet, and click assign, the loop works indefinitely, the macro assigns repeatedly on how much I click the assign button.

    Can u check and let me know if I add any code to break the loop.

  42. #42
    Forum Contributor
    Join Date
    04-24-2019
    Location
    Chennai
    MS-Off Ver
    MS office 2007
    Posts
    104

    Re: Help Required - Allocation based on exclusion of values

    Hi bakerman,

    The macro works fine now. Really thank you for all the efforts taken to get it sorted.

    Duplicates alone is not working, it does not return the same name which was assigned earlier before, I will try to fix it, if there is any code to altered, please let me know.

    Thanks a ton for the support !!!!

+ 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. Replies: 3
    Last Post: 07-15-2016, 12:39 PM
  2. [SOLVED] Simple Conditional Formating Help Required - If Blank then Red Bold text required
    By thilag in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-19-2014, 12:23 PM
  3. Help required in matching data in required format
    By pravindwd in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-04-2013, 09:03 PM
  4. Help required in matching data in required format
    By pravindwd in forum Access Tables & Databases
    Replies: 1
    Last Post: 05-04-2013, 04:11 PM
  5. hi help is required
    By munirdurrani in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 12-14-2012, 01:48 AM
  6. Look up required
    By cyrusir in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-31-2007, 01:33 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