sequence

[snjpverma]

assuming there is a team of 3 employees named a, b and c who have to be assigned a task as shown below.
They have to be assigned task in the same sequence as it comes. i.e. First task to a, second to b and third to c (the last person), when the fourth file comes it has to be assigned again to "a" and 5 to "b" and so on... please see the below illustration. Any suggestions?
Copy pasting the tasks against each member individually is pretty time consuming when there are more then 30 employees.
The tasks are available in a separate sheet as shown in Sheet 2 below. The tasks doesn't come in one go, it keep adding throughout the day.
suggestions please.
Hint: the Employees names are always in serial order. in the below example I have kept each employees' names 4 times to make it simpler. In actual each employees names are mentioned 20 times (in ascending order).

Sheet 1


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Employees[/TD]
[TD]Tasks[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




Sheet 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Tasks[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Dr. Demento]

There are some details you haven't covered about the final format you'd like, but here's one solution
The first time a new person's name is listed, hardcode their number (a=1, b=2, c=3, etc). In all the remaining cells, copy this formula:

=IFERROR(IF($E$1>=B1+$I$1,B1+$I$1,""),"")

Additionally, I created two helper cells:
E1 = Total number of tasks to be completed; alternatively, this could be a formula to count the number of filled in cells on Sheet2 (=COUNTA(B:B), where B is the actual name of the task). If this is the case, any unassigned tasks would need to be kept blank.
I1 = Total number of employees (in your example, 3)

If you're looking for more than a numeric output, you could use an INDEX MATCH formula to show the actual task name listed on Sheet2.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG17May43
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rng2 [COLOR="Navy"]As[/COLOR] Range, Q [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
    .Add Dn.Value, Array(Dn, 1)
[COLOR="Navy"]Else[/COLOR]
   Q = .Item(Dn.Value)
      [COLOR="Navy"]Set[/COLOR] Q(0) = Union(Q(0), Dn)
  .Item(Dn.Value) = Q
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]

[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    [COLOR="Navy"]Set[/COLOR] Rng2 = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant

[COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] n <= Rng2.Count
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
        n = n + 1
        Q = .Item(K)
            Q(0)(Q(1)).Offset(, 1).Value = Rng2(n)
        Q(1) = Q(1) + 1
    .Item(K) = Q
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]Loop[/COLOR]

[COLOR="Navy"]End[/COLOR] With

Regards Mick

 

[snjpverma]

thanks Demento for your support. the output is not numeric. It is an alphanumeric combination. Could you help me with the Index Match suggestion?

Thanks MickG for your support. But I am not much into VBA

 

[Marcelo Branco]

Maybe something like this

Sheet2 (list of tasks)

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Tasks​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Task1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Task2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Task3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Task4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Task5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Task6​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Task7​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][/tr]
[/table]

Sheet1

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Employees​

[/td][td]

Tasks​

[/td][td][/td][td]

Num of Employees​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

a​

[/td][td]

Task1​

[/td][td][/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

a​

[/td][td]

Task4​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

a​

[/td][td]

Task7​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

a​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

b​

[/td][td]

Task2​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

b​

[/td][td]

Task5​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

b​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

b​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

c​

[/td][td]

Task3​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

c​

[/td][td]

Task6​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

c​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

c​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in D2 (count unique of employees)
=SUM(IF(FREQUENCY(IF(A2:A100<>"",MATCH(A2:A100,A2:A100,0)),ROW(A2:A100)-ROW(A2)+1),1))
confirmed with Ctrl+Shift+Enter, not just Enter

Formula in B2 copied down
=IF(A2="","",INDEX(Sheet2!A$2:A$100,D$2*COUNTIF(A$2:A2,A2)-(D$2-1)+SUMPRODUCT(--(A$2:A2<>A$1:A1))-1)&"")

Hope this helps

M.

 

[snjpverma]

Thanks Marcelo. That's exactly what I needed.

 

[snjpverma]

Hi Marcela,
Your formula works great but I would like to kow why you used the part highlighted in Red at the end.

=IF(A2="","",INDEX(Sheet2!A$2:A$100,D$2*COUNTIF(A$2:A2,A2)-(D$2-1)+SUMPRODUCT(--(A$2:A2<>A$1:A1))-1)&"")

 

[Marcelo Branco]

I don't remember exactly the logic i used 5 months ago , but &"" avoids results equal to 0 (zero).
Try without &"" and you'll see

M.