• 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


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

Look up to find data on duplicate values


New Member
Hi Everyone,

I am having a hard time trying to figure out a solution for my below attached query.

In the attached excel workbook below on 1st sheet, column "A" stores the data for different sales offices and there are multiple employees assigned to most of the sales offices.

My requirement: is to fetch the data from Sheet 2 to Sheet 1, have the name of employees to their respective sales office and since most of the sales offices occurs more than once the employee name should not be repeated.

For example Atlanta has 2 employees and on sheet 1 Atlanta appears twice so both should have a separate name just as provided on Sheet 2.

I have added the result the way it should look on Sheet 1 only.

Can anyone please suggest something.

Thank You in advance




Excel Ninja

In "Sheet1" B2, formula copied down :

=IF(A2="","",IFERROR(INDEX(Sheet2!$B$1:$B$35,AGGREGATE(15,6,ROW($B$1:$B$35)/(Sheet2!$A$1:$A$35=A2),COUNTIF(A$2:A2,A2))),"Not available"))



New Member
Hello Everyone,

I have a query on the sheet attached below.

The reason i m posting it here is that i am not sure whether there's some formula that can solve this or VBA is required to answer this.

My Query is, that on Sheet 1 in Column A i have some sales offices for which the employees are listed on the Sheet 2 in Column B.
Now, I m required to use any method to bring those few names to Sheet 1 in Column B with their respective sales offices.

But the challenge is that the records on Sheet 1, even after using several formula combinations, keep on repeating.

For example: on Sheet 1 Chicago has 16 records whereas, on the Sheet 2 employees available against Chicago are only 4. So, i would like to know if there's some method that can make all those 4 employees appear on Sheet 1 against their sales office i.e. Chicago and reset the count when it hits 4 and initiate the same process again for the next 4 records on Sheet 1 and so on.

I have added the result on the same sheet.

Thank You in advance