Create a list of names based on a number indicator

[Nikijune]

I have a list of QA Analyst, and a number of how many audits they need to complete. I then have another list of advisors that need to be audited.

I want to apply the QA Analysts name next to the advisors names the exact number of times that the first list says they need to audit.

I'm not sure if this would be VBA or not. Any ideas?

List 1;

QA Analyst Name No of Audits to complete
Robin 4
Dan 1
Niki 3


List 2

Advsiors Qa Analyst
Jim Robin
bob Robin
sarah Robin
greg Robin
Katie Dan
Jess Niki
Tim Niki
Kim Niki
Tom

Many thanks in advance

 

[Marcelo Branco]

Maybe using a helper column (to make the formula simpler)


[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][td="bgcolor: #DCE6F1"]

E​

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

F​

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

1​

[/td][td]

QA Analyst Name​

[/td][td]

No of Audits to complete​

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

0​

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

Advsiors​

[/td][td]

Qa Analyst​

[/td][/tr]

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

2​

[/td][td]

Robin​

[/td][td]

4​

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

4​

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

Jim​

[/td][td]

Robin​

[/td][/tr]

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

3​

[/td][td]

Dan​

[/td][td]

1​

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

5​

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

bob​

[/td][td]

Robin​

[/td][/tr]

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

4​

[/td][td]

Niki​

[/td][td]

3​

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

8​

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

sarah​

[/td][td]

Robin​

[/td][/tr]

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

5​

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

greg​

[/td][td]

Robin​

[/td][/tr]

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

6​

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

Katie​

[/td][td]

Dan​

[/td][/tr]

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

7​

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

Jess​

[/td][td]

Niki​

[/td][/tr]

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

8​

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

Tim​

[/td][td]

Niki​

[/td][/tr]

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

9​

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

Kim​

[/td][td]

Niki​

[/td][/tr]

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

10​

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

Tom​

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

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

11​

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

Helper column (gray area)
Formula in C1 copied down
=SUM(B$1:B1)

Formula in F2 copied down
=IF(ROWS(F$2:F2)>SUM(B$2:B$4),"",INDEX($A$2:$A$4,MATCH(ROWS(F$2:F2)-1,C$1:C$4)))

Hope this helps

M.

 

[Nikijune]

Thank you Marcelo, this seems to be doing the job.

I do have one problem with the formula though. It is not picking up the first QA analyst from the first list. i.e Robin.

So the first 4 advisors are coming up with #N/A and then it picks up Dan as the first name, and then shows Niki for the next 3... I have played around with the formula but to no avail....

 

[Marcelo Branco]

I don't know why it's not working for you. It worked perfectly for me.

Tell us the exact location (rows/columns) of List1 and List2

M.

 

[Nikijune]

Hi Marcelo,

My tables match yours above, except they are in different sheets. So my formula is slightly different looking at the tabs;

=IF(ROWS(B$2:B2)>SUM(Sheet1!$B$2:$B$50),"",INDEX(Sheet1!$A$2:$A$50,MATCH(ROWS(B$2:B2),Sheet1!C$1:C$50)))

It is the first advisor, Robin, who is coming through as an error. Example below;
[TABLE="width: 139"]
<tbody>[TR]
[TD]Advisor[/TD]
[TD]QA Analyst[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Katie[/TD]
[TD]Dan[/TD]
[/TR]
[TR]
[TD]Jess[/TD]
[TD]Niki[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]Niki[/TD]
[/TR]
[TR]
[TD]Kim[/TD]
[TD]Niki[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Kim[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]Kim[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Jim[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]Jim[/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]Jim[/TD]
[/TR]
[TR]
[TD]Katie[/TD]
[TD]Tim[/TD]
[/TR]
[TR]
[TD]Jess[/TD]
[TD]Tim[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Kim[/TD]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Rob[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]Rob[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Rob[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[Marcelo Branco]

You missed the -1 (see my formula in post 2)

=IF(ROWS(B$2:B2)>SUM(Sheet1!$B$2:$B$50),"",INDEX(Sheet1!$A$2:$A$50,MATCH(ROWS(B$2:B2)-1,Sheet1!C$1:C$50)))

M.