Is there a Function or Macro for This?

[Marilyn]

Hello All,

I have an excel spreadsheet with 10 columns. One of the columns is called
"Add Usage" and in that column I have blank cells but I also have cells that
reads "Add 2008 usage."

I would like to create a formula or macro that will loop or go through the
list
and then populate the data for that Row at the bottom of the sheet whenever
it finds the word "Add 2008 Usage" in the Add usage column.

Basically the results would be all the information from column A - J based
on the "Add Usage" criteria.

Is that possible? I tried an IF statement but quickly failed. Would a
macro do it?

Thanks,

 

[Barb Reinhardt]

Why not use autofilter to find the Add 2008 Usage columns and copy/paste them
to the bottom? Would that work?

 

[Max]

Here's a formulas play which auto-extracts the required list into an adjacent
area to the right of the source data

Assuming source data in cols A to J, from row2 down, with the key col = col
B (say) which will contain the text indicator: Add 2008 usage

In K2:
=IF(B2="","",IF(B2="Add 2008 usage",ROW(),""))
Leave K1 blank

In L2:
=IF(ROWS($1:1)>COUNT($K:$K),"",INDEX(A:A,SMALL($K:$K,ROWS($1:1))))
Copy L2 across by 10 cols to U2. Select K2:U2, copy down to cover the max
expected extent of source data. Hide away col K. Cols L to U will return only
the lines marked as: Add 2008 usage in col B, wil all lines neatly bunched
at the top. The results will be dynamic to changes made in the key col B.

 

[Marilyn]

Max You are a GENIOUS!!!!!! This formula worked like a charm......Thank you
so much for your time

 

[Marilyn]

Would you have some time to explain it?

Here's a formulas play which auto-extracts the required list into an adjacent
area to the right of the source data

Assuming source data in cols A to J, from row2 down, with the key col = col
B (say) which will contain the text indicator: Add 2008 usage

In K2:
=IF(B2="","",IF(B2="Add 2008 usage",ROW(),""))
Leave K1 blank

In L2:
=IF(ROWS($1:1)>COUNT($K:$K),"",INDEX(A:A,SMALL($K:$K,ROWS($1:1))))
Copy L2 across by 10 cols to U2. Select K2:U2, copy down to cover the max
expected extent of source data. Hide away col K. Cols L to U will return only
the lines marked as: Add 2008 usage in col B, wil all lines neatly bunched
at the top. The results will be dynamic to changes made in the key col B.

 

[Max]

Would you have some time to explain it?

Here's some explanations:

Col K is the criteria col. It flags lines within the source data which
satisfy the criteria with arbitrary row numbers. These arb row numbers are
then read sequentially by the SMALL part in the formulas in cols L to U to
extract & bunch all lines satisfying the criteria neatly to the top.

The refrain to "Leave K1 blank" is to ensure that K1 doesn't contain any
number which would disrupt the set of arbitrary row numbers generated in
row2 down.

The INDEX(A:A, .. portion will return the corresponding source data from col
A into col L. When copied across to U2, it'll increment to INDEX(B:B, .. ,
INDEX(C:C, .., etc to return the rest of the source cols accordingly into
cols M to U.

The SMALL($K:$K,ROWS($1:1)) part locks onto the criteria col K to return the
smallest row number from col K
When copied down, ROWS($1:1) will increment sequentially to return the
numbers: 1,2,3 .. . These sequential numbers will be passed into
SMALL($K:$K,..) to then return the smallest row number, the 2nd smallest,
the 3rd smallest, ... from the criteria col K successively.

INDEX(A:A,SMALL($K:$K, ...))
INDEX(B:B,SMALL($K:$K, ...))
etc then returns the data in the source cols A to J corresponding to the
smallest row number, the 2nd smallest, the 3rd smallest, ... within the
criteria col K successively, hence returning the required results.

The front IF trap: IF(ROWS($1:1)>COUNT($K:$K),"",..
serves to return neat looking blanks: "" once all the arb row numbers in the
criteria col K are "exhausted" in the formulated range in cols L to U.

COUNT($K:$K) returns the total number of arb row numbers generated in the
criteria col K, which number equals the number of lines satisfying the
criteria to be returned within cols L to U. So once the sequential numbers
generated by ROWS($1:1) in the copy down exceeds this COUNT($K:$K), the IF
trap will evaluate to TRUE and return the blanks: "".

 

[Max]

welcome, glad it worked for you.