How to filter & aggregate nonblank cells that all contain formulas


G

Guest

I have a range of cells, say A1:B10. All of these cells contain formulas
that conditionally display a value in that cell. So, imagine looking at
A1:B10 and just a handful of the cells are displaying a value. i.e. the
formula in each of the cells resembles: IF(<some condition>,<display
conditional value>,"")

I want to filter all the nonblank cells into another location, and all in
one column preferrably. e.g. A3 shows "foo", A5 shows "squim", B7 shows
"flam", all others in A1:B10 contain the empty string "". I need D1:D3 =
"foo", "squim", "flam".

Autofilter on nonblanks is not exactly what I want since I want to combine
all the nonblanks into another location.
Also, I tried using ISBLANK to filter the cells, but I guess ISBLANK
considers the presence of a formula to be nonblank despite what is being
shown in the cell.

Thanks for looking!
-brandon
 
Ad

Advertisements

G

Guest

Here's one way to do it. In cell D1, enter =a1&b1. Copy that to d2:d10.
Then auto filter nonblanks.
 
Ad

Advertisements

G

Guest

Thanks for the suggestion, but I've got something a bit more complex (of
course!)...
In reality, I'm trying to aggregate over several columns, say A1:V1000,
where different cells on the same row could be nonblank. e.g. B6 = "scree"
and H6 = "flop", and I don't want the aggregate column to contain
"screeflop", but rather "scree" in one cell then "flop" on the next cell. So
combining the cells for a given row is going to pair up nonblank entries for
the same row, which I don't want to happen. I want a resultant column that
is aggregation of all individual nonblank cells in the filter set.
Any ideas?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top