excel compare value & list results

[rosh2g]

Dear Professionals,

I have 2 sheets in excel.

1 sheet has value as below:
Supervisors Research Specialist
Anand Physics
Carren Physics
Bimal Chemistry
Carren Chemistry
darren Biology
Enoel Physics
Enoel Maths

2nd sheet has:
Research Topic Supervisor
Physics




My criteria is that physics (research topic) in 2nd sheet is matched with all values in 1st sheet research specialist, & anand, enoel & carren are available in list to chose under 2nd sheet supervisor field. Please could someone help me with formula on this? Appreciate your help.

Thanks

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[AliGW]

Welcome to the forum!

Excel 2016 (Windows) 32 bit

	A	B	C	D	E
1	Supervisors	Research		Research	Supervisor
2	Anand	Physics		Physics	Anand
3	Carren	Physics			Carren
4	Bimal	Chemistry			Enoel
5	Carren	Chemistry
6	darren	Biology
7	Enoel	Physics
8	Enoel	Maths

Sheet: Sheet2

Excel 2016 (Windows) 32 bit

	E
2	=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$8=$D$2,ROW($A$2:$A$8)),ROWS($A$1:A1))),"")

Sheet: Sheet2

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[shukla.ankur281190]

Sheet1

Row\Col	A	B
1	Supervisors	Research Specialist
2	Anand	Physics
3	Carren	Physics
4	Bimal	Chemistry
5	Carren	Chemistry
6	darren	Biology
7	Enoel	Physics
8	Enoel	Maths

Sheet2

Row\Col	A	B
1	Research Topic	Supervisor
2	Physics	Anand
3		Carren
4		Enoel
5

B2

Please Login or Register  to view this content.

Copy down.!!

[samba_ravi]

Use Pivot Table

[AliGW]

If you are going to make a suggestion like this, i.e. using a pivot table, please provide an example, as not everyone is conversant with them. Thanks!

[samba_ravi]

sorry Aligw, File Attached.

[AliGW]

Thanks, Siva - that's very helpful.

[rosh2g]

Thanks Ankur. I am looking to get Anand, Carren & Enoel in single cell in drop down menu! Thanks.

[AliGW]

If you want a drop-down menu, then you need to base it on the list generated in the solution I offered.

[shukla.ankur281190]

Check the attached file is this what you looking for

[Glenn Kennedy]

I really do not follow what you want. You still haven't uploaded a sample sheet showing your desired result. Is this anyway close?

I will explain later. Enable macros on opening.

[AliGW]

The OP has completely ignored the suggestions offered by all but one person in this thread, so offering guidance is not easy.

@Rosh2g - out of courtesy, please give some feedback on each of the suggestions offered. Members are going out of their way to try to help you. Thanks.

[rosh2g]

Hi Ankur,

Yes it is the one i am looking for. Please could you share how to do it?

Thanks

[AliGW]

Are the rest of us completely invisible?

[rosh2g]

Sorry AliGW,

Bit panicked & tired of looking in internet so couldn't reply all. I am looking all posts. Thanks.

Thanks

[AliGW]

It would be nice if you at least acknowledged those who have offered you help, tired and panicked or not.

[rosh2g]

Appreciate your help Ali & everyone. Still waiting for formula to get result!

[AliGW]

But the formulae you need are in the attachment you were provided in post #11.

[rosh2g]

Hi AliGW,

Hope you are well. Thanks for your reply.
How can i see formula??? Any idea.

Thanks

[AliGW]

Open the attachment provided in post #11.

[rosh2g]

Thanks ALiGW. I have opened it but cannot see formula!

Thanks

[Glenn Kennedy]

Shukla was maybe trying to do too much. Open the sheet at Post 11 and follow the instructions (it took me a while to see it) ... delete the contents of cell F1.

[rosh2g]

Dear Shukla,

Thanks for sharing. I can see that you have pulled the result in D column of sheet 2, & then pulled it in a list of B column(Sheet 2). Is there a way we can pull result directly in a list in B column of Sheet 2?

Thanks for your help.

[shukla.ankur281190]

May be someone else who is expert in VBA can do this, I shared what I know through formula.

Glenn sorry if you have seen the dot part for a while

[Glenn Kennedy]

Did you EVER look at Post 12?

[shukla.ankur281190]

Did you EVER look at Post 12?

No I was quite busy with my company closing these days I have seen right now your attached file, but there is #Name error showing