• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

colored row as delimiter

E

ehkalyan

New Member
Hi,


I have rows of data like below


Animal horse

Animal cat

Animal dog

Bird eagle

Bird pigeon

Car hyundai

Car swift

Car merc

Car BMW

Mobile iPhone

Mobile S4


I want to insert a yellow color coded row between each group so that each group can be distinguished easily


Like yellow color coded row between 3&4 row, 5&6 row, 9&10 row


can you please help?
 
make a pivotTable from the data.


then right click the field, go to Field settings, on the 2nd tab there should be a check box to insert a blank row.

then you can select all of the inserted blank rows together by placing your cursor towards the right side of on the new blank rows (till you see a solid black right-pointing arrow) and format the rows as you wish.
 
Hi Jason,

thank you for your response...

i am a novice unfortunately to excel... I have this huge 1200 rouws of data which I need to delimit with rows as in the example above.

can you please assist me on how to create a pivot table?

many thanks in advance :)
 
Good day ehkalyan


You can create the blank rows separately and then interleave them with the existing rows by sorting. To start, insert a new column to the left of the existing column A. Enter 1 in cell A1 and highlight column A all the way to the last row that contains data. From the Edit menu select Fill | Series and click on OK. Column A should now contain numbers from 1 to the total number of rows. Press Ctrl-C to copy these cells to the clipboard, click in the cell just below the last of them, and press Ctrl-V to paste. Now highlight the entire data area, including the new rows with just a number in column A. Select Sort from the Data menu and choose the No header row option in the resulting dialog box. Under Sort by select Column A, under Then by select column B, and click on OK. Finally, delete column A. You now have a blank row after every one of the original 1,000-odd rows.

Because blank lines can often cause problems with charts or calculations, you may want a quick way to remove them. You can use a similar technique.

Again, insert a new column to the left of column A and fill a series from 1 to the end of the data. Highlight the entire data area and sort by column B (the first column of real data). This will group all of the blank rows together. Next, highlight the data area again, and re-sort by column A. The blank lines are gone, and your data is restored to its original order. Finally, remove column A.

Or if you want to try VBA

MAKE SURE YOU SAVE YOUR WORK BEFORE YOU TEST THIS. Insert in to a module and then run it on your data page. Place your cursor at the start of the column you wish to check for data, this code will insert one row each time a non-blank cell is encountered. The code will only run for the first 1200 rows of data.


Sub insertrow()

' insertrow Macro


Application.ScreenUpdating = True

Dim count As Integer

Dim X As Integer


For count = 1 To 1200

If activecell.Value <> "" Then

activecell.Offset(1, 0).Select

Range(activecell, activecell.Offset(0, 0)).EntireRow.Insert

activecell.Offset(1, 0).Select

For X = 1 To 1

Next X

Else

activecell.Offset(1, 0).Range("a1").Select

End If

Next count


End Sub
 
Hi ehkalyan,

I assume.. there is already a Header Column (if Not Create One.. )

[list type=decimal]
[*] Insert a new Column.. (Click On "A" & Press Ctrl + +

[*] So Now you have a New Column "A"

[*] In A2 write "1" (as A1 is Header Row)

[*] In A3 write Formula as =IF(B3=B2,A2+1,1)

[*] Drag or double click to fill below below..

[*] Come back to A1.. Apply Filter Ctrl + Shift + L

[*] In A1 apply Filter on 1..

Now all 1st occurrence of that group is filtered.

Select Row 2:1200 or Select All Rows.. (Select A2.. then Shift + Space then Ctrl + Shift + Down

Select all Visible Cells Alt + ;

Insert Row Alt + I + R

UnFilter Ctrl + Shift + L

Delete Helper Column A Ctrl + -

[/list type=decimal]

If everything thing goes fine.. then.. I would like to set homework for you.. how to color all Blank newly inserted Rows as Yellow..


Regards,

Deb
 
Hi ,


One more alternative ! Of course this is simple only if your data is in one column , or if you wish to colour one cell of each blank row. If not , please ignore this post.


First , select the column which has the repeated occurrences of data , at each of which you wish to insert the yellow coloured row. For example , in your data , if the data Animal , Bird , Car , Mobile is in column A , select the data in column A.


Click on Data , Subtotal. Excel will display a message ; click OK.


Select Varp in the Use Function drop-down. Click OK.


Excel will insert a Subtotal row between each group , with the #Div/0! error. The only flip side is Excel will also insert a column to the left of the data column. Delete this column , if you wish.


Now , press F5 or CTRL G to bring up the Go To dialog ; click on Special , Formulas ; uncheck the following check boxes : Numbers , Text , Logicals. Click OK.


Since all the inserted cells are selected , click on the fill colour of your choice , to colour all these selected cells ; press the DEL key to get rid of the error values.


Narayan
 
You must log in or register to reply here.
Back
Top