Create list of related Data

[shasi.shekhar@gmail.com]

we have a data set like this-

Region City

North JAIPUR

North DELHI

East KOLKATA

East PATNA

South BANGALORE

South CHENNAI


Now in other sheet, I have created a drop down list of Regions in A1, Now I want that all the cities of selected region to be shown from B1 to B9999.


Please help how this can be done without a macro.

 

[vijaySharma]

Shasi,


your requirement is not clear... when North is selected from the dropdown, do you want only 1 city to be repeating OR do you need a dropdown in B1:B9999 to allow you to select.


If the second option have a look at


http://www.contextures.com/xlDataVal02.html


~VijaySharma

 

[prasaddn]

Hi Shasi Shekhar,


I assume you have data set in Col A and B of Sheet1.


Can you add one helper column C in sheet1 with formula below in cell C2 and drag down till the end of the data set:

=A2&COUNTIF($A$2:A2,A2)


Now, to get your result in sheet2:

i assume you have data validation in A1 and expect result in B1 and down:

paste the below formula in Sheet2, B1 cell

=IF(COUNTIF(Sheet1!$A$2:$A$7,$A$1)>(ROWS($A$1:A1)-1),INDEX(Sheet1!$B$2:$B$7,MATCH($A$1&ROWS($B$1:B1),Sheet1!$C$2:$C$7,0),1),"")


You need to drag this down till rows that you expect data. Not necessarily till B9999


Regards,

Prasad

 

[Hui]

Shasi


you may also want to have a look at: http://chandoo.org/wp/2011/11/18/formula-forensics-003/