• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to set criteria for 2 different columns with different data

J

Jagdev Singh

Active Member
Hi Experts
I am stuck with query which consumed my huge amount of time and the end result is zero. I have list of similar(in integer) entries in a column say A. The entries could be repeatative in column A. What I want is to create a Pivot in macro and a entry which could be repeatative from 1 to 100 in column A should be assigned to a single person say Ram which is added in a column say C.
The similar entries in column A should be assigned to single individual Name. The Individual name is static and fix, but the similar entries is dynamic.
Entries Assigned


123456 Ram
123456
123456
123456
123456
123456
123456
123456
123456
123456
123456
123456
123456

364589 Shyam
364589
364589
364589
364589
 
Hi Narayan

Thanks for you prompt reply. I tried to make the issue simple and understandable in the attached doc. The list of Assignment may vary and the task entries as well. What I am looking for is the cretaria is set of entries. I highlighted the each example entries for you. The similar entries should be assigned to a single person. No matter how many entries it is. The assignment should be per task to single person.

Jaggi
 

Attachments

  • Sample.xlsx
    15.3 KB · Views: 7
Hi Jaggi ,

Sorry , but I have not understood your requirement ; the file you have uploaded has entries in column A ; these have entries against them in sets , in column C.

Thus one name in column C can be assigned to one or more entries in column A.

Now what ?

Narayan
 
Sorry for the confusion Narayan. Let me make it simple to you. In the Column A we have tasks...It could be possible that a task is divided into may subtask with same number. I highlighted such similar task in Column A. In Column C you will find the list of individual whom I have to assign the task. Now the cretaria to assign the task is

Similar task number should be assigned to one individual irrespective of the number of sub-task.

I want the marco to check the similar number task list from the column A and then assign it to the individual I will provide. The list of individual will be not more than 10 it will be static.

Regards,
Jagdev
 
Hi Jaggi ,

But how can a macro decide which task should be assigned to which individual ?

How will you provide the list of individuals ?

Can you take a complete example with a proper list of individuals and explain ?

Narayan
 
Hi Narayan

I am looking out for some cretaria to put on column A. The task number will be in proper order with the RAW data. Is it possible for the macro to select the first task number "05030A12" and move down till it find the different number in the column. Once he find the different number say "06549A14" it allocate name "Roma" to the above range of entries.

We have the following list of fixed individuals (Roma, Shraddha, Akshay, Shyam, Raju, Sam). It is on you how to provide the above set of individual in the macro. The set will remain unchange

Is it possible for the macro to perform the same operation in loop ... like find the similar entries in column A and assigning the name in any order from the list to the tasks.

My many concept is that the same task number should be assigned to a single person irrespective of the count of subtasks. There should not be the case where the same task (Sub task) is assigned to 2 individuals.

Please let me know if you need further clarification.

Regards,
Jagdev
 
Hi Jaggi ,

I have still not understood your requirement.

However , please go through the uploaded file and comment.

Narayan
 

Attachments

  • Jagdev_Singh_Sample.xlsx
    16.2 KB · Views: 14
Hi Narayan

Really sorry for making it so confusing for you to understand.

I checked the sample file and yes this is what I am looking for. Could you please help me with the macro to make the task assignment for the list of individual automate like the way it is currently in the sample file.

Regards,
Jagdev
 
Hi Jaggi ,

If I write the macro to suit the sample file , will you be able to transfer it to your working file ? If not , please upload your working file , or let me know the range addresses for :

1. Your data
2. The range which has the list of individuals

Narayan
 
Hi Narayan

The range of individual will be same...Please provide me the code..I can manage to make the neccessary amendment.

The only thing I would like to inform you that the task list may vary and the individual will be static.

Regards,
Jagdev
 
Hi Narayan

Just asking this question in the curiosity. Will it be possible to make the individual name dynamic. As in depend upon the availability of the individual the macro run and provide the task accordingly. Is it possible, if yes then please amend it in the code.

Sorry for all the trouble.

Regards,
Jagdev
 
To make it more clear to you in case if "Ram" is not in for a day. will it be possible to remove his name from the individual like and the task is assigned to the remaining individuals.

Regards,
Jagdev
 
Hi Narayan

I have not heard anything back from you on the above thread. I assume that you get the clear idea on the query...

I am eager to see it working..

Regards,
J
 
Hi Narayan

You are a Rockstar!! Headson Bro..I amended the code and it is working fine...

I really apologize for all the confusion created by me on the above thread..I really appreciate your input in understanding the need and fixed the issue..

I know I am asking too much from you..Few weeks back I asked a query on Chandoo forum and didn't get the solution for it. Could you please have a look on the below thread and let me know your views on it.

http://chandoo.org/forum/threads/ho...in-a-column-of-a-worksheet.19917/#post-120267


Regards,
Jagdev
 
Hi Narayan

I have a small query regarding the above thread..In this case we have assigned the task and set the availability cretaria to yes/no as per the availability.

Say for a case where we received the data with the assignment of the individuals in it..how can we set the yes/no cretaria on it..Could you please shed some light on it...

Regards,
Jaggi
 
Hi Narayan

The individual names are fixed..Can we create a Macro which will collect only those individual whose availability cretaria is set to yes... In this case I am not refering to task number and trageting assigned column...

I am looking to create a Pivot data on individual basis (Task is already assigned in a raw data)..Say from the list of individual, if I want to pull out the data of Roma from the individual list, I will set its available status yes and rest no..The pivot will give me the set of her record in a sheet...

Regards
Jaggi
 
Hi Jaggi ,

Are your requirements as follows ?

1. Given the raw data , we need to :
  • Isolate the individuals
  • In the master list of individuals mark the matches as YES , and those missing as NO.
2. Given an individual's name , you wish to display all the tasks assigned to that individual

This can easily be done using the Advanced Filter feature.

Narayan
 
Hi Narayan

Thanks for coming back to me on the above thread...Let me clear it bit further...

Let say we have a list of individuals in the masterlist and their availablity next to it in the same way you added in our above example..

Say we have received the raw list in which the task is already assigned to the individuals...No task assignment

I want to review the work on an individual let say (Roma)..Is it possible to create a pivot table which will pull only data related to Roma irrestective of other individuals available in the column...

Considering the above scenerio like making the availability option on other individual "No" and only Roma "Yes".. The pivot will pull on "Roma's" stats infront of us..

This is totally opposite to what we did in the above thread.. Here we are not assigning task and keep tab on assigned individual's availability..

Here..We are going to target a column with the list of fixed individuals in it and it is part of the RAW data. Is it possible to create the mastertable with the list of fixed individuals and have a option on "Yes/No" and depend upon Yes or No the pivot will pull the data.

Please let me know if you need further clarification.

Regards,
Jagdev
 
Hi Jaggi ,

As I have mentioned earlier , what you want can be done using the Advanced Filter facility in Excel. Can you upload a sample workbook ?

Narayan
 
Hi Jaggi ,

Your workbook contains only one column of data with the assigned handlers' names.

From this , what do you want done ?

Narayan
 
Hi Narayan

I want a condition to be set say yes/no on these individual in a tabular form. If I select yes, the data of that individual is selected and I will create pivot of that individual..

Regards,
Jagdev
 
You must log in or register to reply here.
Back
Top