Formula to return name from list - only once.

Hello everyone,

Looking to add another option to a workbook that I have been working on. This will be for excel 2010

I have added a test workbook which outlines the end result I am looking for.

Basically this is a two tab workbook.

• On tab 2 - Column B6:B1000 - I need to return a name from tab 1 but only do so once (the name could appear several times on tab 1)
• New data will be added to tab 1 daily
• Also on Tab 2 - Column A6:A1000 I would like to return the name that is in the same row as the name returned from Tab 1

The above is spelled out in the workbook as well - may be a little clearer in the test workbook.

Thanks in advance - look forward to spreading some rep.

SVTF

B6=IF(COUNTIF('CEI Nov 2014'!B$5:B5,'Feilding Info'!D2)>=1,"",'Feilding Info'!D2)
OR
B6=IFERROR(INDEX(OFFSET('Feilding Info'!$D$1,1,0,COUNTA('Feilding Info'!$D:$D)-1,1),MATCH(0,INDEX(COUNTIF($B$5:$B5,OFFSET('Feilding Info'!$D$1,1,0,COUNTA('Feilding Info'!$D:$D)-1,1)),0),0)),"")
TRY THE ABOVE AND COPY TOWARDS DOWN

@nflsales - your option # 2 formula takes care of column B - Seems to be working as needed.

Need to solve one more thing and I think I can close this up.

What formula can be used for column A6:A1000 on tab 2?

Based on the name entered in B6:B1000 from the formula on Tab # 2....

I need to also populate A6:A1000 with the name located on Tab #1 in the same row of the name that ended up in B6:B1000 on tab # 2 ... see test work book for desired result.

In your sheet you have multiple locations for some advisors in this case what is you expected results
for example CC Advisor have LB and OP Locations and CL have IL, MM and PD

In the final version there will be no duplication.

Example

If b6 returns the name DM and that is pulled from tab 1 D2. What ever the name is in B2 on tab 1 carry over to A6 tab 2.

So in the end... Tab 2 A6 and B6 .... Have the same names as Tab 1 B2 and D2 so on and so forth.



Did that come across ok?

A6=INDEX('Feilding Info'!B:B,MATCH(B6,'Feilding Info'!D:D,0))
try it in A6 and copy towards down

Thank you SVTF