# Create a list of names based on a number indicator

#### Nikijune

##### Board Regular
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

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

#### Marcelo Branco

##### MrExcel MVP
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
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

##### MrExcel MVP
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
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;
 Advisor QA Analyst Jim #N/A Bob #N/A Sarah #N/A Greg #N/A Katie Dan Jess Niki Tim Niki Kim Niki Tom Kim Jim Kim Bob Jim Sarah Jim Greg Jim Katie Tim Jess Tim Tim Bob Kim Bob Tom Rob Jim Rob Bob Rob

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

#### Marcelo Branco

##### MrExcel MVP
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.

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

### 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...