Removing blank spaces dynamically using excel formula

[Ankita Jain]

Hi All,

I have a data table which looks like this:

          A          B        C         D
1        yes/no    Data1    Data2    Data3
2        yes       abcd1
3        no        abcd2    abcd3    abcd4
4        yes
5        no        abcd5    abcd6    abcd7
6        no        abcd8    abcd9    abcd10
7        yes
8        yes

I want to select all "no" (row 3, 5, 6) from yes/no column and add into another table without leaving any blank spaces. The output should be like this:

A B C D
1 yes/no Data1 Data2 Data3
2 no abcd2 abcd3 abcd4
3 no abcd5 abcd6 abcd7
4 no abcd8 abcd9 abcd10

Please help me out to create a formula in excel and achieve this dynamically. It's not necessary that "no" should always be present in Row(3), Row(5) and Row(6).

Thank You..!!

 

[json]

there are a couple ways you can do this:

1) By using the same cell range, you can turn on Filtering and then just select "no" from the filter options
2) Advanced filter
3) create a pivot table from your data, then arrange & filter the data as desired
4) a formula on this other sheet that pulls the desired data over
5) vba: but i dont know enough about this to be able to help

Do you have a preference?

It may be helpful to know how or what you'll do with the 'results'

 

[Xen]

You might find this useful:
http://chandoo.org/wp/2011/11/18/formula-forensics-003/

 

[Ankita Jain]

You might find this useful:
http://chandoo.org/wp/2011/11/18/formula-forensics-003/

Thank You....!!
It really helped....

 

[Ankita Jain]

there are a couple ways you can do this:

1) By using the same cell range, you can turn on Filtering and then just select "no" from the filter options
2) Advanced filter
3) create a pivot table from your data, then arrange & filter the data as desired
4) a formula on this other sheet that pulls the desired data over
5) vba: but i dont know enough about this to be able to help

Do you have a preference?

It may be helpful to know how or what you'll do with the 'results'

Thank You for replying and helping....