- **Excel Worksheet Functions**
(*https://www.excelbanter.com/excel-worksheet-functions/*)

- - **Insert rows based on specific value**
(*https://www.excelbanter.com/excel-worksheet-functions/5142-insert-rows-based-specific-value.html*)

Insert rows based on specific valueWorksheet 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. |

Insert rows based on specific valueHi 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. |

All times are GMT +1. The time now is 05:54 PM. |

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.

ExcelBanter.com