Help with the formula to insert cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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!
=====================================================

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.
 
RagDyeR said:
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.
--
Click to expand...

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
 
Is this the functions group?<bg>
--

Regards,

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



RagDyeR said:
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.
--
Click to expand...

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
 
RagDyeR said:
Is this the functions group?<bg>
--

Regards,

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





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
Click to expand...
 
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.
 
Back
Top