Looking for formula to insert rows

[longview]

I'm new to excel and needing a little help with a particular formula.

I have a spreadsheet of approximately 500 products, each of which comes in 5 different sizes, and each individual size has its own SKU number. This is arranged in a parent/child relationship wherein each child SKU number begins with the parent base number. I'll explain further. If a shirt has a base SKU number A01, then the same shirt in a medium size would have SKU number A01M.
The shirt in Large would be A01L.
The shirt in Extra Large would be A01XL.

My question is this...

I want to be able to insert a formula that will scan the document and insert a blank row anytime there is a SKU change in the parent item. For instance, after all the A01's are listed, there should be a blank row, then the A02 items would begin.

Is this possible? Any suggestions?

 

[Gerald Higgins]

Hi, welcome to the board.

A formula can't insert a row.
It can highlight where a row should be inserted, but it can't do the inserting.

A macro can do the insertion (and also determine whether or not to insert). Is that an option you would consider ?

 

[longview]

Hello Gerald,

Thanks for the information (and the quick response). I am new at Excel, and am not familiar with the function of a macro. But, I am willing to learn. If you think it will do the job, I'm interested.

 

[thema]

One of easiest (I think) macros to solve your problem:

Sub AddRows()
Lines = Range("A1").CurrentRegion.Rows.Count
For a = 1 To Lines
If Cells(a, 1) = "" And Cells(a + 1, 1) = "" Then Exit For
If Cells(a, 1) <> Cells(a + 1, 1) Then
Rows(a + 1 & ":" & a + 1).Select
Selection.Insert Shift:=xlDown
a = a + 1
End If
Next a
End Sub

 

[Armando Montes]

Try:

Sub InsertRow()
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
   With Range("A" & i)
       If .Value <> .Offset(-1).Value Then Rows(i).Insert
   End With
Next i
Application.ScreenUpdating = True
End Sub