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.
|