Help with the formula to insert cells
 

Hi,

Below is my question,

I need to have 2 rows inserted for each cell I have in column "A".

Serial # RESULTS
5877-0101-6090 5877-0101-6090
5886-0101-6090
5885-0101-6090
5886-0101-6090


5885-0101-6090

Help with the formula.

Help with the formula to insert cells
 

With datalist in Column A of Sheet1, say you want to create this "expanded"
list in Column B (or *any* other column) on another sheet.

Enter this formula in first cell of the *new* location:

=INDEX(Sheet1!A:A,ROWS($1:3)/3)

NOW, select the formula cell *AND* the next 2 *empty* cells (select -
*don't* drag to copy the formula).

Click on the fill handle of this *3 cell* selection (1 formula cell and 2
empty cells), and drag down to copy as needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Igneshwara reddy" wrote in
message ...
Hi,

Below is my question,

I need to have 2 rows inserted for each cell I have in column "A".

Serial # RESULTS
5877-0101-6090 5877-0101-6090
5886-0101-6090
5885-0101-6090
5886-0101-6090


5885-0101-6090

Help with the formula.

Help with the formula to insert cells
 

"RagDyeR" wrote:

With datalist in Column A of Sheet1, say you want to create this "expanded"
list in Column B (or *any* other column) on another sheet.

Enter this formula in first cell of the *new* location:

=INDEX(Sheet1!A:A,ROWS($1:3)/3)

NOW, select the formula cell *AND* the next 2 *empty* cells (select -
*don't* drag to copy the formula).

Click on the fill handle of this *3 cell* selection (1 formula cell and 2
empty cells), and drag down to copy as needed.
--

Or you can use this macro

Sub InsertRows()
Application.ScreenUpdating = False
Range("A1").Select
nr = ActiveCell.CurrentRegion.Rows.Count
For i = nr To 3 Step -1
Cells(i, 1).EntireRow.Insert
Cells(i, 1).EntireRow.Insert
Next i
Application.ScreenUpdating = True
End Sub

Peter

Help with the formula to insert cells
 

Is this the functions group?<bg
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Billy Liddel" wrote in message
...


"RagDyeR" wrote:

With datalist in Column A of Sheet1, say you want to create this
"expanded"
list in Column B (or *any* other column) on another sheet.

Enter this formula in first cell of the *new* location:

=INDEX(Sheet1!A:A,ROWS($1:3)/3)

NOW, select the formula cell *AND* the next 2 *empty* cells (select -
*don't* drag to copy the formula).

Click on the fill handle of this *3 cell* selection (1 formula cell and 2
empty cells), and drag down to copy as needed.
--

Or you can use this macro

Sub InsertRows()
Application.ScreenUpdating = False
Range("A1").Select
nr = ActiveCell.CurrentRegion.Rows.Count
For i = nr To 3 Step -1
Cells(i, 1).EntireRow.Insert
Cells(i, 1).EntireRow.Insert
Next i
Application.ScreenUpdating = True
End Sub

Peter

Help with the formula to insert cells
 

"RagDyeR" wrote:

Is this the functions group?<bg
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Billy Liddel" wrote in message
...


"RagDyeR" wrote:

With datalist in Column A of Sheet1, say you want to create this
"expanded"
list in Column B (or *any* other column) on another sheet.

Enter this formula in first cell of the *new* location:

=INDEX(Sheet1!A:A,ROWS($1:3)/3)

NOW, select the formula cell *AND* the next 2 *empty* cells (select -
*don't* drag to copy the formula).

Click on the fill handle of this *3 cell* selection (1 formula cell and 2
empty cells), and drag down to copy as needed.
--

Or you can use this macro

Sub InsertRows()
Application.ScreenUpdating = False
Range("A1").Select
nr = ActiveCell.CurrentRegion.Rows.Count
For i = nr To 3 Step -1
Cells(i, 1).EntireRow.Insert
Cells(i, 1).EntireRow.Insert
Next i
Application.ScreenUpdating = True
End Sub

Peter

Help with the formula to insert cells
 

Hi,

Your forumla is very good to work.

Can I use this forumla with "IF" condition.

For Ex: If cell A1 contains 5886N, in this cell there is an alphabet which
contains and I need to input 4 lines and if it is without alphabet it should
input 3 lines.

I know to sort this and use the function, but very eager to know whether
this will work in the forumla only.

"RagDyeR" wrote:

Is this the functions group?<bg
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Billy Liddel" wrote in message
...


"RagDyeR" wrote:

With datalist in Column A of Sheet1, say you want to create this
"expanded"
list in Column B (or *any* other column) on another sheet.

Enter this formula in first cell of the *new* location:

=INDEX(Sheet1!A:A,ROWS($1:3)/3)

NOW, select the formula cell *AND* the next 2 *empty* cells (select -
*don't* drag to copy the formula).

Click on the fill handle of this *3 cell* selection (1 formula cell and 2
empty cells), and drag down to copy as needed.
--

Or you can use this macro

Sub InsertRows()
Application.ScreenUpdating = False
Range("A1").Select
nr = ActiveCell.CurrentRegion.Rows.Count
For i = nr To 3 Step -1
Cells(i, 1).EntireRow.Insert
Cells(i, 1).EntireRow.Insert
Next i
Application.ScreenUpdating = True
End Sub

Peter