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.
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 are:
what is the logic behind this approach?
do i sort in ascending or descending order in columns C and U?
thanks very much,
bob