is it possible, filter data come in a2, b2 cell

[gsrikanth]

filter data come in a2 b2

find the attachment

if we select city and street one unique name will get
only visible one get in a2, b2

it is like drop down menu
iam looking for find alternative for dropdown menu with a simple formula,

find the attachment

[Colin Legg]

There isn't a simple formula to do this. I've attached your example with two array formulas.

A2
 
=IF(SUBTOTAL(3,A4:A1753)=1,
    OFFSET(A4,MAX((SUBTOTAL(3,OFFSET(A4,ROW(A4:A1753)-ROW(A4),,1)))*ROW(A4:A1753))-ROW(A4),0)
    ,"")
 
B2
 
=IF(SUBTOTAL(3,B4:B1753)=1,
    OFFSET(B4,MAX((SUBTOTAL(3,OFFSET(B4,ROW(B4:B1753)-ROW(B4),,1)))*ROW(B4:B1753))-ROW(B4),0)
    ,"")

Array formulas need to be entered with CTRL+SHIFT+ENTER, not just ENTER.

By the way, please can you update your profile to reflect the correct version of Office which you are using. It has a major influence on the appropriate answer(s) to your question(s).