View Single Post
#3
October 28th 04, 01:31 AM
 bob Posts: n/a

Yes, I always want to allow 25 rows for each section, i.e. to insert whatever
number of rows are needed to push Bos to row 26 and Cha to row 51.

I tried your formula and I get the same value ("Atl.") as the result in
every cell. So my questions a

what is the logic behind this approach?
do i sort in ascending or descending order in columns C and U?

thanks very much,
bob

"Myrna Larson" wrote:

Do you mean you ALWAYS want to insert 10 and 6 rows, respectively, or that you
want to allow 25 rows for each section, i.e. to insert whatever number of rows
are needed to push Bos to row 26 and Cha to row 51?

If the latter, here's a way to do it manually, with some formulas and sorting.
Your data is now in A2:T50, right? Put a header of some kind in U1. In U2, put
this formula: =C2 and copy down through the last existing data row (with
your example, that would be U50). In U51 put this formula:

=IF(COUNTIF(U\$2:U16,"Atl")<25,"Atl",
IF(COUNTIF(U\$2:U16,"Bos")<25,"Bos",
IF(COUNTIF(U\$2:U16,"Cha")<25,"Cha","")))

all on one line in the formula bar, of course. Copy that formula down through
row 76, or until it begins to return empty text.

Now Data/Sort, with the first key column U, the 2nd key column C. Then you can
delete column U.

But this would be cumbersome with more than 3 sections to be expanded. In that
case, you may need a vba macro.

On Wed, 27 Oct 2004 10:45:14 -0700, bob wrote:

Worksheet is 20 columns x 50 rows. Column C is populated with the string
"Atl." from rows 2-15, with "Bos." in rows 16-33, and with "Cha." in rows
34-46.

I want to automatically insert 10 blank rows, beginning with the first cell
in Column C that isn't populated with "Atl." (row 16 in this example). Then I
want to insert 6 blank rows, beginning with the first cell in Column C that
isn't populated with "Bos."

I want to account for that fact that the number of "Atl." rows will be
variable; they always begin with row 2 but may not always end with row 15.
And that the "Bos." rows will always begin with row 25 (due to insertion of
blank rows) but may not end with row 42.