• 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

    Hui...

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

Excel query

Gaurang Mhatre

Gaurang Mhatre

Member
Dear Friends,

I have 1 query in excel.


I have data in the below format in sheet 1 (named it as “Contact list”)

Fig:1

upload_2014-3-6_10-21-7.png

The list goes on till Sr No 40, I have not provided phone no, email id, in above data due to confiendiality.


Now, in new sheet 2 (named it as Snapshot) i have made a “SNAPSHOT.

Fig:2

upload_2014-3-6_10-21-37.png
I have done data validation in D6, as seen above, and in D8 (Contact person column) I have put vlookup formula, and same formula I will apply in phone no, email id, cgmo location head, reporting authority, city, branch and zone column. So that, when I select any location in column D6 the rest details will come automatically via vlook up formula. But, now the problem is, in sheet 1. There are multiple locations along with the person in charge for that location. For example, If I select location in column D6 (Snapshot sheet) as Delhi/NCR then it will give me only 1 detail, but in sheet 1 (Shown in fig1) there are multiple Delhi/NCR and CCSE names towards it. Due to which vlook up captures only the unique value and does not capture duplicate details. Hope you are getting what I am trying to say.

What I want is, to capture the contact person name which comes under that location whenever I select the location drop down list in column D6. I want that in data validation format (Drop down list), so that contact person name will come one below the other. Same format & formula should be applied to other columns as well. So for example if I selection location as Delhi/NCR then multiple contact person names should come pertaining to that location, which vlook up failed to captured before.

What is the formula to do that? Max,LARGE?? I tried vlook up + max formula but failed! Or is it a macro, i want to do it widout macro, is it possible?

Can you help me capture multiple details as suggested above by just selecting location.
Instead of data validation (drop down list) in column D6 can we put INDEX MATCH formula? So that instead of selecting from data validation list, typing the location will be much simpler, what say?


Please help me out with this.

Thanks for your help.

Regards,
Gaurang Mhatre
 
Last edited:
Hi Gaurang,

You have to have a unique key to identfy which row you are looking at say for DELHI/NCR. so if you can insert an extra column in your data which can act as unquie key than your task become easy.

Say before column B insert an column and put this formula.
=IF($C2="","",$C2&"-"&COUNTIF(C$2:C2,$C2))

Now you can create a dropdown from this or you can create a cascade dropdown also first this and than select on city.

Regards,
 
Hi Somendra,


First of all thanks for taking out your valuable time for me.

Thanks for the help buddy, anyways I was trying to tell you as shown in the below picture. The formula which you mentioned, can it give me the below result (shown in pic)

upload_2014-3-6_11-40-58.png

When I select Location in snapshot sheet, Contact person name should come in drop down list since in Delhi/NCR location there are 4-5 people pertaining to it. In above figure, it shows only 1 coz of vlook up formula. I want list of all people pertaining to Delhi/NCR location. So if there are 4 people, then 4 people name should come in drop down list, To give you a clear picture, please check the below edited pic.

upload_2014-3-6_11-41-13.png

I want a formula where the details will come automatically with single click.

Is it possible??


Thanks!

Regards,
Gaurang Mhatre.
 
Hi,

See the attached file. If this method is usable to you? otherwise it has formula that can help you achieve what you want.

Just advise if any issue.

Regards,
 

Attachments

Hey Soumendra,
The excel formula is working, thanks :)
But, can you explain me y you have used IF,SMALL formula and y you have put ROWS(List!E$2:E3))) in last...iknw its if statement bt y selected tht column??
=IFERROR(INDEX(Data!$B$2:$B$11,SMALL(IF(List!E$1=Data!$C$2:$C$11,ROW(Data!$C$2:$C$11)-ROW(Data!$C$2)+1),ROWS(List!E$2:E3)))

Regards,
Gaurang.
 
In the list sheet,
Column A--- Extracting unique city names
Column B,C,D.... Extracting different values w.r.t. city to be used in data validation list.

Regards,
 
Hi Patnaik,

The excel is not supporting, it is showing error.
I am using microsoft 2010. Can u change file format??

Regards,
Gaurang
 
@Gaurang

Thanks for your kind words, welcome back if any problem

Gaurang we all are learners and we can learn more from this forum

Thanks
 
You must log in or register to reply here.
Back
Top