# Look up to find data on duplicate values

#### ushahi

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

Regards

#### Attachments

• 18.5 KB Views: 13

#### bosco_yip

##### Excel Ninja
Try..........

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"))

Regards
Bosco

#### ushahi

##### New Member
Thank you for taking the time to help me, it works perfectly.
Thanks a bunch.

#### ushahi

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