Excel 2007 : Preventing some data to be filtered while filtering

**skol** · 06-06-2012, 02:12 PM

My problem is the following:

Suppose you have 2 columns, A and B.
Column A contains countries and column B contains for every value of column A multiple values. F. Ex.:

A B
USA Atlanta
Boston
Chicago

Belgium Brussels
Luik

Suppose I am interested in USA, I filter on column A and select USA. But I want to see all the cities that belong to A (= values of column B). If I filter on USA, I only get to see the one in the same row, namely Atlanta.

How can I prevent the filter to be applied on the other rows? Merging for example does not help in this case.

Thanks in advance!

**Pepe Le Mokko** · 06-06-2012, 02:37 PM

You could use a helper column to populate the missing countries
Something like = IF(A2="",A1,A2) and pull down as needed - Then eventually Paste special values, or use the helper column instead of the original " countries" column

**skol** · 06-06-2012, 03:06 PM

Is it possible to elaborate your reply because I don't fully get it actually.

If I understand correctly, I should create another column, column C with an if clause. But the one you specified, for example the "", what does it mean?

Ultimately the purpose would be that I can see all regions/cities after the selection via filter of USA.

Thanks again.