Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 27th 04, 06:45 PM
bob
 
Posts: n/a
Default Insert rows based on specific value

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.


  #2   Report Post  
Old October 27th 04, 07:43 PM
Myrna Larson
 
Posts: n/a
Default

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.


  #3   Report Post  
Old October 28th 04, 01:31 AM
bob
 
Posts: n/a
Default

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.



  #4   Report Post  
Old October 28th 04, 02:43 AM
Myrna Larson
 
Posts: n/a
Default

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.




  #5   Report Post  
Old October 28th 04, 05:59 PM
bob
 
Posts: n/a
Default

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.






  #6   Report Post  
Old October 28th 04, 11:25 PM
Myrna Larson
 
Posts: n/a
Default

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.





  #7   Report Post  
Old February 29th 08, 07:11 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2007
Posts: 17
Default Insert rows based on specific value

Hi Myrna,

I'm trying to do something similar, and wondering if you could help. I'm
looking for a macro that will insert a specific number of rows based on a
cell's value.

For example:

The data that would determine the amount of rows to be inserted in is Column
U, beginning with U2.

If the value of U2 is 1 or less, no rows need to be inserted and macro
should move to next value in U (U3).

If the value in U3 is 2, one blank row needs to be inserted underneath,macro
should move to U5.

If the value in U5 is 40, then 39 rows should be inserted underneath, macro
should move to U45.

And so on and so forth, until the value in Column U is blank.

Can you help with this?

Thank you.

"Myrna Larson" wrote:

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.








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Display specific rows from table on other worksheet Bruno G. Excel Discussion (Misc queries) 2 January 20th 05 11:22 PM
Challenging Charting C TO Charts and Charting in Excel 0 January 17th 05 06:57 PM
How do I insert an image into a specific cell within a protected . Scott Peebles Excel Discussion (Misc queries) 1 January 7th 05 01:14 AM
how do I insert multiple rows in excel after every row of data grantm5 Excel Discussion (Misc queries) 1 December 14th 04 08:09 PM
Counting rows based on criteria in multiple cells Margaret Excel Discussion (Misc queries) 11 December 2nd 04 11:04 PM


All times are GMT +1. The time now is 12:06 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017