Filtering assistance

[Guest]

I have a spreadsheet that I want to autofilter, but the design of the
spreadsheet is preventing me from picking up all of the data. I downloaded
some data out of Oracle and it's set up like this

A B C
D
Customer Name Project Name 2004 2005
Customer XYZ Project 1 100
200
Project 2 0
150
Project 3 280
0
Customer ABC Project 1 240
100
Project 2 0
120
and so on...

So when I filter column A and make a selection I only see Project 1, and
none of the other projects under that customer. Is there any way to get all
of the projects under a customer, or is there an easy way to get excel to
populate those cells in column A? For example using the above data, get
cells A3 and A4 to read Customer XYZ, and get A6 to read Customer ABC?

Ted

 

[Alan Beban]

One way, if your data is as orderly as your posting suggests, is to run
the following code snippet with the relevant sheet active:

For Each iCell In Range("A1:A12")
If iCell.Value = "" Then iCell.Value = iCell(0).Value
Next iCell

Substitute the relevant ending cell for A12. Running the code will
insert in all the blank cells in the range the preceding company name.

Alan Beban

 

[Harlan Grove]

Ted Metro wrote...

I have a spreadsheet that I want to autofilter, but the design of the
spreadsheet is preventing me from picking up all of the data. I downloaded
....
So when I filter column A and make a selection I only see Project 1, and
none of the other projects under that customer. Is there any way to get all
of the projects under a customer, or is there an easy way to get excel to
populate those cells in column A? For example using the above data, get
cells A3 and A4 to read Customer XYZ, and get A6 to read Customer ABC?

If you want to keep the format (mostly) and want to avoid VBA, you
could insert a column immediately to the left of the table, enter
anything in the topmost cell (row of field names), and enter the
following formula in the cell below that (I'll assume this cell is A2)

A2:
=IF(TRIM(B2)<>"",B2,A1)

and fill A2 down as needed. Then select the augmented table including
the inserted col A, and autofilter on col A.