Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding blank rows between groups of similar names
I have a spreadsheet that is about 1200 rows long. The first column contains
names of funds, and the other columns contain different information about them. To extremely simplify what I'm trying to do, take the following list for example. A A B B B C D D This is what my first column looks like. I'd like to have Excel search through and put blank rows between the dissimilar groups so that it looks like the following A A B B B C D D Any Ideas? This is going to take me a long time to do manually... and then I want to make those blank rows color too, but doing that manually isn't quite as bad. Thanks All |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding blank rows between groups of similar names
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim iLastRow As Long Application.ScreenUpdating = False With ActiveSheet iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = iLastRow To 2 Step -1 If .Cells(i, TEST_COLUMN).Value < .Cells(i - 1, TEST_COLUMN).Value Then .Rows(i).Insert .Rows(i).Interior.ColorIndex = 38 End If Next i End With Application.ScreenUpdating = True End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Justin" wrote in message ... I have a spreadsheet that is about 1200 rows long. The first column contains names of funds, and the other columns contain different information about them. To extremely simplify what I'm trying to do, take the following list for example. A A B B B C D D This is what my first column looks like. I'd like to have Excel search through and put blank rows between the dissimilar groups so that it looks like the following A A B B B C D D Any Ideas? This is going to take me a long time to do manually... and then I want to make those blank rows color too, but doing that manually isn't quite as bad. Thanks All |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding blank rows between groups of similar names
If you copy your column of names (with a heading) into another sheet,
you could then apply advanced filter to the column and obtain a unique list of names. In your original sheet you can insert a new column A and just copy the names from column B into the new column A. Then you can apply the coloured background to the unique names (and a few other columns) in the second sheet and copy them back to the original sheet at the bottom of your data in column A. Then highlight all the data in the orginal sheet (including the new column A) and do Data | Sort using column A then column B as the sort fields. You can then delete column A, and you can delete the worksheet with the unique names and now you should have what you want. Hope this helps. Pete On Jul 6, 7:10 pm, Justin wrote: I have a spreadsheet that is about 1200 rows long. The first column contains names of funds, and the other columns contain different information about them. To extremely simplify what I'm trying to do, take the following list for example. A A B B B C D D This is what my first column looks like. I'd like to have Excel search through and put blank rows between the dissimilar groups so that it looks like the following A A B B B C D D Any Ideas? This is going to take me a long time to do manually... and then I want to make those blank rows color too, but doing that manually isn't quite as bad. Thanks All |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the MAX number while considering similar names | Excel Discussion (Misc queries) | |||
Excel adding blank rows when sorting | Excel Discussion (Misc queries) | |||
Formula for adding up totals of groups | Excel Discussion (Misc queries) | |||
SIMILAR NAMES IN A TABLE | Excel Discussion (Misc queries) | |||
Combine query to count products with similar names | Excel Worksheet Functions |