skipping blanks?

  • Thread starter Thread starter John Smith
  • Start date Start date
J

John Smith

For excel experts, a table speaks a thousand words, so here you go:

A B C D
1 X Row1 Row1 LOOKUP(1)
2 X Row2 Row2 LOOKUP(2)
3 Y Row3 Row3 LOOKUP(3)
4 X Row4 Row4 LOOKUP(4)
5 0 Row8 LOOKUP(8)
6 0 Row9 LOOKUP(9)
7 0
8 Y Row8
9 X Row9

My problem: Column B above is easy to create, but I want to be able to
create it like column C above, so that my spreadsheet is clean in the
end (there are a number of lookups afterwards - example of column D).

Now in words:

I want to create a list of all the rows in column A that aren't zeros
(or blanks, doesn't matter). The list will then be displayed in a new
column, which will use those references to create a number of
subsequent lookups.

I know that there are a number of ways to solve this after creating
column B, such as filtering non-blanks afterwards, but that doesn't
solve my problem as the list itself is dynamic and I would have to
filter all and then non-blanks again to see the real new list.

Any ideas?

Thanks!


P.S.: Everything here is dynamic, including the spacing between the
blank and non-blank rows.
 
One easy way to dynamically "clean/close it up" in adjacent cols to the right
of source cols

Assume your source data in cols A and B, where the key col = col A, with
blank cells or zeros in col A indicating lines to be skipped
Put in C1: =IF(OR(A1={"",0}),"",ROW())
Copy C1 down to cover the max expected extent of source data, say down to
C500? Minimize/hide col C

Put in D1: =IF(ROW()>COUNT($C:$C),"",INDEX(A:A,SMALL($C:$C,ROW())))
Copy D1 to E1, fill down by the smallest extent large enough to cover the
max expected number of lines (less the skips), say down to E100? Cols D and E
will return the desired results, all neatly packed at the top. Success? wave
it here, hit the YES/rating stars in google
 
One easy way to dynamically "clean/close it up" in adjacent cols to the right
of source cols

Assume your source data in cols A and B, where the key col = col A, with
blank cells or zeros in col A indicating lines to be skipped
Put in C1: =IF(OR(A1={"",0}),"",ROW())
Copy C1 down to cover the max expected extent of source data, say down to
C500?  Minimize/hide col C

Put in D1: =IF(ROW()>COUNT($C:$C),"",INDEX(A:A,SMALL($C:$C,ROW())))
Copy D1 to E1, fill down by the smallest extent large enough to cover the
max expected number of lines (less the skips), say down to E100? Cols D and E
will return the desired results, all neatly packed at the top. Success? wave
it here, hit the YES/rating stars in google
Click to expand...

Max,

Thanks for the tips. I ended up using an abbreviated version of it
without the index function

Starting at E14: =IF(ROW()-13>COUNT($D:$D),"",SMALL($D:$D,ROW()-13))

That worked like a charm.

Thanks again
 
Welcome, glad to hear
Celebrate success, ring the stars in google
--
Max
Singapore


Max,

Thanks for the tips. I ended up using an abbreviated version of it
without the index function

Starting at E14: =IF(ROW()-13>COUNT($D:$D),"",SMALL($D:$D,ROW()-13))

That worked like a charm.

Thanks again
 
Back
Top