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.

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.

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.

There are two formulas, of course. For the rows in the existing table, the
formula is =C2, i.e. just make a copy of what's already in column C. In the
rows below the original data, we use a different formula that will add the
required additional copies of each city code -- enough to make 25 of each.

But the formula I gave you wasn't correct. See below.

In the mean time, the logic is: temporarily add a new column to the table that
contains 25 copies of each city code. Then sort ascending by that column, then
delete it.

The 2nd formula counts the number of Atl.'s above it in column U. If it's less
than 25, it adds another one. If it's 25, then it goes on to check for 25
copies of Bos., then for 25 copies of Cha.

So we should end up with, in column U, 75 filled cells, with 25 of each city
abbreviation. If you started with 50 rows, there are 25 new ones with data
only in column U.

If you sort (ascending) on column U alone, that should work to move the new
Atl. rows where they belong.

But to be sure, I said to use column C as the 2nd key in the sort (also
ascending). Looking at column U, there will be 25 ties for Atl.. To break the
ties, the sort will look at column C. In the original rows, it's filled; in
the new rows it's blank, and Excel's sorting rules say that blanks go to the
bottom. So you should have the original Atl. rows at the top, in their
original order, followed by the 10 blanks that have Atl. only in column U and
no other data.

But I blew it. The formula I posted isn't correct.

First error is that I wrote Atl, Bos, and Cha. I think you have periods after
each abbreviation, so it should be Atl., Bos., and Cha. Maybe you spotted that
and corrected it.

But, I entered the formulas, then did the sort to be sure it worked. Of course
that fouled up the relative row references. That's the 2nd error.

The 2nd formula must count the entries in *all of the cells above it*, so the
formula for U51 should be

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

i.e. add the periods if you didn't already, and replace the 16 with 50. Then
copy down through row 76.

Sorry for the mixup. Please post back with your results. I tried it (with the
correct formulas) and it did work correctly.


On Wed, 27 Oct 2004 17:31:01 -0700, bob wrote:

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.

Yes, I believe this works. Thanks for the help. That's tremendous.

My only problem is that I have more than just, Atl., Bos. Cha.--i have 30
cities in all and have set up five different formulas to cover all of them--4
formulas with 7 nested cities each and a fifth with two.


"Myrna Larson" wrote:

There are two formulas, of course. For the rows in the existing table, the
formula is =C2, i.e. just make a copy of what's already in column C. In the
rows below the original data, we use a different formula that will add the
required additional copies of each city code -- enough to make 25 of each.

But the formula I gave you wasn't correct. See below.

In the mean time, the logic is: temporarily add a new column to the table that
contains 25 copies of each city code. Then sort ascending by that column, then
delete it.

The 2nd formula counts the number of Atl.'s above it in column U. If it's less
than 25, it adds another one. If it's 25, then it goes on to check for 25
copies of Bos., then for 25 copies of Cha.

So we should end up with, in column U, 75 filled cells, with 25 of each city
abbreviation. If you started with 50 rows, there are 25 new ones with data
only in column U.

If you sort (ascending) on column U alone, that should work to move the new
Atl. rows where they belong.

But to be sure, I said to use column C as the 2nd key in the sort (also
ascending). Looking at column U, there will be 25 ties for Atl.. To break the
ties, the sort will look at column C. In the original rows, it's filled; in
the new rows it's blank, and Excel's sorting rules say that blanks go to the
bottom. So you should have the original Atl. rows at the top, in their
original order, followed by the 10 blanks that have Atl. only in column U and
no other data.

But I blew it. The formula I posted isn't correct.

First error is that I wrote Atl, Bos, and Cha. I think you have periods after
each abbreviation, so it should be Atl., Bos., and Cha. Maybe you spotted that
and corrected it.

But, I entered the formulas, then did the sort to be sure it worked. Of course
that fouled up the relative row references. That's the 2nd error.

The 2nd formula must count the entries in *all of the cells above it*, so the
formula for U51 should be

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

i.e. add the periods if you didn't already, and replace the 16 with 50. Then
copy down through row 76.

Sorry for the mixup. Please post back with your results. I tried it (with the
correct formulas) and it did work correctly.


On Wed, 27 Oct 2004 17:31:01 -0700, bob wrote:

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.

If this is a one-time job, you could use formulas, but you'll hit the limit of
7 nexted IF's, so you'd have to do 7 cities in one groups of formulas, and at
row 176, change to another group of formulas that do the next 7, etc.

This sounds pretty tedious, particularly if you periodically receive new data
sets that have to be "fixed". Here are a couple of macros. See the comments
at the top of each. The first requires the original list be sorted, the other
doesn't. They also differ with respect to whether the new rows contain the
city code in column C.

In both subs, I assume that row 1 is a a header row.

Sub AddRows1()
'The original list MUST be sorted by column C.
'The inserted new rows are completely empty: column C does NOT
'contain the city code. To put the city code in those cells,
'remove the apostrophe from the line marked with <<<<<

Dim City As String
Dim NewRows As Long
Dim R1 As Long
Dim R2 As Long

Const ReqRows = 25

With ActiveSheet
R2 = .Cells(Rows.Count, "C").End(xlUp).Row
Do While R2 1
City = .Cells(R2, "C").Value
R1 = R2
Do While .Cells(R1, "C").Value = City
R1 = R1 - 1
Loop
NewRows = ReqRows - (R2 - R1)
If NewRows 0 Then
.Rows(R2 + 1).Resize(NewRows).Insert
'.Rows(R2 + 1).Resize(NewRows).Columns(3).Value = City '<<<<<
End If
R2 = R1
Loop
End With
End Sub

Sub AddRows2()
'Original list does NOT need to be sorted by column C.
'Additional copies of the city code are added at the bottom
'of the existing rows, then the entire range is sorted by
'city code. The new rows have city code in column C but are
'otherwise empty.

Dim C As Long
Dim Cities() As Variant
Dim R As Long
Dim Rng As Range

Const HasHeader As Boolean = True
Const ReqRows As Long = 25

With ActiveSheet
R = .Cells(.Rows.Count, "C").End(xlUp).Row
Set Rng = Range(.Cells(IIf(HasHeader, 2, 1), "C"), .Cells(R, "C"))

Cities() = UniqueValues(Rng)
R = R + 1

For C = 1 To UBound(Cities)
NewRows = ReqRows - Application.CountIf(Rng, Cities(C))
If NewRows 0 Then
.Cells(R, 3).Resize(NewRows, 1).Value = Cities(C)
R = R + NewRows
End If
Next C

.Cells(1, 3).CurrentRegion.Sort _
key1:=.Cells(1, 3), order1:=xlAscending, header:=IIf(HasHeader, xlYes,
xlNo)
End With

End Sub

Private Function UniqueValues(Rng As Range) As Variant()
Dim i As Long
Dim j As Long
Dim N As Variant
Dim v As Variant

v = Rng.Value
ReDim List(1 To UBound(v, 1))
N = 0
For i = 1 To UBound(v, 1)
x = v(i, 1)
If x < "" Then
For j = 1 To N
If List(j) = x Then Exit For
Next j
If j N Then
N = j
List(N) = x
End If
End If
Next i
ReDim Preserve List(1 To N)
UniqueValues = List()
End Function




On Thu, 28 Oct 2004 09:59:01 -0700, bob wrote:

Yes, I believe this works. Thanks for the help. That's tremendous.

My only problem is that I have more than just, Atl., Bos. Cha.--i have 30
cities in all and have set up five different formulas to cover all of them--4
formulas with 7 nested cities each and a fifth with two.


"Myrna Larson" wrote:

There are two formulas, of course. For the rows in the existing table, the
formula is =C2, i.e. just make a copy of what's already in column C. In the
rows below the original data, we use a different formula that will add the
required additional copies of each city code -- enough to make 25 of each.

But the formula I gave you wasn't correct. See below.

In the mean time, the logic is: temporarily add a new column to the table
that
contains 25 copies of each city code. Then sort ascending by that column,
then
delete it.

The 2nd formula counts the number of Atl.'s above it in column U. If it's
less
than 25, it adds another one. If it's 25, then it goes on to check for 25
copies of Bos., then for 25 copies of Cha.

So we should end up with, in column U, 75 filled cells, with 25 of each
city
abbreviation. If you started with 50 rows, there are 25 new ones with data
only in column U.

If you sort (ascending) on column U alone, that should work to move the new
Atl. rows where they belong.

But to be sure, I said to use column C as the 2nd key in the sort (also
ascending). Looking at column U, there will be 25 ties for Atl.. To break
the
ties, the sort will look at column C. In the original rows, it's filled; in
the new rows it's blank, and Excel's sorting rules say that blanks go to
the
bottom. So you should have the original Atl. rows at the top, in their
original order, followed by the 10 blanks that have Atl. only in column U
and
no other data.

But I blew it. The formula I posted isn't correct.

First error is that I wrote Atl, Bos, and Cha. I think you have periods
after
each abbreviation, so it should be Atl., Bos., and Cha. Maybe you spotted
that
and corrected it.

But, I entered the formulas, then did the sort to be sure it worked. Of
course
that fouled up the relative row references. That's the 2nd error.

The 2nd formula must count the entries in *all of the cells above it*, so
the
formula for U51 should be

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

i.e. add the periods if you didn't already, and replace the 16 with 50.
Then
copy down through row 76.

Sorry for the mixup. Please post back with your results. I tried it (with
the
correct formulas) and it did work correctly.


On Wed, 27 Oct 2004 17:31:01 -0700, bob
wrote:

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.