Retrieve data based on two conditions

[ApolloID]

Hi, i have this situation:

Sheet1:
dep1...john...some datas1
dep2...jim.....some datas2
dep2...john...some datas3
dep1...john...some datas4
dep1...jim.....some datas5
.....
Sheet2:
I need in sheet2 to retrive all rows containing two criterias: (dep1 and john)
sheet2 should look like this:
dep1...john...some datas1
dep1...john...some datas4
.....
Can this be done?
Thanks!

 

[Sandeep Warrier]

How about

Excel 2007<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(166, 170, 182); border-collapse: collapse;" cellpadding="2.5px" rules="all"><colgroup><col style="background-color: rgb(224, 224, 240);" width="10px"><col><col><col></colgroup><thead><tr style="background-color: rgb(224, 224, 240); text-align: center; color: rgb(22, 17, 32);"><th>
</th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">1</td><td style="font-weight: bold;">Dept</td><td style="font-weight: bold;">Name</td><td style="font-weight: bold;">Data</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">2</td><td style="">dep1</td><td style="">john</td><td style="">some datas1</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">3</td><td style="">dep2</td><td style="">jim</td><td style="">some datas2</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">4</td><td style="">dep2</td><td style="">john</td><td style="">some datas3</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">5</td><td style="">dep1</td><td style="">john</td><td style="">some datas4</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">6</td><td style="">dep1</td><td style="">jim</td><td style="">some datas5</td></tr></tbody></table>

Sheet1​

Excel 2007<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(166, 170, 182); border-collapse: collapse;" cellpadding="2.5px" rules="all"><colgroup><col style="background-color: rgb(224, 224, 240);" width="10px"><col><col><col><col></colgroup><thead><tr style="background-color: rgb(224, 224, 240); text-align: center; color: rgb(22, 17, 32);"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">1</td><td style="font-weight: bold;">Dept</td><td style="font-weight: bold;">Name</td><td style="font-weight: bold;">Data</td><td style="font-weight: bold;">Count</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">2</td><td style="">dep1</td><td style="">john</td><td style="">some datas1</td><td style="text-align: right;">2</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">3</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="">some datas4</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">4</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">5</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="">
</td><td style="text-align: right;">
</td></tr></tbody></table>

Sheet2​

<table style="border: 2px solid black; border-collapse: collapse; padding: 0.4em; background-color: rgb(255, 255, 255);" cellpadding="2.5px" width="85%" rules="all"><tbody><tr><td style="padding: 6px;">Worksheet Formulas<table style="border: 1px solid rgb(166, 170, 182); text-align: center; background-color: rgb(255, 255, 255); border-collapse: collapse;" cellpadding="2.5px" width="100%" rules="all"><thead><tr style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);"><th width="10px">Cell</th><th style="text-align: left; padding-left: 5px;">Formula</th></tr></thead><tbody><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">D2</th><td style="text-align: left;">=SUMPRODUCT(--(Sheet1!$A$2:$A$6=$A$2),--(Sheet1!$B$2:$B$6=$B$2))</td></tr></tbody></table></td></tr></tbody></table>
<table style="border: 2px solid black; border-collapse: collapse; padding: 0.4em; background-color: rgb(255, 255, 255);" cellpadding="2.5px" width="85%" rules="all"><tbody><tr><td style="padding: 6px;">Array Formulas<table style="border: 1px solid rgb(166, 170, 182); text-align: center; background-color: rgb(255, 255, 255); border-collapse: collapse;" cellpadding="2.5px" width="100%" rules="all"><thead><tr style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);"><th width="10px">Cell</th><th style="text-align: left; padding-left: 5px;">Formula</th></tr></thead><tbody><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">C2</th><td style="text-align: left;">{=IF(ROWS($C$1:C1)<=$D$2,INDEX(Sheet1!$C$2:$C$6,SMALL(IF(Sheet1!$A$2:$A$6=$A$2,IF(Sheet1!$B$2:$B$6=$B$2,ROW(Sheet1!$C$2:$C$6)-ROW(Sheet1!$C$2)+1)),ROWS($C$1:C1))),"")}</td></tr></tbody></table>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself</td></tr></tbody></table>

 

[ApolloID]

It's working! Sorry for the late response!
Thanks!