Create a list of names based on a number indicator

Nikijune

Board Regular
Joined
Aug 16, 2016
Messages
51
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
Maybe using a helper column (to make the formula simpler)


A
B
C
D
E
F
1
QA Analyst Name​
No of Audits to complete​
0​
Advsiors​
Qa Analyst​
2
Robin​
4​
4​
Jim​
Robin​
3
Dan​
1​
5​
bob​
Robin​
4
Niki​
3​
8​
sarah​
Robin​
5
greg​
Robin​
6
Katie​
Dan​
7
Jess​
Niki​
8
Tim​
Niki​
9
Kim​
Niki​
10
Tom​
11

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

Board Regular
Joined
Aug 16, 2016
Messages
51
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.... :confused:
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
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

Board Regular
Joined
Aug 16, 2016
Messages
51
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;
AdvisorQA Analyst
Jim#N/A
Bob#N/A
Sarah#N/A
Greg#N/A
KatieDan
JessNiki
TimNiki
KimNiki
TomKim
JimKim
BobJim
SarahJim
GregJim
KatieTim
JessTim
TimBob
KimBob
TomRob
JimRob
BobRob

<tbody>
</tbody><colgroup><col><col></colgroup>
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
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.
 

Forum statistics

Threads
1,082,133
Messages
5,363,341
Members
400,728
Latest member
Hoan1985

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top